Too Many Copy & Paste Operations May Confuse SSIS

Apparently its easy to confuse SSIS if you make too many copy and paste operations inside a Data Flow Task. In this case I added six data flows going through six Slowly Changing Dimension Transformations to one Data Flow Task. I know, I could have split the DFL into 6 seperate DFLs, but the data flows are very small. Each flow needs tracking information from a SSIS variable. That is done with a Derived Column Transformation. Each flow receives the same tracking variable so the Derived Column Transformation was configured once and then copied five times. The Data Flow Task runs fine but ends with an error even though all components inside the DFL are marked green! The DFL is marked red in the Control Flow editor.

The error thrown was:

The variable “System::LocaleID” is already on the read list.

The workaround:
- create a new package
- copy the misbehaving Data Flow Task and paste it in the new package
- delete the Data Flow Task
- copy and paste from the new package

Maybe it has something to do with IDs of components that are not getting updated correclty ?
Related: New-suggestion-for-templates-in-Katmai

Initialize SSIS Variables from a Database Table

In many SSIS packages variables control the workflow. Here is a quick and generic way to load the initial values from a database table instead of having to hard-code them into the package or use configuration files.

Read more »

SSIS Variables Do Not Like Varchar(MAX)

You may get an error when loading a string variable from a varchar(MAX) field via an Execute SQL Task. Here is the complete error I got when loading a variable named SQLStatement:

Error: 0xC001F009 at I_CTHNDL: The type of the value being assigned to variable “User::SQLStatement” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Read more »

Convert an Integer Date to a Smalldatetime

A common way to represent a date – often in mainframe systems – is in the format yyyymmdd. Unfortunately it is not easy to do calculations on such a representation. To be able to use the native T-SQL date functions such as datediff and dateadd you will need to convert it to the datetime or smalldatetime type.

Read more »

« Previous Page