 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Integration Services / May 2008Configuration woes
Hi, I guess I'm not understanding how SSIS configuration works. I have an SSIS package which I run under Sql Server Agent. I want to setup multiple jobs to run the same package with different configurations. What I have so far is I used the Configuration Manager in BIDS, and an indirect Xml file to store the settings. In Sql Server Agent, I have set the job to execute the package and specified in Configurations to the proper file. The initial export works fine. I setup another job the same way, but pointed it to a different configuration file. This job fails because of a logon failure (the two jobs have different proxies which have different logon credientials; so package A's logon can't access database B, and package B can't access database A). For some reason, this second job is still trying to get to database A when it executes. I then goto the SSIS step and change the settings in the Data Sources tab. Still the package goes to the wrong database. What am I missing? Thanks Andy My guess is that when you say "pointed it to a different configuration file" you are actually updating the environment variable that handles the indirect configuration. Is this correct? The problem with this approach is that Windows processes only read in environment variables when the process starts up. This means that until you restart SQL Server Agent, your packages will keep using the old configuration file. It also means that any approach that is based on updating environment variables is pretty much doomed to failure. The approach that I would take here would be to remove the configurations from the package itself, update the Agent jobs to use the CmdExec (Operating System Command) job step type and call DTEXEC.EXE with the /CONF switch ( http://msdn.microsoft.com/en-us/library/ms162810.aspx ) to explicitly specify the correct XML config file for each execution. > Hi, > [quoted text clipped - 20 lines] > Thanks > Andy > My guess is that when you say "pointed it to a different configuration file" > you are actually updating the environment variable that handles the indirect > configuration. Is this correct? No, I was talking about when I edit the SSIS Execute Package step, and go to the Configurations tab. > The problem with this approach is that Windows processes only read in > environment variables when the process starts up. This means that until you > restart SQL Server Agent, your packages will keep using the old configuration > file. It also means that any approach that is based on updating environment > variables is pretty much doomed to failure. Ok.. > The approach that I would take here would be to remove the configurations > from the package itself, update the Agent jobs to use the CmdExec (Operating > System Command) job step type and call DTEXEC.EXE with the /CONF switch (http://msdn.microsoft.com/en-us/library/ms162810.aspx) to explicitly specify > the correct XML config file for each execution. Ok.. I guess I really don't see the point of the configuration in a SSIS package then. It seems almost useless if you're still tied to one file at a time. Any place that I can request a more sane setup? Like being able to do this via the SSIS step? Thanks Andy I removed the configuration file setting from the package and re- deployed. I then set the SSIS step again to use SSIS. It now complains it can't find one of the views used by my package. I tried using the cmdExec again, and it the initial command line it gives seemed to mirror the options set when the step was still SSIS. I get the same error using the cmdexec as I did the SSIS step. I'd like to know more about the error, but for some reason several useful messages are truncated in the log. Great. Where I can find the log before Sql Agent chops it to bits? This really shouldn't be as difficult as it has been.. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.