DTExec and SSIS

I could list lots of excuses as to why it’s been so long since I blogged but I’d rather get to the blog post so let’s just say I’m lazy and working on getting better.  :)

I’ve been doing work with SSIS and running it at a command prompt so I could use it to load test a Database.  My hope is later to move this into a simple application that calls the procedures I want but until I start learning C# for now I’ll stick with SSIS. 

The problem I ran into was passing parameters.  I have a loop inside my package and I want it to run X amount of times for my testing needs.  That’s not a problem on setting up the loop you can find that here.  But how do you pass the variable to the package so that you can tell it how many loops you want?  Well for that here is  another blog that talks about it.    Ok so now that I’ve linked off most this post to others what the hell am I trying to add to all this?

Well I read those blogs and it worked until I started adding multiple set options and I ran into some simple syntax issues.  I also noticed there was few examples out there of the full syntax of what it looks like.  Call me crazy but this always screws me up when I’m trying to get syntax right with SSIS.  So here are my scripts that I use currently to execute my package. 

DTExec.exe /Rep N
/set \Package.Variables[User::iCounterEnd].Properties[Value];100
/set \Package.Variables[User::RunDataLoad].Properties[Value];-1
/set \Package.Variables[User::InitialLoadEnd].Properties[Value];1000000
/File "c:\TestAdventureWorks\runstresstests.dtsx"

Now as you might guess in my package I have  3 variables called iCounterEnd,RunDataLoad and InitialLoadEnd.  For all you SQL people out there that are not use to coding environments they are case sensitive(yes this has been hard for me as well).  Also notice that RunDataLoad is a –1.  The variable is a Boolean which I would have assumed would say “true” or “False”  but I was wrong.  It is –1(True) or 0(false) for the setting. 

Now a great way to get examples of what should and should not be is to use Package Configurations.  If you enable package configurations and write an xml file down to some directory then you can get the variable path/name that you need and the value it’s currently set to.  Here’s a screenshot of what the package config file will look like. 

The Path = is what you need above to make sure you have the correct path to the variable. 

Hopefully this will help others to take a little less time to get a package to run from the command line.

Pat