Sunday, May 24, 2009

Some SSIS pointers

Here's a few pointers that'll make your life with SQL Server Integration Services (SSIS) a bit easier:

Set your database table fields to Unicode character types
If your data sources come from MS Excel and Access, and you're not using Unicode character types, you'll need to include the Data Conversion data flow task. It's pure grunt work that you'll need to include for each table you're migrating, and it can be easily avoided.

Avoid repository conflicts by working on separate packages and merging later
Using SVN, this has been a nightmare. Sometimes your packages merge perfectly, other times you have many lines of conflict that are just not worth the effort of resolving. It's easier in that situation to copy and paste the work you've created to another package, delete the file and get it back from SVN again, re-add your files and commit.

As a temporary stop measure work on separate packages, within the same solution, from your colleagues and designate one person to look after a "main" package (essentially manually merging your work). This also prevents an irate colleague from walking over to your desk several times a day when you keep breaking their connection strings and giving them gifts of 29 errors and 209 warnings that they can only resolve through re-establishing the connections in the each of the data flow tasks.

Only use a SQL Server Destination if your packages are going to run on a local server
Use an OLE DB Destination if your SQL Server instance is located on a remote server. An issue with this is that you don't get all the functionality associated with SQL Server Destination (and, most importantly, the speed!). A quick MSDN lookup will show you that this is in fact a feature, not a bug! Such is life...

No comments:

Post a Comment