It's the little details that get you down. SQL Server Integration Services are all about extracting, transforming and loading data. The big problem with doing that successfully is handling the data types. You see SSIS has a slightly different set of types to what your SQL database has. Translating between them is done automatically and just like a weakly typed language it can get them wrong. However recently I had a different issue which requires relating the whole saga.
Firstly my package hung, sitting on pre-execute. This alone was vexing for there was no obvious reason. So I turned to the old adage "Let the database handle it". I made stored procedures of my selection queries, and temporary tables to draw from.
Alas an SQL task above my data flow gave syntax errors and dutifully I went to debug. I turned off retain same connection and that seemed to do the trick. I was running, or getting closer to it.
Alas behind one error another, parameter not found in mapping. Thus I went to rename the parameters, to make them whole and clean.
I set up my package around this new way, with an Execute SQL Task, followed by the data flow. And now my data flow would retrieve the data from temporary tables that existed only as long as my connection. Of course this meant I had to turn back on retain same connection. SSIS is diligent in cleaning up after itself.
It is a strange thing but in testing I have found, using parameters in SSIS queries incredibly slows it down. I do not know the vagaries of pre-execute, only the lengths that it must go to. Thus the lesson for today is, when going for complex parametric queries, just use stored procs. Slow they may be, but faster than SSIS.
Batch scripting makes everything better! If a tool doesn't have a command line equivalent it probably isn't worth using.
dtutil is the command line utility for SSIS Packages. You can use it to perform any function that would be available through interacting with a package store through Management Studio. Like most command line executables you can get help for it with dtutil /?
The above command will take the package MyPackage.dtsx from C:\ and install it . /EN means we want to encrypt that package and install it to the SQL Server as TestPackage. The 5 on the end of that argument is the encryption level, 5 means use the Server's encryption rather than providing a manual password. /Q specifies quiet mode which means the package will automatically overwrite TestPackage if it already exists. Finally the /dests argument specifies which server we're installing to, without it dtutil will assume you want localhost.
This script will create a new folder in the package store called TestProject, install TestPackage to it and then give us a view of the packages in TestProject, so we know it's installed correctly. It'll also display that until the user presses a key. From there our batch script might go on to run an SQL script or install a library. It's all up to you.
Another enjoyable problem in SQL which reared its head. We had a transactions table with transactions linked to customers. Normally in SQL you operate on all, there's no concept of an ordered list which you iterate through. In this particular case we had to retrieve the previous transactions for customers where the last transaction was a cancellation on the same day.