Category Archives: Parameters

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.


SSIS Parameters

I think I’m writing this as much for my sake as for others. Since I continually forget this setting here is how you pass in Parameters into SP’s your calling in your Execute SQL tasks’s in SSIS. In particular for this one I’m passing date’s which is common.

When you have a Execute SQL Task to set the paratemer your passing in as a Variable in SSIS here’s the Steps i’ve been using.

Create the Variable in SSIS, SSIS>Variables>NewIcon> Give it a name and type it should be scoped most likely as the whole package. That does depend on your complexity of the package.

Add your Execute SQL Task> When entering your Stored Procedure use Question marks for the parameters seperated by a comma. Then hit the parameters button and in the parameter name use each variable name from the stored procedure in the same order as you have Them in the stored procedure. Choose the Variable that it corresponds to on the other side. So you might see this.

From here hit Ok and you should have your parameters set to the proper information.

Hope that helps!