Hello,
Here is the following mind-numbing problem I have (and wished I did not have to experience)
A set of 2 SSIS packages is scheduled to run in a sql server agent job on the same server. Both packages use an environment variable that point to a package configuration file. In this file there are 2 connections, one to a sql server with a sql server user id and passwordn, another to an AS400 DB2. Both packages are deployed on the same server in SSIS server under MSDB sql storage, with package protection level set to 'rely on server storage and roles for access control'.
Today the connection to the As400 needed to change, it is now connected to another AS400 server. The packages have been modified to use the new connection. In the configuration file the old connection has been commented out and the new connection string was added, the connection itself was given a new more meaningfull name in the packages.
Running the packages from visual studio 2005 works. After testing I have deployed the packages to SSIS server in MSDB storage.
Now when I start the sql server agent job that runs these packages, the job quits with an error, in the history I see an error message that it failed to connect to the sql server with the given sql server user account.
When I the step in the sql server agent job properties for both packages, under the Tab 'Data sources' I see that it is using the new AS400 connection. I can also see the connectionstring for the sql server with the user id (but no password).
To make it possible for my packages to run (the users are waiting for the data) I have solved it like this:
- under the 'configurations' tab I have added the name of my package configuration file.
- i did this for both packages in both job steps
when I run the job , it works without problems.
Now , my question is: I have hardcoded the pathname for the package configuration file in my job. Instead of the package using the environment variable to find th epackage configuration file. I would prefer my packages , when in a sql server agent job to also use the environment variable. What can I do to make this happen?
Flabbergasted as always,
Hello,
I have found the cause of the problem and the answer to my question.
I forgot to mention one of the steps of the modification: the package configuration file was moved to another drive, to centralize all configuration files on the same drive and folder. The environment variable fior the config file had been changed to point to the new and correct location, the config file had been deleted from the old location. But the SSIS server had NOT been restarted.
therefore the probable reason of the problem was: when the job was run, the package was given the in memory environment variable value from SSIS server and did not find the package configuration file, it then used the connections string information kept in the package. but in a package the password is never stored, so it could not connect to the sql server. It may also have received the connection from the sql server agent job (in the 'Data sources' tab under the job step properties) but in there the password is not kept either.
I have copied the package configuration file back to the old drive and folder and removed the package configuration file name from the 'Connections' tab in the job step properties. After that the job ran without problems.
Conclusion: I have to restart the SSIS service to read the environment variable again , so that it reads from the config files in the new folder.
question : is there some other way to force SSIS to read environment variables?
It is hard to think of a sentence that contains the words 'developer-friendly' and 'SSIS' (without negation). Sentences that contain the words 'mind-boggling', 'blistering barnacles' or 'cumbersome' together with 'SSIS' are easier to think of.
|||I would agree with Jan Dhondt.SSIS package execution is not consistent under different environment and may function abnormally. Very fundamental issues have not been dealt with, the result is sleepless nights for developers like me.
This is what I am stuck with:
I have a SSIS package, which dynamically connects to a flat file data source and loads the data to an OLE DB destination.
The package when executed from the command prompt, with the 'dtExec' utility, and executed from structured file storage works fine and loads the data from any folder location without any error. It also executes fine, when executed from Query designer or SQL Agent Job, if the flat file data source resides in the same directory as the package or any of sub directories of the saved package; but when the package is executed from the Query designer or SQL Agent Job, and when the data source file, is not saved in the directory or sub directories same as the package source the package gives throws a strange error:
Code: 0xC001401E
Source: SSISPackage_Data_Import Connection manager "FlatFileConnectionManager"
Description: The file name "\\filepath\Training_090909.txt" specified in the connection was not valid.
Also the variable(varFilename), which dynamically stores the location of the flat file data source, has a valid default value, so it shouldn't give any design time validation error. I don't have a package configuration file.
Any help on this would be appreciated.
Hope that MS would deal with such issues before it is too late.
Thanks
|||If the package is started from a sql agent job, it may have different credentials than normal, and therefore may not have read access to the txt file. Is this the case?
|||I am trying to run the package in a T-SQL statement.