SSIS Package not using the XML Config File

Question

SSIS Package not using the XML Config File

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 11, 2010 at 12:13 pm

#157144

Hello Everyone
This is actually for SSIS 2008
I am in the process of troubleshooting a package migration from the Dev box to the SSIS Server. There is an XML config file that is mated to the package. The Command line has the /configfile attribute with the correct path.
But when I execute the package, the config file is never called, and the hard coded values like the database connection is used from within the package, and never uses the values in the config file.
What could be causing this?
Thank you in advance
Andrew SQLDBA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 1

AndrewSQLDBA (8/11/2010)
Hello Everyone
This is actually for SSIS 2008
I am in the process of troubleshooting a package migration from the Dev box to the SSIS Server. There is an XML config file that is mated to the package. The Command line has the /configfile attribute with the correct path.
But when I execute the package, the config file is never called, and the hard coded values like the database connection is used from within the package, and never uses the values in the config file.
What could be causing this?
Thank you in advance
Andrew SQLDBA

In the Agent Job Step Properties make sure your configuration file is added to the "configurations tab".

Thanks,

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

It is. I have checked everything in every place that I know of, or can think of.

I have compared this one that does not work, with one that does work, that is is using an XML config file.

Everything looks exactly the same, one works, and one does not.

Andrew SQLDBA

WayneS SSC Guru Points: 95460 More actions · Answer 3

I thought that to use a configuration file, that you had to specify it in the SSIS package itself? I thought that was how it knew to load it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

You can do that. I thought the same thing, until I started looking thru the actual XML code that is generated. All that you really have to do is place the /configfile line in the command line that fires off the package. I am sure there has to be some kind of tie to tell the package to use a config file

I use all database tables to store my configs. That is actually listed in the XML code of the package.

I am still learning about SSIS, but it is so much better than DTS.

Andrew SQLDBA

Baskar B.V SSCrazy Points: 2823 More actions · Answer 5

AndrewSQLDBA (8/12/2010)
You can do that. I thought the same thing, until I started looking thru the actual XML code that is generated. All that you really have to do is place the /configfile line in the command line that fires off the package. I am sure there has to be some kind of tie to tell the package to use a config file
I use all database tables to store my configs. That is actually listed in the XML code of the package.
I am still learning about SSIS, but it is so much better than DTS.
Andrew SQLDBA

Can you try to create a new config. file in the SSIS package which is not using the xmlconfig with the same parameters as defined in the xmlconfig?. You can create it under SSIS menu - > Package Configurations.

Once you configure all the parameters try to run it from command line using the xmlconfig file and see whether it works.

When we do this way, i think the package would get related to the config file.

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

WayneS (8/11/2010)
I thought that to use a configuration file, that you had to specify it in the SSIS package itself? I thought that was how it knew to load it.

Actaully to associate it with the package you can just add the file in the SSIS package config wizard. In the agent job step properties, you will have to point to that config file by adding it under the Configuration TAb.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 7

Found a fix for this issue:

Once you change or edit the config file, and then copy it up to the SSIS server, you need to remove and add the file back into the SQL Job, under steps --> Configure

Even thought the file is the same name, the job will actually look thru the config file and verify everything such as the paths, variable names, etc.... SQL will then cashe that file. Only way for a new one to get processed, it is to remove and add the new file into the Configure tab of the job step.

Very similar to the environmental variables, but you just to not have to bounce the SQL service.

Andrew SQLDBA

Baskar B.V SSCrazy Points: 2823 More actions · Answer 8

Once you change or edit the config file, and then copy it up to the SSIS server, you need to remove and add the file back into the SQL Job, under steps --> Configure

Even thought the file is the same name, the job will actually look thru the config file and verify everything such as the paths, variable names, etc.... SQL will then cashe that file. Only way for a new one to get processed, it is to remove and add the new file into the Configure tab of the job step.

I don't think so. For (e.g) in job scheduler like control-m or so we just execute SSIS by passing the parameter as

dtexec.exe <<filename> \configfile <<filename>

In those scenerio, if i modify the config file what change i need to make? I think if we add the config file in the package using SSIS menu -> configuration, then it is enough the package should able to point to the xml config file.

[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 9

Very interesting find to say the least, never notice that behavior in my packages???

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 10

I am not saying that is what for "Absolutely Certain, 100%" was the fix, but once I did that, the package started using the values in the XML config.

This is why I am changing everything over to be stored in a SQL table. No more issues with files. Reminds me of Unix and Oracle.

I almost cannot even type that "O" word.

Andrew SQLDBA

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 11

AndrewSQLDBA (8/12/2010)
Found a fix for this issue:
Once you change or edit the config file, and then copy it up to the SSIS server, you need to remove and add the file back into the SQL Job, under steps --> Configure
Even thought the file is the same name, the job will actually look thru the config file and verify everything such as the paths, variable names, etc.... SQL will then cashe that file. Only way for a new one to get processed, it is to remove and add the new file into the Configure tab of the job step.
Very similar to the environmental variables, but you just to not have to bounce the SQL service.
Andrew SQLDBA

I have never notice that behavior in my packages as well. If the config file is added to the package and updated all you should do is redeploy the package and you're good. You shouldn't have to do anything with the config file (remove/add) under the configuration tab in SQL Agent.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends