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.
| 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 connectiomNow, 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 authenticationDatabase: Config, with a table, Configs that holds my configurationsTables: Configs, MyTableDatabase: MyOtherDB, possibly on another server, that I want to configure using the package configurationThe Package configuration has two entries:1. Connect_Config, for the connection Config_Conn so that I can get at MyTable at run time2. Connect_MyOtherDB for the connection MYOtherDB_ConnMy 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 itWe usually use Environment variable for this.This method is called Indirect configuration.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|
|
|
|
Access to the package config db