Home > SQL Server > SSIS Woes


I spent the day editing some very glitchy SSIS packages , and I must say I can understand why some people stay away from SSIS. A particular annoyance that I’ve found in 2008 comes up when using the Derived Column transformation. When you create a derived column and enter the expression, it will automatically set the data type for you. You cannot change this here like you can in SSIS 2005. Why the backward step ?
In case you were wondering if you are stuck with the system selected data type , you can use the advanced editor to change it. Close the Derived Column setup window , and in the data flow Right Click on the Derived column box and select the Advanced editor. Select the Input and Output properties tab, and under Derived Column Output you can change it.

Now another thing that has really caused me frustration on many an occasion is loading large CSV files into a database via SSIS. I’m referring in particular to files with a large amount of columns ( 150 + ) and lots of data. When you create the data source object , it will automatically detect the data types of the columns. You can even hit a button called Suggest Types and it will go through a number of records and try to figure it out.

Unfortunately it sometimes gets it wrong. And then you have to manually go through 200 columns an edit the type. And then you get issues converting between Unicode and Non Unicode. Its a pain. Lets hope these little things get fixed soon.

I must say thought that once I’ve tested and deployed packages , they have worked reliably without needing much attention. Are there any other SSIS bugs and quick fixes you’d like to share ? Drop us some comments …..

Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: