March 12, 2011 at 10:38 am
I am attempting to run an SSIS package from a job. I need to run the package twice giving passing it a parameter each time (previous_month, current_month e.g.). This should be straight forward (it was in DTS) however the "Edit the command line manually" function on the "Command Line" tab does not work.
When I select the needed package from SSIS Package Store then select the "Command Line" tab, it looks like this, which is fine without parameters.
/DTS "\MSDB - MyStore\MySubFoler\MyPackage" /SERVER "MyServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
However, when I make changes using "Edit the command line manually," everything disappears. That is, the server name ("MyServer") and the parameters I meant to pass. Then it looks like this.
/DTS "\MSDB - MyStore\MySubFoler\MyPackage" /SERVER /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
- Tony C
March 12, 2011 at 10:39 am
I should have mentioned, I am running SQL Server 2005 EE.
- Tony C
March 13, 2011 at 9:45 am
As an aside, if you need help with SQL 2005 it's best to post in the SQL 2005 Forums otherwise you may get 2008-specific responses.
For you particular issue, and this should work on 2005 or 2008, if you're using the SSIS step-type you'll want to set your variable in the "Package Variables" tab.
If you find yourself needing to edit the command line manually you would be better off using a CmdExec step-type to call DTEXEC directly rather than using the SSIS package step-type as you'll have more control over the command line and won't run the risk of the UI eating your settings.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 13, 2011 at 2:06 pm
Thanks for the reply. Sorry about posting to the 2008 forum...as soon as I sent my reply stating it was a 2005 issue, a "well duh" went off in my head. Too late by then.
Anyway, I set the "Property Path" and "Value" on the "Set value" tab. That worked as I wanted it to. The syntax is a bit tricky but here's what I ended up with (for those who may stumble across this):
"Property Path"
\Package.Variables[User::v_run_type].Properties[Value]
"Value:
current
Where "v_run_type" is the user variable that I was trying to set and "current" is the value I want to pass to the SSIS package for examination and further processing. In my case, current month processing.
I created 2 steps that call the same SSIS package. The 2nd step has a value of "previous" for previous month processing.
Clue for others: to get the Property Path syntax, create a configuration in BIDS - "SSIS | Package Configurations | Add" and follow the wizzard to the last step. There will be a listing of "Properties:" with the paths for the items you select.
- Tony C
- Tony C
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply