Hi there,
I've got an urgent thing I need to resolve.
I have a package that has a file connection. We have set the connection in the package for development - we also have the connection in a config file.
We are calling the package from SQL Jobs. In the step we call the package in, we try to set the value for the connection in the Data Source tab. However, the package does NOT use the value we enter.
I then removed the connection from the config file - then the package uses what the connection was set to in development. It still will not use what we enter in SQL Jobs.
Furthermore, when we run the package we lose the values that we put in the DataSource tab!!!!
My work around has been to change the SQL Job type from an INtegration services package to a straight command line - this works.
The question is - can you set a package's connection strings from SQL Jobs using the SSIS Job Type
Thanks!!!
URGENT!!!! SSIS Configurations and SQL Jobs
Andreas Fabri
Hi Darren,
I've kept the step as a CmdExec step and it works OK.
Your right, the configuration file is now redundant. This is becuase we now want to point the package at different files depending on the job (as opposed to before when we just had one file) - so we want the Job - the SQL Job that is - to tell the package what file to process.
I think the issue still is that SQL Jobs (using the SSIS step type) gives you the impression that you are setting the data source by editing it in the Job set up screen - the command line it generates also gives that impression. Yet when the job runs the editing that has been done is simply deleted.
I'm not 100% sure how SQL Jobs is meant to inetgrate with SSIS packages but it's not really intuative.
Thanks for the reply.
Cheers,
Dave.
Susan Still
I cannot say if this is a problem or not, but what I can offer in mitigation is don't worry too much, you can keep using a CmdExec step. I always do as I much prefer the job step output logging options, and some cases it has proved invaluable as outlined here-
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html
Not sure why you need to set the DataSource if you are also using a configuration file, that seems redundant, or maybe I misread.
If this is really urgent, then open a support case with MS Product Support Services.