SSIS Package Execution from TSQL

I have been learning some SSIS and created a file transformation package to load and convert some data. I have come across 2 major headaches in doing so that i thought i would share.

1st - the deployment. This seemed so complex when i first set about doing it. If you follow the MS articles in BOL you will get most of the way there but they omit the part about security settings. When you deploy the build & deploy package id you encrypt sensitive data with user key it will now work as it will try and use your local workstation account which will not reside on the server. Basically you will need to tell it not to save sensitive data (property setting on the control flow page).

Also if you deploy the settings to a sql server table it appears to save the passwords encrypted! Truth is that it doeasnt save the passwords for user connections to databases etc! So the only way i found to get it to work is to store the password (which is in plain text) in the config! I just dont get that bit - just doesnt seem secure to me.

Next biggy was getting the package to run using the dtsexec util. I wanted to fire in the values for 2 variables i had to allow the package to be dynamic so i could re-use it for multiple files.

Here is the command line i tried running:

EXEC xp_cmdshell

'dtexec /DTS "\File System\DataLoad\DataLoad" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /SET "\Package.Variables[User::FileName].Properties[Value]";"c:\data\01052008b.txt"'

Do you see the problem?? No - didnt think so. Its just not obvious. What you cant see from the text above is some CRLF's (line breaks) the i put in improve readability while i was debugging. Turns out that all that will get passed to the package is the text up to the first CRLF!!! Hence my /SET wasnt getting parsed!!

Talk about head/brickwall!

So make sure you keep your command executions on a single line! Even is its huuuuuge!