Posts Tagged ‘SSIS’

04
Oct

When loading data through an SSIS package, one of the problems we face is when data causes a step to fail.  If the source data contains 10,000 rows, how do we tell which row had the data that was too long, in the wrong format, null or caused the bad join? Well, when running in automated mode (via a SQL agent job), SQL will log the cause of the error (the error description and the column), but will not log anything

19
Sep

One of the problems with moving a SQL insert statement to SSIS based packages is that logic which is freely available in SQL needs some work arounds to get the same result in SSIS. Such work arounds needs to be developed when working with pattern matching. In SQL, doing pattern matching is fairly easy. We may match patterns for logical tests using the SQL LIKE clause as follows: ...,[SLAM_HRG_Code] = CASE WHEN LEFT(r.[Service],1) LIKE '[A-Za-z]' AND SUBSTRING(r.[Service],2,1) LIKE '[A-Za-z]' AND ISNUMERIC(SUBSTRING(r.[Service],3,2))

04
Sep

Things to check when your data warehouse loads take too long‏

If you ever find yourself suffering from slow data load problem, let me say without a doubt…. there is no situation where the loading of data into a data warehouse should go beyond a couple of hours. If a data warehouse here at WhippsX NHS Trust can load 2 years worth of data from 3 different trusts in less than 2 hours, any data warehouse should be able to load a days worth of data in a few

14
Sep

Whenever we develop data maintenance packages through SSIS for our customers, one of the areas we pay particular attention to are invalid records. Where possible, we work with the owner of the data to put in code to cleanse the data as it is processed, but for unexpected errors, we like to create customer logging and error trapping, which processes the valid data, logs the invalid data (for manual entry or correction), and continues without failing the who