Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Access to the package config db

Author  Topic 

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2013-12-05 : 16:46:04
Say that I'm using Package Configurations and storing those configs on SQL server. I create a db called Config and using the wizard, create a table in it called Configs using a connection called Config_Conn. I also have another table in the same DB for other uses, called MyTable, using the same connectiom

Now, suppose that this db lives on a server that I can only access with SQL Server authentication. That is, I've previously setup an OLE DB Connection manager called Config_Conn, which points to the database Config on the SQL Server I'm using, using SQL Server authentication.

Next, I have a database I want to access from my package. Call it MyOtherDB and let is use the connection manager MYOtherDB_Conn. I want to configure the connection manager for this DB at run time using Package Configurations. So, to summarize I have:

Server: MyServer, only supports SQL Server authentication
Database: Config, with a table, Configs that holds my configurations
Tables: Configs, MyTable
Database: MyOtherDB, possibly on another server, that I want to configure using the package configuration


The Package configuration has two entries:
1. Connect_Config, for the connection Config_Conn so that I can get at MyTable at run time
2. Connect_MyOtherDB for the connection MYOtherDB_Conn


My question is this:

When SSIS starts up, how does it authenticate to the server holding the Config database? It seems like a catch-22.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-06 : 05:15:50
you need to have a separate configuration to store the config value for MyServer (with sql username and password). You can choose to store this on a XML file or using ENvironment variable in the server.
Otherwise you need to have another sql db where you need to create a table and store it
We usually use Environment variable for this.
This method is called Indirect configuration.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -