June 5, 2012 at 3:42 pm
I have a package working find on SQL Server 2005 when fired from a SQL Server Agent step configured using the "SSIS" type of Step.
My package is configured in two steps:
1) an environment variable gives me a database connection (to 2008R2)
2) various package properties are configured from a table on this db connection
So far so good, I create the environment variable from the Computer System settings and everything works really fine.
Problem...
My customer wants to run both PreProd and Prod on the same machine.
I know it does not sound a great idea but this is not the issue.
Because my environment variable is setup at the system level, I can only have one environment per machine.
So, I thought I'd start DTEXEC myself as a command line instruction so that I could setup my environment variable in a cmd script just before firing DTEXEC.
Unfortunately, I just can't get this right...
I took the parameters from the SQL Agent job step I had initially created and this means I should start something like this...
[font="Courier New"]
set SSIS_ROOT="Data Source=MyServer;Trusted_Connection=Yes;Initial Catalog=MyDB;Provider=SQL
NCLI.1"
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\dtexec" /FILE "C:\...\MyPackage.dtsx" /CHECKPOINTING OFF /SET "\Package.Variables[User::Instruction].Properties[Value]";"in_Clients" /REPORTING E
[/font]
Now, when I try to run it, it seems to ignore the SSIS_ROOT env. var. and times out when trying to connect to setup each package property from the database.
Any idea what I might be getting wrong?
Thanks
Eric
PS: just in case this is relevant...
I run Win 7 32bits (Sorry, I got the title wrong...) with SQL 2005, 2008 and 2008R2 installed on the same box
June 6, 2012 at 12:03 am
In such a scenario I wouldn't use environment variables but an XML config file.
You can specify this XML config file in the jobstep.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 6, 2012 at 2:20 pm
I suppose I could.
If I have to modify my package, I might even be tempted to simply store the configuration database connection string as a variable and pass the variable value to the package through SQL Server Agent...
Having said that, is there any particular reason why I should be struggling with the environment variable approach?
June 6, 2012 at 2:23 pm
Eric Mamet (6/6/2012)
I suppose I could.If I have to modify my package, I might even be tempted to simply store the configuration database connection string as a variable and pass the variable value to the package through SQL Server Agent...
Having said that, is there any particular reason why I should be struggling with the environment variable approach?
You're struggling because you have multiple environments on one machine.
Environment variables only work perfectly if there's only one environment.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply