Config File in SQL Server Database
-
How do I add Package Configuration File Stored in SQL Server Database (table) when I am running that package as a job in SQL Server Agent. When I create a job and go to the steps I can easily add a configuration file that is stored as an XML but what if the configuration file is stored in some SQL database? I don't see any option to map to the SQL table. Any help/input will be appreciated. Thanks.
-
Or could it be that SQL Server Configuration is only available when the deployment method is "SQL Server". Here I did a file system deployment and for security issues I stored the configuration in SQL server- and tryin to use but i don't see the option....
-
Right-click your Control Flow design surface and choose Package Configurations...
Click Enable, then Add, and you can choose SQL Server:
Look for the "SQL Server Configurations" section in this article:
Understanding Integration Services Package Configurations
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (6/5/2012)
Right-click your Control Flow design surface and choose Package Configurations...Click Enable, then Add, and you can choose SQL Server:
Look for the "SQL Server Configurations" section in this article:
opc.three,
my query was more related/based on implementing config files from SQL table during run time through SQL Server Agent. In the attached JPG when you click on configurations and click on add, you only get the option to load file based config files. I want to load from a SQL table??
-
The use of a table is in lieu of a file. Have a look at the link I posted. It explains all the types of package configs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (6/5/2012)
The use of a table is in lieu of a file. Have a look at the link I posted. It explains all the types of package configs.I have been to that page a couple times, it explains everything well except for the part that i am really looking for, ie. how to map to SQL table config file through SQL Server Agent. Since I am here, i also wanet to ask you.... i have been experiencing this rather weird problem.
I have a package that has package configuration enabled. I tried two configuration files FileA and FileB. FileA and FileB have almost same properties (connection strings) except that File B also has some variable properties. Now I mapped my package to run using these config values one after another. If a change something in FileA (say try wrong password), the package fails. If i try the samething in FileB, the package succeeds. This happened both in SQL Server Agent as well inside BIDS. It looks like when the package doesn't like the config file, it decides to take the default values from the design time...but what makes them make that decision?
-
gravitysucks (6/6/2012)
opc.three (6/5/2012)
The use of a table is in lieu of a file. Have a look at the link I posted. It explains all the types of package configs.I have been to that page a couple times, it explains everything well except for the part that i am really looking for, ie. how to map to SQL table config file through SQL Server Agent.
SQL Server Agent has nothing to do with it. Setup the package to get its config data from SQL Server and whether you run the package from BIDS, the command line directly using dtexec, via an Agent job the package or any other method the package will look to the SQL Server table for its config data.
Since I am here, i also wanet to ask you.... i have been experiencing this rather weird problem.
I have a package that has package configuration enabled. I tried two configuration files FileA and FileB. FileA and FileB have almost same properties (connection strings) except that File B also has some variable properties. Now I mapped my package to run using these config values one after another. If a change something in FileA (say try wrong password), the package fails. If i try the samething in FileB, the package succeeds. This happened both in SQL Server Agent as well inside BIDS. It looks like when the package doesn't like the config file, it decides to take the default values from the design time...but what makes them make that decision?
There is a precedence when it comes to runtime values. Ideally your config files will contain mutually exclusive sets of properties. Bing for "SSIS configuration precedence" for more info.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three (6/6/2012)
gravitysucks (6/6/2012)
opc.three (6/5/2012)
The use of a table is in lieu of a file. Have a look at the link I posted. It explains all the types of package configs.I have been to that page a couple times, it explains everything well except for the part that i am really looking for, ie. how to map to SQL table config file through SQL Server Agent.
SQL Server Agent has nothing to do with it. Setup the package to get its config data from SQL Server and whether you run the package from BIDS, the command line directly using dtexec, via an Agent job the package or any other method the package will look to the SQL Server table for its config data.
Since I am here, i also wanet to ask you.... i have been experiencing this rather weird problem.
I have a package that has package configuration enabled. I tried two configuration files FileA and FileB. FileA and FileB have almost same properties (connection strings) except that File B also has some variable properties. Now I mapped my package to run using these config values one after another. If a change something in FileA (say try wrong password), the package fails. If i try the samething in FileB, the package succeeds. This happened both in SQL Server Agent as well inside BIDS. It looks like when the package doesn't like the config file, it decides to take the default values from the design time...but what makes them make that decision?
There is a precedence when it comes to runtime values. Ideally your config files will contain mutually exclusive sets of properties. Bing for "SSIS configuration precedence" for more info.
Thank You opc.three. Your help is appreciated as always!!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply