June 5, 2012 at 4:47 am
Hi,
Created a SSIS package in Dev.
Created a table Sample_Config in database with below columns with datatype nvarchar
ConfigurationFilter, ConfiguredValue, PackagePath
Need to create a config file using SQL Server, so that the package will be run in Dev and test environment if we updated the table.
Please help me how to create config file and how to update the table
June 5, 2012 at 5:01 am
Open the package in BIDS.
Right click and select "Package Configurations..." or click on SSIS menu and select "Package Configurations..."
Click on "Enable package configuration" check box.
Click on "Add..." button.
....
June 5, 2012 at 5:31 am
Thanks Suresh.
But i need to set the config file from SQL Server.
Inserted the config script in Sample_Config table.
Done the following things.
Package Configuration -----? Right Click -----------?(Wizard Opened) Selected Config Type as SQL Server -----? selected connection and config table
I need to know the other steps
June 5, 2012 at 6:57 am
What I understand from question is:
You need to enable SQL configurations for your package.
For this you need to have two connection first connection will point your package to SQL configuration table and second connection will be the SQL configuration table.(This is called indirect configuration)
For First connection you can create an environment variable or you can creat a XML configuration file.
In Env variable: value would be.
Data Source=<Your database server>;Initial Catalog=<Configuration database name>;Provider=< Use a provider I use SQLNCLI10.1>;Integrated Security=SSPI;
For SQL configuration step. You need to have one entry for every connection.
That entry should be chosen as Configuration filter for that particularr connection.
For example you have two connection one for SQL and one for Oracle you will have two configuration entries in the SQL config table. While in the package there should be three entries one for environment variable and one one for Oracle and SQL connection each.
June 5, 2012 at 9:10 am
Thanks shailesh thapliyal
🙂
June 5, 2012 at 11:50 pm
Is it necessary to use XML config or Environment variable? Without using them, is there any other way to use SQL Server table for setting configuration?
June 6, 2012 at 12:20 am
sqlstud (6/5/2012)
Is it necessary to use XML config or Environment variable? Without using them, is there any other way to use SQL Server table for setting configuration?
Hi Sqlstud,
No it is not necessary to use XML config or Environment variable, but, it is of great benefit, anyway, you can setup an Sql Server table to set your packages configurations; please follow these steps:
1. You need a table at your Sql Server (I named mine SSIS Configurations), the table structure is shown below, with some records.
2. Right-click at your package control flow and select Package Configuration as shown below
3. You will get an screen like the one below, those entries are part of my tests, in your case you will get it empty.
4. Select Sql Server from the Configuration Type drop-down, as shown below
5. Select Sql Server details to reach your configuration table, e.g. SSIS Configurations shown at step 1; you will need to create a connection (in your package) to the Sql Server containing your configuration table before you can add use it as described by these steps.
Cheers,
Hope this helps,
Rock from VbCity
June 6, 2012 at 12:37 am
Thanks Rock..
But i need to use it in different environments ie Dev and Test etc...
How can we pointing to different environments?
If we run the package, it should take the corresponding environment details from the table and to be execute accordingly
Regards
SqlStud
June 6, 2012 at 12:43 am
Rock from VbCity (6/6/2012)
... please follow these steps:...
Thanks for the detailed post with screen shots.
You can publish it as a blog or article. Becuase, I believe no one has published anything about it so far.
Regards.
June 6, 2012 at 1:04 am
sqlstud (6/6/2012)
Thanks Rock..But i need to use it in different environments ie Dev and Test etc...
How can we pointing to different environments?
If we run the package, it should take the corresponding environment details from the table and to be execute accordingly
Regards
SqlStud
That is when an external Xml file and environment variable work together, you turned off that option
Hope this helps,
Rock from VbCity
June 6, 2012 at 1:11 am
Suresh B. (6/6/2012)
Rock from VbCity (6/6/2012)
... please follow these steps:...
Thanks for the detailed post with screen shots.
You can publish it as a blog or article. Becuase, I believe no one has published anything about it so far.
Regards.
Thanks Sûresh,
I will follow your advice, I am fairly new to SSC and unfamiliar with its features, I think I should email Steve Jones to find out more; I had written articles in the past at VbCity.
cheers
Hope this helps,
Rock from VbCity
June 6, 2012 at 1:17 am
Thanks Rock.
That means, if we update the table to Dev, it will execute it in Dev environment and update the tabel to test, it will execute it in Test environment?
( I cant use any xml and environment variable?
Whether my understanding is correct?
Regards
SqlStud
June 6, 2012 at 1:28 am
Your package will have a connection to a database containing the configuration table, if your dev and prd enviroments share this server-database you will be fine, if they do not, then you should consider the external xml config file as a minimum.
Hope this helps,
Rock from VbCity
June 6, 2012 at 1:40 am
Thanks Rock.
I have the below Configuredvalue in SQL server table for config
Data Source=Sample_Dev;Initial Catalog=Medical_Loss;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Packet Size=32767;Application Name=SSIS-Load OAM_Data-{C1404D63-52E4-4629-86C5-7CF476146C02} Sample_Dev. Medical_Loss;
Dev Server : Sample_Dev
Db Name : Medical_Loss
If we update the test server and db name in the table, whether it will be executed?
Test Server : Sample_Test
DB name : Medical_Loss
Regards
Sqlstud
June 6, 2012 at 1:47 am
Actually sqlstud,
There is a third option, setting up a package parameter taking the name of your target (dev or prd) server, then internally build the connection string that you will be using.
Hope this helps,
Rock from VbCity
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply