Issue with SSIS Package Configurations in SqlServer

Hello everyone,

I am working on a SSIS project and I am facing an issue for getting the configuration settings of the package, once it is deployed and executed from SQL Server agent.

The package uses two configuration types: (listed bellow in the order they are appeared in the configuration editor)

Config1 - Xml configuration file - for storing the database connection string.

Config2 - SQL Server - for storing some user defined variables. It uses the same database as specified in Config1.

Everything works fine and the package uses the database configuration values as defined in Config2, if I execute it from Visual Studio,

However, the package doesn’t get the configuration settings from the database when I try to execute it as a SQL Agent job.

There aren’t any errors and the package executes all tasks successfully, using the connection object Config1 (the same we use to get the config parameters from the database) and the default values of the user defined variables.

It works ok, if I change Config2 to be of type XML configuration file.

There could be two problems:

1. SQL server agent doesn’t read the configuration from the database and I am not quite sure how to set this. In Agent/ Job step properties screen/ Configurations tab I can only browse for a config file. I can also use the command window and /CONFIGFILE option to specify xml file, but how to use it in a case of a database configuration? Is there a /CONFIGDATABSE option or /CONFIGFILE works with database connection as well. I tried with /CONFIGFILE and database connection, but it doesn’t seem to work.

2. SQL server agent doesn’t get the configurations in the specified order. In my case,

it could try to read Config2 first, but at that moment it doesn’t have the database connection from Config1 and it fails. Again, I am not sure how to set the sequence.

Thanks in advance for your comments.

ITHave you specified a full path for Config1?|||

Yes, I specified the full path for Config1. I used the configuration tab to select the file and in the command line window it shows the full path.

The package itself uses the connection from Config1 for the data flow tasks and they work without any issues.

|||

Hi,

What kind of user defined variables are you fetching from the database?

Can you try storing them in package level variables by using a Script Task?

Regards,

B@.ns

|||

I think you can discard option 2. The package configurations should be processed in the order in which they are stored.

It sounds like a permissions problem with the SQl Server Agent account.

Have you tried profiling (SQL Profiler) the package execution to see if it is generating any SQL errors trying to read the configuration table? If the package fails to read the configuration, it only raises a warning, not an error, so it is not always obvious when a configuration fails to load.

|||

I couldn’t find any errors or warnings during the package execution.

Moreover the same connection is used for all data flow tasks inside the package and the SSIS logging (it uses SSIS log provider for SQL) and all of them work without any issues.

As a workaround I can add a SQL script to read those variables from the database, but this will duplicate the configuration functionality.

The idea was to use the existing SSIS database configuration feature instead of building a similar one from scratch.

|||Are you changig the path of the Conf1 in Agent, different than one you have when building the package?|||I.T.

See if you this blog spot can help you identifying the issue:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

It uses an environment variable instead of a file|||

Hi Rafael,

I checked your blog (actually this was one of the first materials I found when I started exploring the issue a week ago), but in our case we have some system restrictions for using environment variables.

In your example you mentioned also that you used it successfully with XML file.

I would like to ask you if you were to deploy it and execute it as a SQL agent job?

Thanks,

IT

|||Yes, I did, and as far as I remember it worked ok. The only problems I can remember are stupid things like the XML file was not accessible for the account running the SS Agent; not updating the connection string properly withing the XML file (hence trying to connect to the wrong instance), etc. But most of the times, the package logging will tell you if something is wrong with any of the configurations and even shows the order in which the configurations were applied.|||

Rafael,

I would like to ask you if you remember how you set the sql configuration in the SQL Agent job. Did you do something special for this?

I couldn’t see any available options to set explicitly the configuration as SQL server (the UI allows only to browse and select config file).

The package logging is set to log all events and it doesn’t show any errors during the execution.

You mentioned also that the package logging can tell the order of the configuration, but I couldn’t see that kind of information. Is there a special custom event for this?

Thanks,

IT

|||

I just ran some tests using XML and Table configurations (in that order) and works fine in BIDS and as an agent job.

I didn't have to do any special thing in the job step (CmdExec); just to a command like:

DTEXEC /FILE "{path}\Configurations demo 2.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

But what I could notice is that the messages about package configurations are not shown in te progress tab; I recently updated to SP2, and I wonder if that has been changed. I dind't even received any warning when I deliberated removed the xml config file...not even in the package logging|||

I. T. wrote:

I couldn’t find any errors or warnings during the package execution.

This is normal behaviour in my experience. I didn't get any warnings or errors either.