SSIS - Speeding Up Package Load Time

Your Package Will Load...Someday 

While working with SSIS, I was often frustrated by how long the package takes to load for editing. Frequently, I was simply interested in opening the SSIS packages to see what they did, but really had no need to actually edit them. Sadly, if you try to open an SSIS package on a machine that either does not contain all of the databases referenced in the package or that cannot connect to those databases, you often have to wait an incredible length of time for the package to open before you even have a chance to change the connections to something valid.

I discovered a small trick which cures that dilemma - open the SSIS package in a text editor (the package content is actually XML), make some minor edits, save, then finally open the SSIS package in BIDS.

Delaying Package Validation Until Later

 Open your package in a text editor and search for all instances of

 "DelayValidation">0</DTS:  

Replace those with

"DelayValidation">1</DTS:

This simple change will prevent BIDS from attempting to connect to and validate every object before allowing you to view the content of the SSIS package. It will still attempt to validate an object when you click on that object to edit it, but at least you won't have to wait for it to time out on its attempts to validate every item.

Correct Connection Strings

Open your package in a text editor and search for all instances of

<DTS:Property DTS:Name="ConnectionString">

Check the value for Data Source and Initial Catalog - are these correct for the environment you're working in? Many times, the package may have been created with connection strings specific to the server they were built on and either the server no longer exists or is no longer accessible. By correcting the connection string information prior to opening the package in BIDS, you can speed up the package load time and avoid the flood of validation errors that go along with incorrect connection information.*

=======
* Yes, I know there are some SSIS tricks for handling different connection strings, such as using a configuration file to hold the connection information, however many SSIS packages that people inherit do not have configuration files for the connection information.

Adventures in SSIS: Data Migration

After escaping from technical support to the “greener fields” of Product Engineering, I thought that I would finally be in my element and have completely stress-free workdays.
So untrue.
So painfully untrue.
The new position offered enough stress to make event the most stable person ready to fling primary colored cartoon-like stuffed birds at an equally cartoon-like stuffed pig (who, for some unexplained reason, is lime green rather than pink, and seems to be just a head). Not that I do that frequently, but these poor birds have seen better days, as their ruffled feathers can attest. I suppose I could try to take my stress out on other things, but HR gets a wee bit upset if I throw chairs or coworkers (drat).Fortunately, they don’t seem to mind the occasional Angry Bird™ flying through the cube farm, however, so I fling away.
What is the current stressor?
SSIS.
I must admit that up until now I haven’t touched SSIS at all. I have plenty of experience working with DTS packages, but none with SSIS.
Until now.
There is no easy DTS to SSIS knowledge conversion. They’re completely different animals. They may do similar things, but nowhere near in the same manner. Books Online offers some information, but if you don’t already know SSIS the documentation might as well be in Klingon since it assumes you fully understand everything and are just using the books online for a quick definition check.
Great. And it’s due by the end of February. Which, by the way was yesterday.
Thank GOD it’s a leap year, otherwise I would have had to ensure I learned invisibility instantly.
Some of the sections of the package I was able to copy from other migration packages I found in our Source Control (it’s Tortoise Subversion, BTW), but the rest had to be created by me, and quickly.
Some things I could figure out: get data from Source, compare to Destination using LookUp, then Insert New unmatched data into Destination. Works great unless you have some wiseguy who customizes the data and causes a primary key violation between Source and Destination. Simply inserting a new row without keeping the key value didn’t work because the Destination table does not have identity insert (aka “autoNumber”) on the key. I searched everywhere for ideas—checking  “SSIS Upsert” and equivalents in Bing and Goolgle—to see if anyone had written code that could help me. Most articles assumed that the destination had identity insert turned on.
Thankfully, I had the #SQLHelp hastag on Twitter and received responses from some awesome people* who were able to provide me the hints / guidance that I needed to plow through this disaster waiting to happen. The saving tweet (thanks @EricWisdahl) contained a link to an extremely helpful article by Phil Brammer called, “Generating Surrogate Keys”. It was exactly what I needed. 
Well, mostly….
SSIS is extremely fussy about certain things—C# coding is case sensitive; datatypes must match exactly; math on certain numbers mysteriously changes the datatype. Things like that.
The issue I had was that the Source table use int for its KeyID, and the Destination table used tinyInt. Most searches for an equivalent in SSIs won’t tell you the full story… but I finally found it.
First, several sources tell you that

tinyInt is equivalent to DT_UI1 (single byte un-signed integer),

but if you check the list of datatypes for variables, you’ll note that it’s missing (or if not missing, then named something that is NOT intuitively equal. What does DT_UI1 mean anyway?).
After many false starts (including learning that the C# scripting is painfully case sensitive), I discovered that

the Byte data type works for tinyInt Variables.

The other thing I found was that in SSIS scripts, when you add something to a value, it automatically forces a conversion to int datatype—and it’s very difficult to undo that. What I did was use Eric’s script unchanged, except to the final statement

Row.ClientKey = NextKey;

I added a conversion to byte:

Row.ClientKey = (byte)NextKey;

and it worked.

Now stress is greatly reduced and my Angry Birds and Green Pig are happily noshing on my cereal.



* HUGS! to @onpnt and @EricWisdahl for being “first responders”