Courion Tip – SSIS Package changes

First off – MAKE A COPY OF THE SSIS PACKAGES before you do anything. If possible, implement a versioning system. I like to just copy the folder, and rename the copy to yearmonthdate_package name. For instance, 20120301_Courion_SAP_Import

I can’t give you too many specifics, I will probably re-edit this post several times, but here are my gripes and my solutions to something that is more a matter of opinion than anything else.

Make as many copies of the package as you have target system types, then strip out all the other targets. In other words, if you have one package that imports 8 target types, you should wind up with 8 packages, each dedicated to a particular target type. I rename all of them to match what they are importing. For instance, Courion_SAP_Import, Courion_AS400_Import, etc. If you share your SQL server / SSIS server or DBA with other systems, the Courion at the front will help know it’s your package that’s failing.

Why split it up this way?

Well, for one thing, you can schedule them as appropriate. We import Active Directory every few hours looking for new accounts, but AS/400’s only once a day.

Secondly, debugging and updating. The person who did ours was, admittedly, under a time constraint. So, the resulting package worked just fine, but generated something like 40 warnings. This was a little intimidating for me (didn’t know SSIS very well at the time). By splitting it up, I could look at a few warnings and tackle them one at a time to get rid of the warnings. Also, you don’t want to add a target to just a small part of it, only to find out you have broken ALL of it.

Most of the warnings consisted of ‘data truncation could occur…’ Basically, we were importing 100 characters from a file, and putting it into a 50 character column. If you know your data (see my other post), you should know what the maximum is (50? 100? 2000). To correct this, start by setting your column in the table definition to something larger than or equal to the maximum (for instance, no point in having employee id’s that are 100 characters long, when company rules say they should only be 6 characters), and then looking at the connector information in the package. It might take you a while to track them all down, but it will be worth it as a training exercise & you will have a cleaner system.

Learn what *.dtsconfig files are about, and use them!!  We didn’t know how to test our code in our test system, then implement it in production without making fairly big changes to the code. This is the answer.

Your test database table structure must match your production – besides being just plain common sense, good practice and all that – since all you should be editing is the dtsconfig file, your package will fail if the tables are different (or work in an unpredictable way)


Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: