SQL Server Integration Services Package Configuration

By:   |   Updated: 2008-01-03   |   Comments (106)   |   Related: 1 | 2 | 3 | > Integration Services Configuration Options


Problem

As we develop, test and deploy SSIS packages, we need to specify different parameter values for things like database connection strings, file and folder paths, etc. without having to edit the individual SSIS packages to make these changes. How can we take advantage of the configuration capabilities that SSIS provides, particularly the SQL Server package configuration, to accomplish our goal?

Solution

SSIS provides several options for handling package configuration values such as environment variables, XML files, registry settings, and a table in a SQL Server database. In each case you are storing and maintaining configuration parameter values outside of the SSIS package then using those stored values when executing the package. There are several advantages to using SQL Server package configuration over the other options such as:

  • Storing package configuration values in a SQL Server database rather than on the file system or in the registry allows us to include this data in our standard SQL Server backup and restore plans.
  • Changing the configuration data can be done with simple T-SQL commands such as INSERT, UPDATE and DELETE.
  • DBAs are usually more comfortable working with SQL Server tables than XML files or registry settings.

Before we walk through the steps to setup SQL Server package configuration in an SSIS package, there are two preliminary things that need to be done. First, you need to decide on the database server and database name to store the configuration data; for our example we will use the local server and SSISConfig as the database name. While not a requirement to store the SQL Server package configuration in its own database, it is usually a good idea to do so. Second, you should setup an environment variable that holds the database connection string to the package configuration database on the machine(s) where your SSIS package will run. We will configure the SSIS package to get the database connection string for the package configuration database from the environment variable, allowing us the flexibility to change the configuration database as the SSIS package goes through the development, testing and deployment phases.

To setup the environment variable, use the standard Windows Control Panel System applet and create a system environment variable. For example, use SSIS_CONFIG_DB as the environment variable and assign this OLE DB database connection string as its value:

Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Now we are ready to setup an SSIS package to use SQL Server package configuration. To begin, open Business Intelligence Developer Studio (BIDS) or Visual Studio. We will perform the following steps:

  • Create a new Integration Services project
  • Add a new SSIS package to the project; the steps following are performed in this package
  • Add Connection Managers for the SSISConfig and AdventureWorks databases (or any other database of your choice)
  • Add an environment variable package configuration to assign the SSISConfig database ConnectionString property
  • Add a SQL Server package configuration to assign the AdventureWorks database ConnectionString property

The detailed steps with screen shots follow:

1. Create a new project; click File, New Project:

Create project

2. Select Business Intelligence Projects as the Project Type, Integration Services Project as the template; fill in the Name and Location (change as necessary):

New Project dialog

3. Add a new SSIS package to the project; right click SSIS Packages in the Solution Explorer, then select New SSIS Package from the popup menu.

Solution Explorer

4. Add the Connection Manager for the SQL Package Configuration database; right click in the Connection Managers area of the new package and select New OLE DB Connection

New Connection Manager

5. Configure the Connection Manager to point to the SSISConfig database:

Configure the Connection Manager

6. Repeat steps 4 and 5 to add another Connection Manager; point to the AdventureWorks database (or the database of your choice); we will use SQL Server Package Configuration to assign the ConnectionString property of this database.

add another Connection Manager

7. Right click on the design surface and select Package Configurations from the popup menu:

Package Configurations

8. Click Enable package configurations then click Add:

Enable package configurations

9. Add the SSIS_CONFIG_DB environment variable configuration then click Next:

Add the SSIS_CONFIG_DB environment variable

10. Set the ConnectionString property of the SSISConfig Connection Manager to the value of the environment variable:

Set the ConnectionString property

11. Name the environment variable configuration:

Name the environment variable

12. Add a SQL Server Package Configuration, select the SSISConfig Connection Manager for the Connection; click the New button (cut off in screen shot below) next to Configuration table and accept the default to create a configuration table, and finally enter AdventureWorksConn as the Configuration filter:

Add a SQL Server Package Configuration

13. Set the ConnectionString property of the AdventureWorks Connection Manager to the value of the SQL Server package configuration:

Set the ConnectionString property

14. Set the Configuration name and click Finish:

Set the Configuration name

15. Review the Package Configuration Organizer; you should see two configurations:

Review the Package Configuration

16. Open the configuration table in SQL Server Management Studio to view the configuration (see step 12 above for the database name and table name):

Open the configuration table in SQL Server Management Studio

The schema of the SQL Server package configuration table (as shown in step 16 above) includes the following four columns:

  • ConfigurationFilter - consider this as the unique key for the table; when you update the configuration table using a T-SQL UPDATE statement, this is the value you use in your WHERE clause
  • ConfiguredValue - this column contains the configuration parameter value and is the one that you edit
  • PackagePath - this column identifies a single property value in your SSIS package to be set to the value in the ConfiguredValue column
  • ConfiguredValueType (cutoff in screen shot above) - the type of ConfiguredValue, usually String

Caveats:

  • When you add an Environment variable configuration (see step 9 above) and the environment variable does not appear in the Environment variable drop down list, close BIDS and reopen it. Any environment variable added after opening BIDS will not show up until you close and reopen.
  • When you are working with an SSIS package in BIDS, the package configuration is read when you open the package. Any changes made to the configuration will not be reflected until you close and reopen the SSIS package.
  • You can use an Environment variable package configuration to specify the ConnectionString property of the Configuration database Connection Manager, allowing you to change the server or database that holds the package configuration data. However, the table name that you specify (see step 12 above) is hard-coded in the SSIS package.
Next Steps
  • Experiment with the SQL Server package configuration and see if it meets your configuration needs.
  • Think of the SQL Server package configuration table as a group of parameter values for a particular application; you can use a different configuration table for each application, allowing you to specify different values as appropriate. Remember that you specify the Configuration table name when you define the package configuration (see step 12 above).
  • Stay tuned for an upcoming tip on defining reusable SSIS package templates; the SQL Server package configuration is an integral part of creating reusable package templates.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-01-03

Comments For This Article




Wednesday, November 15, 2023 - 3:17:52 PM - Ray Barley Back To Top (91760)
Judging by the date of the oldest comment for this tip, it's referring to SQL Server 2005 or 2008. The oldest SQL Server instance I have is SQL Server 2022 so I can't help out.

Starting with SQL Server 2012, Microsoft changed the way you do configuration, switching to a "package" deployment model that works much better.

Wednesday, November 15, 2023 - 8:57:33 AM - Andy Back To Top (91755)
This is an old one, but I'm hoping someone has some input on this.

I have everything setup and it looks like it is operating correctly. However, when I run the package, I am seeing a warning that "Configuration registry key "SSIS_CONFIG_DB" was not found. A configuration entry specifies a registry key that is not available." in the progress window.

I've checked the registry and the key is there. I've tried running Visual Studio in admin mode and it gives the same result.

Any Idea how to fix this? I'm not finding anything in my searches.

Thanks!

Tuesday, November 28, 2017 - 6:52:09 AM - Gregor Kvas Back To Top (73328)

Yes, you are right Ray, I adress a sql-tip of yours based on SQL Server 2008 (since we are still using this system in our company):

The typical sensitive information is a connection string, especially when it includes a user name and password. My personal preference for this situation is to store that in a SQL Server package configuration, then secure the SQL Server database that's used to hold the configuration table. Take a look at our earlier tip SQL Server Integration Services SSIS Package Configuration for the details on SQL Server package configuration. (https://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/)

To store (sensitive) connection strings in a SQL Server package configuration and securing the belonging SQL Server database just didn't solve for me the security issue that comes with the still shown connection strings in the corresponding connection managers in the SSIS Package itself. Understanding your sql-tip in https://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/ this should be handeld via the SSIS Package Protection Level.

 

Thank you for your tip and link concerning the better configuration possibilities in SQL Server 2012!

Best regards, Gregory

Monday, November 27, 2017 - 2:56:19 PM - Ray Barley Back To Top (73297)

You should limit the access to the SQL Server database that holds the configuration information and sensitive information should be encrypted.  I believe this tip was based on SQL Server 2008 and I do not think there was any built-in option to encrypt the data in SQL Server configuration.

Beginning with SQL Server 2012, you can store the SSIS configuration data in the built-in SSISDB database and there's a checkbox for sensitive data.  When checked the data is encrypted.  You create "environments" to store configuration data.  Take a look at his tip to get the details: https://www.mssqltips.com/sqlservertip/2451/ssis-project-deployment-model-in-sql-server-2012-part-2-of-2/

 

 

Monday, November 27, 2017 - 1:18:17 PM - Gregor Kvas Back To Top (73294)

Hi Ray!

Thx for your easy to follow step-by-step tip to setup SSIS package configurations!

But i fear, that i am missing here something: When using SQL Server package configuration to store a connection string in a package configuration database (like in your example above to the Database AdventureWorks), is there any security issue when not securing the SQL Server database, that holds the configuration table (as you proposed in https://www.mssqltips.com/sqlservertip/2091/securing-your-ssis-packages-using-package-protection-level/), because the connection string that is exported to the configuration table is shown anyway in the properties of the belonging connection manager!?

Thx a lot for your help!
Gregory

Wednesday, March 16, 2016 - 2:03:00 PM - Greg Robidoux Back To Top (40959)

Rajeev,

here are the correct links from Ray's post

https://www.mssqltips.com/sqlservertip/2450/ssis-project-deployment-model-in-sql-server-2012-part-1-of-2/

https://www.mssqltips.com/sqlservertip/2451/ssis-project-deployment-model-in-sql-server-2012-part-2-of-2/

 

Wednesday, March 16, 2016 - 1:56:09 PM - Ray Barley Back To Top (40958)

Rajeev

You have to change every package that you want to use the package configuration option that you have chosen.  After the change, you can change just the configuration and not the individual packages.

 

This tip is an old one; if you are using SQL Server 2012 or later, a better approach is to use the Project Deployment model; it has a much better configuration.

 

Take a look at these tips:

https://www.mssqltips.com/.../ssis-project-deployment-model-in-sql-server- 2012-part-1-of-2/
https://www.mssqltips.com/.../ssis-project-deployment-model-in-sql-server- 2012-part-2-of-2/

 

 

Saturday, March 12, 2016 - 7:49:06 AM - Rajeev Back To Top (40921)

I am new to SSIS,i did what is written above but i dont know how to move next.Means i have many package and my need is to change a connection at one place and it will automatically effect in each package without change it manually each time.how i can change the database name or server name at one place and it will automatically done for each packages.

Reply soon in mail [email protected]

Thanks!!

Friday, November 7, 2014 - 9:29:43 AM - Raymond Barley Back To Top (35220)

2 thoughts - is the [User::IRDB] variable scope set to the package level?  If you put a breakpoint on the task that uses the connection manager, can you see that the [User::IRDB] variable has the value that you expect it to have?

What error message are you getting?

Friday, November 7, 2014 - 4:33:08 AM - M Muthuraja Back To Top (35218)

Hi ,

 

How will i asign the databasename dynamically.. i have  assigned usin expression in coneection manager properties...

"Data Source=Localhost;Initial Catalog="+@[User::IRDB]+";Integrated Security=SSPI"

 

for this user variable i have assign the value from sql task ..byt its not working for me...any idea would be appreciated..

 

Thanks in advance

 

 

 

Wednesday, October 15, 2014 - 5:03:51 PM - Raymond Barley Back To Top (34975)

 If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.

Wednesday, October 15, 2014 - 5:03:25 PM - Raymond Barley Back To Top (34974)

Responding to Fran below - the SQL Server Package Configuration option allows you to retrieve values from a single configuration table in a single SQL Server database.  Refer to step 12 above in the tip when you specify the connection manager and table name.

If necessary you could use a view that has the 4 columns in the SSIS Configurations table and that view could retrieve data from multiple tables and / or multiple databases, use a linked server or openquery, etc.

Wednesday, October 15, 2014 - 4:17:24 PM - Fran Back To Top (34973)

Thanks, I was able to create one connection this way. How do I create multiple connections using this process?

Thursday, July 10, 2014 - 5:42:32 AM - Niraj Back To Top (32624)

 Thanq Ray Barley i got this solution, i was missing to configure varaible in exact postion of registry key 

Wednesday, July 9, 2014 - 2:48:43 PM - Ray Barley Back To Top (32614)

I don't think there is a tip on using a registry key for SSIS package configuration.

There's a paragraph on it on this MSDN page: http://msdn.microsoft.com/en-us/library/ms141682%28v=sql.110%29.aspx

Wednesday, July 9, 2014 - 8:09:17 AM - Niraj Back To Top (32601)

Hi,

 Can u please try to explain about the package configuration using registry entry for the local machine, if you have already explain about this please share the link for article   

Thursday, June 5, 2014 - 7:47:45 AM - Ray Barley Back To Top (32092)

The [SSIS Configurations] table is created for you when you configure the SQL Server package configuration type; see step 12 in the article.  There is a New button that you can click to create the table (it's cut off on the screen shot in step 12)

Wednesday, June 4, 2014 - 5:54:26 PM - Kristen Back To Top (32085)

Did you make this table [SSIS Configurations]?  What do you recommend for the structure of the table and what columns shoudl be included?

Tuesday, May 27, 2014 - 10:56:14 AM - Ray Barley Back To Top (31957)

One way of solving your problem is to use package variables for things like connection strings and table names.   When you run the package you can use DTEXEC and specify variable values on the command line; see this tip for the details: http://www.mssqltips.com/sqlservertip/1812/dtexec-command-line-parameters-using-command-files/

Another way is to use the SQL Server package configuration, create multiple configurations, and select the configuration at runtime; see this tip for the details: http://www.mssqltips.com/sqlservertip/2745/how-to-support-multiple-sql-server-package-configurations-in-ssis/

Yet another approach (and this is my favorite) is to create multiple SQL Server configuration databases, each contains a particular set of configuration vales, then you use sql server aliases to pick the right configuration at runtime; see this tip for the details: http://www.mssqltips.com/sqlservertip/2459/using-a-sql-server-alias-for-ssis-package-configuration-database-connection-string/

 

Monday, May 26, 2014 - 10:39:07 AM - Victor Gomez Back To Top (31943)

Hello, I from Dominican Republic, very intersting this post, But I have a very intersting problem, I have one Stagin Area(Sql Server 2008) and 5  Data Source(Oracle) by country, and we have 9 Country, is the same table but in diferente DataBase, so I need one process that can change this data source with a table in the staging with this conection, do you have some idea you can share with me?

Thanks,

 

Victor Gomez

Tuesday, March 11, 2014 - 6:09:43 AM - Nidheesh Pillai Back To Top (29704)

Thankyou Mr. Barley for your response. The link you provided is useful.

Friday, March 7, 2014 - 9:27:56 AM - Ray Barley Back To Top (29678)

When I first started using SSIS I came up with the idea of using an environment variable for the connection string.  Later I came up with a better approach; take a look at this tip: http://www.mssqltips.com/sqlservertip/2459/using-a-sql-server-alias-for-ssis-package-configuration-database-connection-string/

 

Friday, March 7, 2014 - 4:15:21 AM - Nidheesh Pillai Back To Top (29672)

Thankyou Mr. Barley for your answer and also for the SQL 2012 tip.Though I am still on 2008R2, this tip will greatly help when we move to 2012 in the future.

I have another question which is with regards to the role of environment variable configuration for a SQL server configuration. Taking your example, you mention that we create a new OLEDB connection in the connection manager section and configure it using an environment variable and only then do we proceed to using SQL server configuration type.

So does that mean that for configuring any package using configuration type = SQL server, we would require the help of both configuration type = Environment variable and configuration type = SQL server. IS there any way we can successfully configure SSIS package with configuration type = SQL server ONLY, without bringing Environment variables into the picture?

Wednesday, March 5, 2014 - 10:33:22 AM - Raymond Barley Back To Top (29648)

As a general rule I want to store configuration data for SSIS packages in a separate database.  While not required, the benefit is that you have a single database that has all configuration data in one place where it can easily be shared by many SSIS packages.  

Starting with SQL Server 2012, you have the option to use "project" deployment where you an store all of the SSIS package configuration data for an entire project in the SSISDB database.  This allows every package in a particular project to share the configuration data.

Tuesday, March 4, 2014 - 2:27:05 AM - Nidheesh Pillai Back To Top (29633)

Mr. Barley,

Nice article on the tips to how we configure SSIS package using configuration type = SQL Server. I developed a curiosity while going through the article, hence I have chalked out this question for your expert advice.

As per the article, you mention the usage of a database called SSISConfig, which will house a table called dbo.[SSIS Configurations]. Inside the package, there will be one OLEDB connection for this new database, which will be later configured using an environmental variable called SSIS_CONFIG_DB, and then the dbo.[SSIS Configurations] table will be used to store configuration values of the rest of the connections like AdventureWorks OLEDB connection and the like.

My take-away from the link is that whenever one would want to do SSIS package configurations with Configuration Type = SQL Server, as a pre-requisite, we would require (if we dont have one) a new database to house the table containing the configurations and an environmental variable to configure to this new database inside the SSIS package itself before we proceed further.

My question is, whether this pre-requisite is always a necessity? Can we not create the dbo.[SSIS Configurations] table inside AdventureWorks itself and configure the items we would want to inside it? What would be the implications if I did so?

Thursday, January 23, 2014 - 9:10:57 AM - Raymond Barley Back To Top (28192)

Multiple packages can and should share a single SSIS config table.  The SSIS config table doesn't need to know what package(s) uses it.

When you open or run a package this query is run to retrieve the configuration; note it's filtered by the ConfigurationFilter column which is on the Select Configuration Type page of the Package Configuration Wizard:

exec sp_executesql N'Select ConfiguredValue, PackagePath, ConfiguredValueType from [dbo].[SSIS Configurations] Where ConfigurationFilter = @P1',N'@P1 nchar(2)',N'GP'

When you specify the ConfigurationFilter, you can then configure multiple package properties.  If you want to configure a group of properties and you want different values based on the package that is running then you configure the group of properties in more than one ConfigurationFilter.  Each package specifies the ConfigurationFilter to use.

Wednesday, January 22, 2014 - 11:04:42 PM - Shovan Back To Top (28182)

In sql server Configuration approach discussed above No where in the SSISConfig table the name of the package is stored to uniquely identify it.So shall I have to create 10 diff SSISConfig tables for deploying 10 diff diff Master packages??

Thanks & Regards, Shovan

Friday, September 6, 2013 - 4:14:08 PM - Ray Barley Back To Top (26653)

You have to post the error that you are getting in order for someone to offer advice on how to fix it.

If you are trying to read an Excel file that's in a SharePoint document library, take a look at this post which may be your problem:

http://rusco.wordpress.com/2012/08/24/allow-authentication-forwarding-when-using-webdav-with-fqdn/

Friday, September 6, 2013 - 3:00:10 PM - Nes Back To Top (26652)

For example I have an excel source that is located from the different server where my package is located. The path where the excel files are located is in \\SharePointSever\sites\test\ExcelFiles\test.xls when I use this path in my excel connection manager it giving me an error. I am newbie in SSIS, a help will much be appreciated.

Thanks, Nes

Wednesday, July 24, 2013 - 9:42:23 AM - Raymond Barley Back To Top (25979)

You can verify this with SQL Server Profiler but I think when you change configuration it may delete then insert.

Wednesday, July 24, 2013 - 7:30:13 AM - venkat Back To Top (25975)

i am not deleting any data on config table.just i am changing connectionstring from dev to int for configuration purpose.

changs like dev server is aaaa i am changing it to int server name like bbbb ,saving ssis pkg and giving to production deployteam.

but databse names and configration table structure are same in all environments.

Tuesday, July 23, 2013 - 9:38:49 AM - Raymond Barley Back To Top (25963)

The error is saying that the user running the wizard does not have delete permission ion the SSIS_Configurations table.

 

Take a look at this tip on how to grant the user delete permission: http://www.mssqltips.com/sqlservertip/1138/giving-and-removing-permissions-in-sql-server/

Tuesday, July 23, 2013 - 8:18:36 AM - venkat Back To Top (25959)

i created one package in dev environment with sqlserver configuration.it is working fine in dev and inserting config values into dbo.ssis_configration table

for same package i changed connection strings from dev to INT environment i am getting below error,please help me.

TITLE: Microsoft Visual Studio
------------------------------

Could not complete wizard actions.

------------------------------
ADDITIONAL INFORMATION:

Cannot delete existing configuration information from the configuration table. (Microsoft.DataTransformationServices.Wizards)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=GenerateSqlConfigurationErrors_couldNotDeleteExistingConfiguration&LinkId=20476

------------------------------

The DELETE permission was denied on the object 'SSIS_Configurations', database 'xxxxx', schema 'dbo'. (Microsoft SQL Server Native Client 10.0)

------------------------------
BUTTONS:

OK
------------------------------

Saturday, July 20, 2013 - 8:56:24 AM - Ray Barley Back To Top (25926)

I don't know of an easy way to do that.  However, I did another tip where I added a column to the SSIS Configuration table and created multiple views on top of that table.  Each view represents the set of configurations for an application.  Here's the link to the tip:

http://www.mssqltips.com/sqlservertip/2745/how-to-support-multiple-sql-server-package-configurations-in-ssis/

If it's possible to move to SQL Server 2012 Integration Services, you can take advantage of a new configuration model that will do exactly what you want.  You can create "environments" that have their own set of configuration value then specify the environment at runtime.

Friday, July 19, 2013 - 11:00:25 AM - Gary Back To Top (25914)

Just wonder, is it possible to have the “Configuration filter:” configurable during deployment?

This will allow same package used in multiple jobs.

Monday, June 24, 2013 - 8:21:25 AM - Raymond Barley Back To Top (25537)

I'm assuming you have the connection strings in a table and you want to keep it that way and that using the connection strings dynamically means you want to implement some logic when the package runs that determines which connection string to use.  

Change your connection manager's ConnectionString property to get the value from a package variable.  You can set the package variable's value to the appropriate connection string from your table by using an Execute SQL task that runs a query and returns a single row with the connection string as a column.  You have to get the column's value and assign it to your package variable; you do this on the Parameter Mapping property page  The Execute SQL task has a ResultSet property on the General page that you would set to SingleRow; i.e. you want a query that returns 1 row and has a column that contains the connection string. 

 

 

Saturday, June 22, 2013 - 11:22:23 AM - Ashish Kumar Back To Top (25527)

 

Hello Ray,


I have a connection strings in various remote DB in my master DB Table.

Can you please suggest me how do I use these connnection strings dynamiclly to our packages.


Shall I use local variables for the same.

Thursday, June 13, 2013 - 4:18:54 PM - Raymond Barley Back To Top (25425)

Take a look at this tip: http://www.mssqltips.com/sqlservertip/1442/handle-slowly-changing-dimensions-in-sql-server-integration-services/

There is a built-in SCD component in SSIS that may work for you.

Thursday, June 13, 2013 - 2:40:09 PM - Mizanur Rahman Back To Top (25423)

Hi Ray:

Here is my case:

I want to implement SCD type II in my DW.

In the following source data, Product name(for Same productId =1) is changing dynamically in the same source file.

My question is, in SSIS how we can handle for these kinds of data in case of SCD Type II data capturing. 

Source Table's Data:

(This case can hapen in every load):

ID                            Name                                    Prd_No           Modified_Date

1                              Adjustable Race                      AR-5381                  2005/01/01

1                              Adjustable Race_Change1      AR-5381                  2005/01/02

1                              Adjustable Race_Change2       AR-5381                  2005/01/03

2                              Bearing Ball                              BA-8327                  2005/01/01

3                              BB Ball Bearing                        BE-2349                  2005/01/01

4                              Headset Ball Bearings              E-2908                   2005/01/01

316                         Blade                                         BL-2036                  2005/01/01

317                          LL Crankarm                           CA-5965                  2005/01/01

318                         ML Crankarm                            CA-6738                  2005/01/01

 

 

Target Table:

SG_PK   ID           Name                                      Prd_No                      Current_Status

1              1              Adjustable Race                       AR-5381                               0 

2              1              Adjustable Race_Change1       AR-5381                               0  

3              1              Adjustable Race_Change2       AR-5381                              1 

4              2               Bearing Ball                            BA-8327                               1 

5              3               BB Ball Bearing                       BE-2349                                1 

6              4               Headset Ball Bearings             E-2908                                 1 

7              316          Blade                                         BL-2036                                1 

8              317          LL Crankarm                            CA-5965                               1 

9              318         ML Crankarm                            CA-6738                               1 

 

 

 

 

 

Could you please give me a tips to design this ETL mapping?

 

 

 

Regards.

Monday, June 10, 2013 - 10:14:02 AM - Raymond Barley Back To Top (25375)

Here are the steps to get excel file location from a column in a table and use it to load from an excel file:

create a package variable;e.g. EXCEL_FILE_PATH scope=Package data type=string

add an execute sql task to the package; set ResultSet = SingleRow, SQLStatement = select that returns the excel file location from your table, parameter mapping = assign column selected to your package variable (the exact details depend on which type of connection you are using)

excel connection manager - use Expressions property to set ExcelFilePath to your project variable; e.g. EXCEL_FILE_PATH

Monday, June 10, 2013 - 3:56:48 AM - Basava Back To Top (25362)

Hello Ray,

I want to process value "excel file location" (column) from Table and SSIS should refer column value path and load that excel data into destination table. Purpose of this task is In future we just update the file location in the table no need to change the source details in ssis.

 

Thanks in Advance.

 

Regards,

Basava

Wednesday, May 1, 2013 - 3:41:43 AM - Ash Shah Back To Top (23648)

Thanks for the response Ray but with an ex-colleague's help we figured it out.

My assistant had put the env variable package configuration (which only contains the connection string) below the SQL Server configuration (which contains all other package configurations). Therefore it was trying to read the SQL Server configurations first and would therefore use the connection string in the package (built on my assistant's machine) rather than the correct connection string (which had not yet been read from the env variable).

Simply swapping these two items around fixed the problem. Annoyingly simple!

Thanks again,

Ash

Monday, April 29, 2013 - 11:30:15 AM - Raymond Barley Back To Top (23618)

Check the connection string and make sure it doesn't have the server set to the machine name of your assistant.

Run the package using DTEXEC and pay close attention to the output that shows the configuration stuff that's going on.

e.g. DTEXEC /rep v /file package.dtsx

 

If a configuration fails, it's generally not an error and the package may be running with the value in the connection string; i.e. the configuration that you expect is happening isn't and the connection string isn't being overwritten based on your configuration.

Monday, April 29, 2013 - 10:04:21 AM - Ash Shah Back To Top (23613)

Hello Raymond,

We implemented a solution exactly like this 3 years ago and it has worked fine until this week. Now, when my assistant makes package changes on her local machine (using an environment variable pointing to a local instance of SQL Server), when we deploy that package to the production server, the package tries to connect to my assistant's local SQL Server, rather than the SQL Server on the production server. I can confirm that the environment variable on the production server has the correct connection string.

We run our packages through SQL Agent, so there should be no issue of rebooting or restarting anything (in fact we have made so many deployments over the years it is second nature to us).

This problem is defeating me at present, so I would be grateful for any thoughts you might have. If you need further information please let me know.

Thanks for taking the time,

Ash Shah

Tuesday, February 19, 2013 - 8:57:14 AM - Raymond Barley Back To Top (22270)

If at all possible use SQL Server 2012; the configuration is completely redone and it works the way it should.

What you need to do is create an SSIS package template where you put in place all of the configuration then copy that package each time you create a new one.

Tuesday, February 19, 2013 - 6:29:11 AM - AlfieAmalfi Back To Top (22258)

This looks great in principle, but I'm having some problems applying it in practice.  We are just starting a project that will require the building of several SSIS packages that will need to share configuration data, so storing it in a database seems ideal.  However, as configuring each package to use SQL Server configuration requires going through the wizard that then exports the relevant variables to the config table, how can I do this for each package without having to enter a value for each variable for each package or without the export overwriting what's already in the database if I don't enter a value for each variable?  Am I missing something?

Friday, February 1, 2013 - 8:22:24 AM - Raymond Barley Back To Top (21863)

What is the error message you're getting?

Friday, February 1, 2013 - 6:32:57 AM - adwait Back To Top (21860)

Hi

I am having one Web Service Task for sending the Emails from SSIS Package. The Package works fine when running on the local machine but gives an error when deployed.

Please suggest what should I do?

 

Thanks in advance.

 

Regards,

Adwait

Tuesday, December 18, 2012 - 11:35:22 AM - Maggie Back To Top (21015)

I added the package config to a project with about 30 different packages. All packages use the same configuration file.  there are 5 connection managers in the config file. All packages connect to all managers.  Several times now I have closed the project and reopened it only to have one (or several) of the packages no longer finding an established connection manager.  When I edit the config file within that package you can see that the connection manager causing the problem is no longer "checked". However it remains in the config file when you look at it through notepad or IE.  It is not always the same package and not always the same connection manager.  I have checked every "delay validation" property I can find in the whole project.  I have also removed and recreated the config file from scratch.  Any help would be greatly appreciated. I am running SQL 2005 using Bids in a 2003 OS. 

Friday, October 12, 2012 - 2:04:41 PM - RD Francis Back To Top (19898)

When we first set up SSIS a few years ago, we initially used an environment variable.  While we were testing (we had several hundred DTS packages we were migrating), the environment variable on one of our servers "went away".  I suspect that it was set up wrong; tied to a user account instead of to the account running SQL Server, if not simply completely neglected.  However, the fact that we had to stop SQL to resolve the situation bothered me.

We wound up switching to using an XML file to hold the SSIS DB connection string.  I identified a drive letter that all our servers had, and created a folder on each of those drives with our config file in it.

Then, I mapped a folder on a network drive to that same drive letter on my machine.  I keep a copy of each server's config file (each one identifying the server and DB to use for SSIS configurations).  This lets me run all our packages from my machine with the config for the appropriate environment.  Since no environment variable is involved, I can simply copy the file for (for instance) our QAS server, and rename it to the common config file name, SSIS_Server.dtsConfig.  When I run the package I'm testing, it picks up its config from that QAS server.

A matter of personal preference, I suppose.

I also have a script that lets me grab the configurations for a package from the QAS SSISConfig database, and build INSERT/UPDATE statements to add that data to the same DB on another server.  I then review the values, and modify those that need to be different in the new environment.

It works well for me, at least; thought I'd share.

Tuesday, October 9, 2012 - 12:21:10 PM - Ray Barley Back To Top (19834)

SSRS has a shared data source which will do exactly what you want.  You can search for Shared Data Source on the MSSQLTIPS.com home page to get the details; here's the first link: http://www.mssqltips.com/sqlservertutorial/233/create-a-shared-data-source/

 

Tuesday, October 9, 2012 - 11:12:36 AM - Ramy Gerges Back To Top (19832)

I understand now, however I was wondering if you could help me with a problem I have. I am moving a bunch of SSRS reports and SSIS packages from development to production. The way I do so is using copy and paste, however I then have to update the connection strings and other configurations manually. Do you know of a way that can automatically update at least the connection string for the reports instead of having to do them manually? 

Thanks,

Ramy

Friday, September 28, 2012 - 5:42:57 PM - Ray Barley Back To Top (19742)

The purpose of the tip was to show how to create a package that gets some of its propertiy values set via SQL Server package configuration.  The tip shows how to get the connection string for the package configuration database from an environment variable and how to get the connection string for a database from the package configuration.

Following along with the tip when you move the package to another machine and want to change the configuration database you change the environment variable.  There is nothing built-in to update the package configuration values - use SQL Server management Studio is the easiest way.

Friday, September 28, 2012 - 5:04:14 PM - Ramy Gerges Back To Top (19741)

Hi Ray,

My problem is not running the package you helped us create here. My problem is actually making use of it. From my understanding, the purpose of the package created here is to update the configuration values of a certain package which is moved from one server to another. When do its values get updated? 

For example: I have a package 'LoadData.dtsx' on the development server. It is used to pointing to a table 'DataTable' in a Database 'DevelopmentServerDB'. Now I move 'LoadData.dtsx' to the test server and would like to update its configuration values and which database it points to. Using the package you helped us create here, how do I achieve this? 

Thanks again,

Ramy Gerges

Friday, September 28, 2012 - 4:46:31 PM - Ray Barley Back To Top (19740)

Take a look at this tip for the options you have for executing an SSIS package: http://www.mssqltips.com/sqlservertip/1775/different-ways-to-execute-a-sql-server-ssis-package/

I think DTEXEC is the easiest.

There are 3 ways to deploy SSIS packages; you can read about that here: http://www.mssqltips.com/sqlservertutorial/213/deploying-ssis-packages/

I think deploying to the file system is the esaiest; you simply copy the SSIS package to a particular folder.

To update the configuration values I think the easiest way is to edit the configuration table in SQL Server Management Studio.  You can also write TSQL scripts to insert, update or delete the rows as necessary.

Friday, September 28, 2012 - 4:11:08 PM - Ramy Gerges Back To Top (19738)

Hi, 

 

I have a little bit of an amateaur question. So I created the package (per your above instructions) in my test server, however I am not sure how to use it at this point. I have a package in my development server which I would like to move into my test server, while also updateing its Configuration values (by using the method you specified in this article). My problem is I do not know how to use the package you helped us create here, in order to update the configuration values in the package I am moving from development to the test server. 

Thank you,

 

Ramy Gerges

Wednesday, June 20, 2012 - 12:58:07 PM - Ray Barley Back To Top (18133)

You do not need an environment variable; I used it to specify the connection string for the package configuration database; it's convenient but as a general rule I no longer use them; there are better ways.  My preferred way is to use a SQL Server Alias to specify the database server then just hardcode the database name of the config database in the package.  You can take a look at this tip for the details: http://www.mssqltips.com/sqlservertip/2459/using-a-sql-server-alias-for-ssis-package-configuration-database-connection-string/

You can run a package simultaneously by creating separate SQL Agent jobs; i.e. one job for each source database.  This also allows you to tweak your package variables as necessary for each source database; select the job step type SQL Server Integration Services Package

Wednesday, June 20, 2012 - 10:36:43 AM - Bhanu Singh Back To Top (18128)

Hi,

I have 3 databases for 3 countries. I want to run the same package on 3 datbases simultaneously to create seperate extract for each country.

I want to create SSIS config based on SQL Server database ( Not XML). When creating SSIS config, do I need to create an environment variable or can I use a variable for connection String.  Some variabls are common like Start Date and End Date  for extracting data from the source system. the Database and output CSV file wll be different for each country.

Will I be able to run extract for 3 country simultaneously ?

Thanks in Advance.

 

Regards,

Bhanu

 

 

Thursday, May 24, 2012 - 10:01:43 AM - Ray Barley Back To Top (17639)

Here are 2 TechNet links that provide the details;

Built-in sources: http://technet.microsoft.com/en-us/library/ms141093%28v=sql.105%29

Built-in destinations: http://technet.microsoft.com/en-us/library/ms141089%28v=sql.105%29

 

Thursday, May 24, 2012 - 3:08:30 AM - Rahul Back To Top (17630)

Hi,

I want to know the different types of data provider which are supported by SSIS package.

Regards,

Rahul

 

Monday, November 14, 2011 - 4:18:49 PM - Jay Back To Top (15110)

Hi:

this article is good but lacking on 64 bit Alias. the pkg is running under 32bit alias & not 64bit.

Do we have any article for 64bit.

Thanks

Jay

 

Monday, September 12, 2011 - 7:51:57 AM - pramod Back To Top (14633)

So the connection information that you specified in the article:

Data Source=localhost;Initial Catalog=SSISConfig;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;

Does this use windows global authentication to login to the database?

 

If I have to set this up on the production server, will I be able to use windows authentication instead of SQL Server authentication?

Monday, September 12, 2011 - 7:39:22 AM - Ray Barley Back To Top (14632)

You can but from a security standpoint it's not a good idea. 


Monday, September 12, 2011 - 7:17:50 AM - pramod Back To Top (14631)

Can we use SQL Server username/password, while setting up the environmental variable containing the database information to the config table?

Wednesday, August 10, 2011 - 5:11:29 PM - Ray Barley Back To Top (14365)

The easiest way to test whether your package configuration is working is to change the ConfiguredValue in the SSIS Configuration table for one of your configurations then open up an SSIS package in BIDS and make sure that the object that you configured has the value in the SSIS Configuration table.  For instance if you setup a package configuration for an OLE DB Connection Manager's connection string, you can check the value in your package by clicking on the connection manager then checking the value in the properties window.

All that package configuration does is retrieve a value and assign it to something in your package.

Close your package, change the ConfiguredValue in the SSIS Configuration table, then open your package again to see that the new value has been assigned.

Wednesday, August 10, 2011 - 4:37:40 PM - Tasha N Phan Back To Top (14363)



Hello all,

I'm new to this, sorry in advance for this basic question. I made the configuration package as instructed in the article but now I am wondering how to test it. Can anyone help me out?

Thursday, April 14, 2011 - 6:03:29 AM - Ray Barley Back To Top (13610)

yes - you need to set the environment variable on any machine where the package is run.  When the package runs the connection string is retrieved from the environment variable so it has to be set. 

Thursday, April 14, 2011 - 2:42:32 AM - wong Back To Top (13609)

yes, in my local. after deploy it to development server, i run the package(in development server, by right click the package and run). do i need to set it in development server too?

Wednesday, April 13, 2011 - 7:23:41 AM - Ray Barley Back To Top (13606)

Are you sure you created the environment variable?  If you go to control panel, system, advanced settings do you see the environment variable in the system variables list?  Did you close BIDS and reopen it after creating the environment variable?  Once BIDS (or any program for that matter) is open, it doesn't "see" environment variables added to the system after it's been opened; you have to close and reopen. 

Wednesday, April 13, 2011 - 3:33:15 AM - wong Back To Top (13604)

Ok, i had tested, the text file is generated but i got error, said that the environmental variable is not found "ssis_config_db"

and can't open database ssisconfig because login failed for development/sa.

 

 

Wednesday, April 13, 2011 - 3:06:52 AM - wong Back To Top (13602)

Okay i made mistake, the default table will be made in SSISConfi.

How to appy this after i deploy? can provide another link to refer?

Wednesday, April 13, 2011 - 2:27:21 AM - wong Back To Top (13601)

what's the column name , and value in SSISConfig database. Do i really need to create that?

if yes, do i need to export this to development server.


My scenario is like this, i had a databse named : MIT, with a table name AOM. i want to export this table's data to text file.

So i add the  Connection Managers for the MIT databases, and file manager.then i add the configuraton , env & sql


i do this in my local machine and need to deploy it to development server to test. i do not know the id and password of my development server. By doing according to your article, i'm able to run after that. i don't know why i need that SSISConfig. what is this for? Newbie here.

Monday, April 12, 2010 - 9:58:26 AM - raybarley Back To Top (5224)

Your package must have a configuration entry that is looking for the registry entry as noted in the warning.  You should open the package configuration and delete it.

 

Monday, April 12, 2010 - 9:28:59 AM - bndgyawali Back To Top (5223)

Thanks for the reply. But still my problem is not resolved.

But after removing Integrated Security, still I have the problem that, there is no variable in registry. What should I do to add the variable in registry. I have directly added an environment variable with the value set. Is this ok or I should do something else.This gives warning as below, but SSIS execution completes successfully. 

 

My error is as:

Provider=SQLNCLI10.1;Data Source=SERVERNAME;Persist Security Info=True;Password=******;User ID=USERNAME;Initial Catalog=DATABASENAME;
dtexec /file %SSIS_DIR% /CONN "MYConnection;%SSIS_CONFIG_DB%"

 

Microsoft (R) SQL Server Execute Package Utility
Version 10.0.2531.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  3:59:16 PM
Warning: 2010-04-12 15:59:16.78
   Code: 0x80012015
   Source: ParallelExecution
   Description: Configuration registry key "Provider=SQLNCLI10.1;Data Source=SERVERNAME;Persist Security Info=True;Password=******;User ID=username;Initial Catalog=DATABASENAME;" was not found. A configuration entry specifies a registry key that is not available. Check the registry to ensure that the key is there.
End Warning
Warning: 2010-04-12 15:59:16.78
   Code: 0x80012059
   Source: ParallelExecution
   Description: Failed to load at least one of the configuration entries for the package. Check configuration entries for "CONN" and previous warnings to see descriptions of which configuration failed.
End Warning
Warning: 2010-04-12 15:59:16.78
   Code: 0x80012015
   Source: ParallelExecution
   Description: Configuration registry key "Provider=SQLNCLI10.1;Data Source=SERVERNAME;Persist Security Info=True;Password=******;User ID=username;Initial Catalog=DATABASENAME;" was not found. A configuration entry specifies a registry key that is not available. Check the registry to ensure that the key is there.
End Warning
Warning: 2010-04-12 15:59:16.78
   Code: 0x80012059
   Source: ParallelExecution
   Description: Failed to load at least one of the configuration entries for the package. Check configuration entries for "CONN" and previous warnings to see descriptions of which configuration failed.
End Warning

 

 Please help me.

 

Monday, April 12, 2010 - 6:53:40 AM - raybarley Back To Top (5222)

Near the bottom of your output you have this error:

An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult:
0x80040E4D  Description: "Login failed for user 'D2HS\bgyawali'.".
End Error

You have Integrated Security=SSPI; in your connection string which means use Windows authentication.  The user id and password are being ignored and you are connecting using Windows authentication which is failing.  You should either have Integrated Security=SSPI; or user id and password - not both.

Also as a general rule there isn't a good justification from a security standpoint for connecting as sa.  


Monday, April 12, 2010 - 2:15:21 AM - bndgyawali Back To Top (5220)

I tried as you have said. But, still it givs error in my project. 

 Please see my configuration here:

I set environment variable as  ssis_config_db: Data Source=servername;Initial Catalog=databasename;Provider=SQLNCLI.1;Integrated Security=SSPI;user id=sa;password=*******Auto Translate=False;

 I have changed my connection name as MyConnection.

I created configuration name is CONN


Now, I tried in cmd as:  dtexec /file "ParallelExecution.dtsx" /CONN "MYConnection;%SSIS_CONFIG_DB%"

 

This gives me error as:

 

dtexec /file "ParallelExecution.dtsx" /CONN "MYConnection;%SSIS_CONFIG_DB%"
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.2531.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  11:49:53 AM
Warning: 2010-04-12 11:49:54.03
   Code: 0x80012015
   Source: ParallelExecution
   Description: Configuration registry key "SSIS_CONFIG_DB" was not found. A con
figuration entry specifies a registry key that is not available. Check the registry to ensure that the key is there.
End Warning
Warning: 2010-04-12 11:49:54.03
   Code: 0x80012059
   Source: ParallelExecution
   Description: Failed to load at least one of the configuration entries for the
 package. Check configuration entries for "CONN" and previous warnings to see de
scriptions of which configuration failed.
End Warning
Warning: 2010-04-12 11:49:54.03
   Code: 0x80012015
   Source: ParallelExecution
   Description: Configuration registry key "SSIS_CONFIG_DB" was not found. A con
figuration entry specifies a registry key that is not available. Check the regis
try to ensure that the key is there.
End Warning
Warning: 2010-04-12 11:49:54.05
   Code: 0x80012059
   Source: ParallelExecution
   Description: Failed to load at least one of the configuration entries for the
 package. Check configuration entries for "CONN" and previous warnings to see de
scriptions of which configuration failed.
End Warning
Error: 2010-04-12 11:49:54.22
   Code: 0xC0202009
   Source: ParallelExecution Connection manager "MYConnection"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred.
 Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult:
0x80040E4D  Description: "Login failed for user 'user'.".
End Error
Error: 2010-04-12 11:49:54.22
   Code: 0xC00291EC
   Source: SP_COMMON Execute SQL Task
   Description: Failed to acquire connection "MYConnection". Connection may not
be configured correctly or you may not have the right permissions on this connec
tion.
End Error
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  11:49:53 AM
Finished: 11:49:54 AM
Elapsed:  0.359 seconds

Friday, April 9, 2010 - 11:52:46 AM - raybarley Back To Top (5216)

DB _Config in your SSIS command line must match the name of the connection manager in the SSIS package.  I think that's what the error is saying.

Your connection string should not have Integrated Security=SSPI if you are specifying a user account and password; SSPI means you're using Windows authentication. 

 

 

Friday, April 9, 2010 - 9:36:49 AM - bndgyawali Back To Top (5215)

I learned to configure SSIS.But what I want is that, I want to run it through cmd using dtexec

I tried by doing as

dtexec /file "ParallelExecution.dtsx" /CONN "DB_Config;%SSIS_CONFIG_DB%"
Microsoft (R) SQL Server Execute Package Utility
Version 10.0.2531.0 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  7:20:20 PM
Error: 2010-04-09 19:20:21.05
   Code: 0xC001000E
   Source: ParallelExecution
   Description: The connection "DB_Config" is not found. This error is thrown by
 Connections collection when the specific connection element is not found.
End Error
DTExec: Could not find the DB_Config connection in the package.
Started:  7:20:20 PM
Finished: 7:20:21 PM
Elapsed:  0.125 seconds

I donot know what does DB_Config, but I did this by finding in some blogs. As you have said,created an environment variable as 

Data Source=cooper;Initial Catalog=abc;Provider=SQLNCLI.1;Integrated Security=SSPI;user id=sa;password=****;Auto Translate=False;

 And this gives an error.

Please let me know what is this error, and any errors that are occuring in mine.

 

 

Thanks in advance.


Friday, November 6, 2009 - 2:02:44 PM - raybarley Back To Top (4383)

Did you install Integration Services on the machine where you're trying to run the package?  When you create a package in Visual Studio you don't need to have Integration services installed.  However when you run a package outside of Visual Studiop you do.

Take a look at this link: http://msdn.microsoft.com/en-us/library/aa337371.aspx

 

 

Friday, November 6, 2009 - 1:19:12 PM - jrw39 Back To Top (4381)

This was a very useful article.  I have an additional question regarding running SSIS Package.  I created my SSIS Package in Visual Studio and I'm trying to run it in SQL Server Management Studio\Stored Packages\File Systems\MSDB\Maintenance Plans\(name of package).  This runs fine in Visual Studio without any errors.  But when I run it in Management Studio I get the following problems:

Error: The product level is insufficient for component 'DataReader Source' (279)

Task Data Flow Task Failed

Waring The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1): resulting in failure. 

I'm confused on what I need to do to the datareader and how to adjust the maximum errors allowed for the package.

 

Thanks in advance

JRW

 

Wednesday, July 29, 2009 - 10:43:46 AM - lianaent Back To Top (3807)

Sorry, I wasn't bashing anyone in particular.  I was referring to the "general" people, not any particular person.  A cry of frustration...

The answer you found probably would have helped me, but it's not at all clear in that article that that solution has anything to do with package configurations (I couldn't find the term "configuration" in the article), so I wouldn't have thought to look at Google results with that link.  Even Jamie, the author, admits that it took him a long time to find that solution.  Actually, re-reading it, I think he's talking about something else entirely, but it just happens to be the same thing I did.

Finally, I thought I spelled out my initial problem pretty clearly as re-quoted below (emphasis added).  Where don't I spell out that the problem is in my Foreach loop not retrieving the value from the config file?  In any case, accurate communication is always a difficult task so I do strive to be clear - an elusive goal.  Thanks!

"I have a package-scoped variable, User::FolderName, that I want to contain the folder name of the directory I want to Foreach loop through.  I created a SQL package config file with a filter name, the ConfiguredValue is the folder name, and the PackagePath is \Package.Variables[User::FolderName].Properties[Value].

In my Foreach loop I've set the Folder name in the Collection tab to User::FolderName, but it's always blank."

 

Wednesday, July 29, 2009 - 9:37:14 AM - R3x0r Back To Top (3806)

I'm glad you found the solution to the issue on your own. With that attitude of yours I don't think I would have offered an answer to you even if I were able to correctly deduce your original problem in the first place.Your issue concerned a ForEach loop... why post an unrelated question in this thread? Then go on to bash someone who replies in an attempt to help you?? Amazing.

FYI... I spent exactly 47 seconds to find this blog for your issue: http://blogs.conchango.com/jamiethomson/archive/2006/10/13/SSIS_3A00_-Expressions-on-ForEach-enumerators.aspx


Wednesday, July 29, 2009 - 9:08:19 AM - lianaent Back To Top (3805)

I don't know why this is such a difficult concept for people to grasp!  It's NOT the package configuration file I was struggling with, but how to reference it back in my package. The SQL Server table IS the type I wanted, and I created the package configuration without any problem.  The problem was how to get that value from SQL back into my Foreach loop.

Okay, I'll give it away.  I was trying to set the Folder Name in the Collections tab of the Foreach loop to User::FolderName.  That wasn't working.  What I finally did was create an expression in the Foreach loop setting the property "Directory" to @[User::FolderName].  That's all there was to it. 

Now, that one simple line in any forum anywhere in the world would have saved me 2 weeks of time.  I should charge a Microsoft Support price to anyone who reads my solution.  But, if you can find that solution anywhere else I'll refund your money.

Oh, and you're right, you have to close and reopen the package in order to see the changes in the config file.  Seems a little bizarre, but what the heck...

 

Wednesday, July 29, 2009 - 8:51:58 AM - lianaent Back To Top (3803)

Never mind - I figured it out on my own.  However, since this is apparently arcane knowledge available only to Initiates (those whom after months of fruitless searching have finally figured out Microsoft's illogic on their own) far be it for me to give this informaton out to non-Initiates!


Wednesday, July 29, 2009 - 8:51:14 AM - raybarley Back To Top (3802)

The way that you use the package configuration file starts with setting it up from within your package.  You right click on the Control Flow design surface then select Package Configurations from the popup menu.  You specify what kind of configuration you want; e.g. XML configuration file, then you select the package elements that you want to get their values from the config file.

Take a look at this tip for the step by step details: http://www.mssqltips.com/tip.asp?tip=1434

The tip that you replied to was focused on using a SQL Server table to store the configuration.

One other point - the SSIS package reads the config file when you open the package and when you run it.  If you change the config file while the package is open, the package won't reflect the changes. 

 

Wednesday, July 29, 2009 - 8:05:49 AM - lianaent Back To Top (3801)

I've found hundreds of articles on how to create these package configuration files, and that's wonderful, but they all end at the critical point of how to use them back in your package!  Not a single thread, no clue, after weeks of searching.  I know it must be possible, or else everyone is just pretending to have their packages working.

Okay, details.  First, I'm not interested yet in the environment variable, I just want to use my package variable directly from the package config file.  I have a package-scoped variable, User::FolderName, that I want to contain the folder name of the directory I want to Foreach loop through.  I created a SQL package config file with a filter name, the ConfiguredValue is the folder name, and the PackagePath is \Package.Variables[User::FolderName].Properties[Value].

In my Foreach loop I've set the Folder name in the Collection tab to User::FolderName, but it's always blank.  Obviously this isn't the right way to do this, but does ANYBODY know what the right way is???

Thanks!

Larry


Friday, April 10, 2009 - 2:43:41 AM - raybarley Back To Top (3162)

 If you followed the example in the article andcreated an environment variable to hold the connection string to the configuration database, did you close Visual Studio or BIDS and open it up again?  If you had it open while the environment variable was set it won't see the environment variable until it's closed and reopened.

When you open the package does the connection string get the value that you have in the configuration table?  Did you create the package configuration and set the connection string of your connection manager in the package configuration?

You could also try running your package DTEXECUI; you might see something in the verbose output that helps to figure out what's wrong.

 

Thursday, April 9, 2009 - 10:11:22 PM - anamica.jain Back To Top (3160)

Hi ,

I have tried building package configuration using sql server same way as you have mentioned,but my packege is failing with this error

[Execute SQL Task] Error: Failed to acquire connection "01HW182484.AG_Media_Analytics_Banner_Dispaly.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.

I have tried enabling package configuration using XML,and it works fine.

 

Saturday, March 14, 2009 - 9:03:09 AM - raybarley Back To Top (3003)

To use a SQL ConnectionManager you create a new ADO.NET Connection (right click in the Connection Managers area), then pick .Net Providers\SqlClient Data Provider as the Provider.

You could use an expression on the ConnectionString or ServerName properties of the ADO.NET Connection toset it based on the machine you're on.

Generally the server you're running on can be specified as localhost.  If there are multiple instances of SQL Server then you could use the WMI Data Reader task to determine what SQL Server instances are running on your machine; you'd have to figure out some way to pick the right instance if there are multiple.  

The WMI Data Reader requires a WMI Connection Manager; the key properties for theconnection manager are:

ServerName: \\localhost

Namespace: \root\Microsoft\SqlServer\ComputerManagement

The WMI Data Reader needs a query; e.g.  SELECT DisplayName, ServiceName FROM SqlService

As far as your second idea you can do anything you want with an SSIS package programmatically; I have no experience in doing that; you can refer to Books on Line for the details and some examples.

 

Wednesday, March 11, 2009 - 7:49:07 AM - Crawford Back To Top (2968)

 I've been reading over the papers included in the Project REAL demo from MS and it seems like what you recommend covers most of the best practices that came out of that endeavor.  I've created a package template with logging setup in a similar fashion to what you suggested using the stored procedures provided by Project REAL (with a little tweaking, of course).  There's also a connection manager in the template for each of the dbs used in our data warehousing, which get their connection strings from scoped variables that get loaded from a SQL server configuration (whose connection is provided via an indirect configuration using an environment variable pointing to a single source-controlled config file).  

 Two things I'd like to try and figure out in the up-coming week: 

1) Setup package template to detect the server it's running on and to use a SQL connection manager for that server instead of an OLE DB manager.

2) Come up with a setup task for package template that parses the name of certain tasks within the package and configures each task based on what's contained within the Name property for the task.  Picture it!  A dumbed-down version of drag-and-drop where tasks get configured without ever opening up a property window.  (Obviously, there are probably several drawbacks to doing this including poor maintainability, troubleshooting, etc. I think I'd still like to give it a shot; worst case - I scrap the whole idea after learn something(s) new.)

 Any thoughts on how to achieve either?  I've got a good idea for the second one, but no clue on the first.


Tuesday, March 10, 2009 - 2:32:57 PM - raybarley Back To Top (2966)

 I didn't write a tip about template packages.  I have used template packages on several projects but about the best advice I can give is that you probably can't create just one that will work in every situation.  So even in a single project you could have a couple.  Here are the kinds of things that you would want to have in a template package:

- Package Configuration (as this article discussed)

- Connection Manager(s) - you may write many packages that extract from a particular source and write to another source.  Define them in your template and get their connection string from your choice of Package Configuration.

- Logging - I typically do this by putting an Execute SQL task as the first and last task.  It just calls a standard stored proc that records things like the package name, start time, stop time, rows inserted, updated, deleted, success or failure.

- Error handling - I put an Execute SQL task in the OnTaskFailed and OnError event handlers; OnTaskFailed will log the error and OnError may send an email

- Stub out the control flow - drop the tasks for your common basic flow in the template

The general idea is to not start every package from scratch; figure out what's common, create one or more templates, then use a template as your starting point.

In BIDS 2008 there is some support for templates; you put them in a particular folder then when you want to create a new package you have the option of creating it from one of your templates.

 

Tuesday, March 10, 2009 - 12:47:21 PM - Crawford Back To Top (2965)

 Was the tip mentioned at the end of the article about template packages ever written and if so, where can I find it?  If not, any chance of getting a mini-tip as a reply? ;)


Friday, January 16, 2009 - 8:16:58 AM - Mohsin Back To Top (2570)

I am able to execute the package. I was not sure earlier that the new configurations settings are applied during the run time.

 Thanks,

-Mohsin

Tuesday, January 13, 2009 - 8:44:52 AM - raybarley Back To Top (2548)
Did you run the Package Configurations Organizer? You do this by right clicking on the Control flow design surface then selecting Package Configurations from the popup menu. You then Add a configuration, specify SQL Server as the configuration type, etc. The main thing is to associate the configuration with the property in the package that you want to set from the configuration. From your description I don't know whether you did this. If not you can follow along in the article to get it done.

Tuesday, January 13, 2009 - 8:29:07 AM - Mohsin Back To Top (2547)

Hi All,

How to run the SSIS package which uses configuration values from the SQL Server table.

I have a file watcher task which should use the path value from the table. If I remove the hard coded value from the file watcher task component, I am unable to run this.

 Thanks,

-Mohsin

Thursday, December 18, 2008 - 9:08:35 PM - raybarley Back To Top (2423)
Good point. You also have to close Business Intelligence Developer Studio and reopen it in order to see the new environment variable.

Thursday, December 18, 2008 - 1:58:02 PM - R3x0r Back To Top (2421)

 One important thing to note (which may save someone some troubleshooting time in the future) is the following:

 -  Once you create the new Environment Variable, be sure to restart both the  SQL Server Integration Service & SQL Server Agent services

Failure to do this may cause SSIS packages to hang when they are run by the SQL Server Agent. You will be able to manually run the package all day long, but the job never runs to completion. Hope this helps someone... so they do not spend 4 hours on the phone with Microsoft Support ; )


Monday, December 8, 2008 - 5:27:26 AM - raybarley Back To Top (2359)
You can choose to set the value of any or all package variables via the package configuration. However, you have to specify them individually in the package configurations organizer.

Monday, December 8, 2008 - 4:08:59 AM - anjali Back To Top (2358)

Hi Raybarley

Is it possible that all the variables used in the package are stored in the database and before execution of the package all the variable are been copied form Database to Package using Package configuration of Sql Server 


Sunday, August 24, 2008 - 7:17:44 AM - raybarley Back To Top (1696)

There is also a tip on using XML package configuration on MSSQLTips here: http://www.mssqltips.com/tip.asp?tip=1434

 

Monday, August 18, 2008 - 3:56:49 AM - reneep Back To Top (1648)

This is how to do XML config:

http://www.sqlis.com/26.aspx

Friday, January 25, 2008 - 5:54:23 PM - raybarley Back To Top (249)

There has not been a tip published on the XML config files yet.

Friday, January 25, 2008 - 3:02:16 PM - drek_01 Back To Top (248)

looks great but i was wondering if you have any artical about xml file configuration which has all the variables so the parent and child packages can get the values from .dtsconfig file.

Thanks

Derek

Tuesday, January 22, 2008 - 6:21:22 PM - Marsharks Back To Top (237)

 Thanks. I guess it is a bit manual and this was exactly what I was looking for. I will test it tomorrow.

Tuesday, January 22, 2008 - 5:49:41 PM - raybarley Back To Top (236)

You would generally want a separate configuration database for environment - development, test/QA, and production.  The reason is that your SSIS packages will need different configuration values in each environment, particularly database connection strings.

Let's assume that you start by creating a configuration database in your development environment.  Create a new SSIS package and start adding SQL Server package configurations.  The values will be saved to the SQL Server table that you specify when you define each package configuration.  When you are ready to setup your test environment you want to copy the SQL Server table from development to test.  I open the table in SQL Server Management Studio and copy/paste the rows into an Excel spreadsheet.  Then script out the table, create it in the database on the test server, then copy/paste the values from the Excel spreadsheet into the table, again opening the table in SQL Server Management Studio.  You could use bcp to copy the table or even write an SSIS package to do it.

Tuesday, January 22, 2008 - 12:27:49 PM - Marsharks Back To Top (235)

It was a great article.  How do I deploy to a test server though?  Do I need to create the SSISConfig database in the test server?  What do I need to do to get the values into the test server?  Sorry, but I am very new to this.  I saw examples for deploying using XML files, but not for SQL Server package. 














get free sql tips
agree to terms