Getting the most recent records from a history table.

Assume we have a History table with multiple records per Employee that looks like this :

It contains 3 fields an EmployeeID,StartDate and HistoryInfo

We are only interested in the most recent record per employee. So this would be records marked in yellow :

To select these 3 records, let’s first create a SQL statement that adds a columns with a rank per employee :

Add another calculated column to number the records belonging to the same Employee

Now place this in a WITH-Clause called BaseTable and select only rows where RankNumb is equal to RowNumk, like this :

Now we have the most recent record per employee.

Debugging an SSIS ‘Script Task’ and an SSIS ‘Script Component’.

Script Task : In many cases debugging a Script Task is not that difficult. Only if you have installed 64bit version of integration services you cannot debug. If you place a break point in a Script task, the debugger will not interrupt execution. Instead you will get error “Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime.”. To make sure de debugger stops on breakpoints. Go to the project in your solution explorer, right mouse click and go to properties, then go  to ‘debugging’ and select Run64BitRuntime and change it to false.

Next time you run the package you will be able to debug. Once everything work you might want to switch back  to the 64bit runtime again.

Script Components : ‘Script Components’ are a bit different from ‘Script Tasks’. ‘Script Tasks’ can be used in the control flow and ‘Script Components’ can be only be used inside a Data Flow Tasks. They either act as a source, a destination or do a transformation. In many cases they are hard to debug. Because placing a break point will not stop the execution of the package ones it reaches the break point. Some of my college say they put messageboxes in the code to see what happens. I doesn’t work for me. I prefer putting  Debug.Write lines in it.

like this :

System.Diagnostics.Trace.Write("Klant " + Dts.Variables["FolderKLANT"].Value);

To see this feedback or debug info you must download a small utility called debug view

http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx

Once you run this itwill capture all the Debug.Writes you have placed in you scripts

Make sure you run the 32bit Run time. See above.

If you let the Debug.Write statements in your script if won’t be a big problem. With any luck if you have the right tou will be able to debug the production versions of your package. You can let DebugView utility connect to another computer or server

I hope this makes you life a bit  easier, and lets you produce more stable packages.

SSIS Foreach Loop Container stops after the first error.

Let's say you have a Foreach Loop Container looping thru all the employee records. For every record it does a few business logic checks, for some record you decide to throw an error and rollback the 'MS DTC' transaction to rollback eventually inserted or updated records. This error will be sent to the a Foreach Loop Container stopping it from selecting the next Employee record.

I have played around with FailPackageOnFailure, ForceExecutionValue and some other properties, none of them gave the result I wanted. I simply wanted the loop  to pick up the next Employee record and apply the same business logic.
The solution was adding an OnError Event handler to the Sequence container inside this Foreach Loop Container, then go to the variables and push the button to see the system variables and set the value for the Propagate variable to false

Now the error is no longer Propagated to the Foreach Loop Container. This means it will continue doing its work as expected