SSIS global variables and execute process task

Within SQL Server 2000, if you wanted to execute a command line task (e.g. execute a BCP statement within DTS such as "bcp <arguments>"), a common task would be to use a ActiveX Script Task to create your command line statement.  This command line statement would then be placed within a global variable.  Then a dynamic variable task would be used to take the global variable value and place it within the Arguments property of the Execute Process Task.
 
For SQL Server 2005, you can use SQL Server Integration Services (SSIS) to do this task much simpler.  You can still use a Script Task (e.g. using Visual Basic .NET) to create your command line statement and load this statement into a variable.  But skipping the dynamic variable task (which no longer exists), you can modify the Execute Process Task at the point of the Property Expression attribute. This attribute can then load the Arguments property of the Execute Process Task directly. 
 
For more info on the “Property Expression” attribute can be found within the “Expression Builder” at http://msdn2.microsoft.com/en-us/library/ms181412.aspx
 
As you can see from the "Execute Process Task Editor", you can set the Global Variable DateInfo (within the "Property Expression Editor" it is @DateInfo) to the Execute Process Task Arguments attribute.  For example, if you had a command line statement of:
       LogInfo.exe <DateInfo>
 
where <DateInfo> is a command line argument, the DateInfo variable will contain the date information that normally is passed to the LogInfo.exe executable.  Presuming your Script Task creates the correct date and props it into a variable, associating the "Process Task Editor" Arguments attribute to this variable will allow the SSIS package to dynamically change the arguments to execute the "Execute Process Task".
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s