SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Access to the package config db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gbritton
Aged Yak Warrior

654 Posts

Posted - 12/05/2013 :  16:46:04  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/06/2013 :  05:15:50  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000