Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Question on Dynamic Connection String for OLE DB

Answered Question on Dynamic Connection String for OLE DB

  • Saturday, January 05, 2013 1:02 AM
     
     
    Sign In to Vote
    0

    Here is the situation:

     I have a very simple query which needs to be run on say 10 database on  3 servers. Credentials to access to these database are consistent. 

    I know I could create 10x3 = 30 connection managers for this task but it just sounds too stupid. 

    I'd like to know if there is a solution for me to loop each of these 10 databases, once it's done, move on to the next server and query the next 10 databases until the third one has been covered. 

      Thanks

      Hui

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     

All Replies

  • Saturday, January 05, 2013 1:18 AM
     
     
    Sign In to Vote
    0

    Hi Hui,

    Yes, it's very much possible, if your metadata is not going to change. E.g. if you are querying object of same schema, and pushing data to an object of same schema. You can achieve it by setting up expressions in your connection string. You can hold values of Server, DB etc in some variable and then dynamically set up your connections.

    HTH

    Regards,
    Santosh

    http://microsoftbizintel.wordpress.com/

     
  • Saturday, January 05, 2013 5:09 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Create a control table, may be CREATE TABLE DBConnections (   ID int,   ServerName ,   DatabaseName ,TableName,   ConString)—all varchars except ID…

    Also Create a table to hold results, CREATE TABLE Results(  TableName ,  ConString ,  RecCount int,

    LoadTime  datetime)

     Load all connection strings here in your table.  Within SSIS package, use Execute SQL Task to query all the connection strings and store the result-set in a variable of object type

    Now Use ForEach Loop container to shred the content of the object variable and iterate through each of the connection strings.

    Place an Execute SQL task inside ForEach Loop container with the query  you have to run in all the DB instances. You can use Script Task to modify your query if needed.

    Create 4 Variables ConString, Query, DBConnections( object),Table

    Now Create 2 Connection managers

    Local.Test and Dynamic

    Local to query DBConnections control table and Dynamic as the name suggests to set up dynamic connections

    Drag  Execute SQL Task and rename with "EST - Get List of Conn Strings". Now click on properties and set following values :

    Result Set: Full Result Set

    Connection: Local.Test

    ConnectionType: Direct Input

    SQL Statement: SELECT ConnString,TableName FROM DBConnections

    Now click on Result Set to store the result of SQL Task in variable User:: DBConnections and result name 0

    Now setup ForEach Loop container ,In  ForEach Loop Click on Collection  and select Foreach ADO Enumerator as Enumerator. In Enumerator configuration, select User:: DBConnections as ADO object source variable

    Now Script Task in C# inside ForEach Loop container  Select User::ConnString,User::Table as ReadOnlyVariables and User::Query as ReadWriteVariables. Now click on Edit Script button and write following code in Main function:

    public void Main()

{
   try
   {
      String Table = Dts.Variables["User::Table"].Value.ToString();
      String ConString = Dts.Variables["User::ConString"].Value.ToString();
      MessageBox.Show("Table = " + Table + "\nCurrentConString = " + ConString);
      string SQL = "SELECT '" + Table + "' AS TableName, N'" + ConString + "' AS ConString, COUNT (*) AS RecCount, GETDATE() AS LoadTime FROM " + Dts.Variables["User::Table"].Value.ToString() + " (NOLOCK)";

      Dts.Variables["User::Query"].Value = SQL;
      Dts.TaskResult = (int)ScriptResults.Success;
   }
   catch (Exception e) 
   {
      Dts.Log(e.Message, 0, null);
   }
}

    Finally get you DFT  Add OLE DB Source and Dest. Double click on OLE DB Source to configure the properties. Select Dynamic Connection as OLE DB connection manager and SQL command from variable as Data access mode. Select variable name as User::Query. Now click on columns to generate meta data.

    Double click on OLE DB Destination to configure the properties. Select Local.Test as OLE DB connection manager and Table or view - fast load as Data access mode. Select [dbo].[Results] as Name of the table or the view. now click on Mappings to map the columns from source  


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .



    • Edited by Dia.Agha Saturday, January 05, 2013 5:11 AM
    • Marked As Answer by cat_ca Thursday, January 10, 2013 7:08 PM
    •  
     
  • Monday, January 07, 2013 5:44 PM
     
     
    Sign In to Vote
    0
    Thanks , I'll try it and report the results here

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Monday, January 07, 2013 6:13 PM
     
     
    Sign In to Vote
    0

    Hi Dia:

      Your comments " Load all connection strings here in your table.  Within SSIS package, use Execute SQL Task to query all the connection strings "

    First, column ConString, do I have to manually populate this column in table DBConnections? 

    Second: What's the format of ConString?  Are you talking about sth like below: (that's the one I get from connection manager property )

    Data Source=10.50.30.8;User ID=hshi;Initial Catalog=Configuration;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS-apiCentralizedDB-{EB3ECC9A-0092-4FFC-9267-B9CABFA56855}10.50.30.8.Configuration.hshi;Auto Translate=False;

    Thanks

    Hui

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Monday, January 07, 2013 6:39 PM
     
     
    Sign In to Vote
    0

    Until SQL Server is able to read your mind, you are going to have to insert the connection strings into the DBConnections table.  Computers are still rather poor at mind reading.

    The Connection strings that you would enter is the connection string you have in your post.  You just change the Data Source and Initial Catalog and add the password.

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Monday, January 07, 2013 6:44 PM
     
     
    Sign In to Vote
    0

    Hi russ, thanks for your posting, a couple of questions here

    1. "You just change the Data Source and Initial Catalog and add the password.  "  where do I insert the  password?  Can you highlight it below?

    Data Source=10.50.30.8;User ID=hshi;Initial Catalog=XXXXXXX;Provider=SQLNCLI10.1;Persist Security Info=True;Application Name=SSIS-apiCentralizedDB-{EB3ECC9A-0092-4FFC-9267-B9CABFA56855}10.50.30.8.Configuration.hshi;Auto Translate=False;

    2. The password inserted will be encrypted or plain password? 

    thanks

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Monday, January 07, 2013 7:00 PM
     
     
    Sign In to Vote
    0

    Check http://www.connectionstrings.com/articles/show/all-sql-server-connection-string-keywords for connection string.  You can search for way to encrypt password, but it isn't automatic.

    I can't be much more help, my flu fever is rising right now so I need to go back to bed.

    Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

     
  • Monday, January 07, 2013 7:13 PM
     
     
    Sign In to Vote
    0

    yes you would populate control table youself

    for example

    INSERT INTO DBConnections

    SELECT 1 ID,
    '(local)' ServerName, --Define required Server
    'TestHN' DatabaseName,--Define DB Name
    'TestTable' TableName,
    'Data Source=(local);Initial Catalog=TestHN;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;' ConString
     

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

     
  • Monday, January 07, 2013 8:06 PM
     
     
    Sign In to Vote
    0

    Thanks Dia, Where you did you put sql server authentication in the ConString column?

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Monday, January 07, 2013 8:29 PM
     
     
    Sign In to Vote
    0
    I am not using windows authentication ,just to be clear...

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Tuesday, January 08, 2013 1:07 PM
     
     
    Sign In to Vote
    0
    Yes, in ConString Column.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

     
  • Tuesday, January 08, 2013 11:18 PM
     
     
    Sign In to Vote
    0

    Hi Dia/Russ:

      I've encountered the roadblock  and it is the dynamic connection manager. 

    How did you create that dynamic connection manager? 

    I've created a OLE manager and put regular server name/catalog and credentials to pass.

    After that, I've set up the expressions on the Connection String property and have it point to the value

    of User::ConnString.

     However, in the ForEach loop, when I tried to put a DFT , at the OLE DB source editor, I chose the dynamic as the connection manger, but it kept telling me the following errors;

    Error 1 Validation error. Data Flow Task: Data Flow Task: A connection manager has not been assigned to the runtime connection "OleDbConnection" (10).   apiCentralizedDB.dtsx 0 0

    Error 2 Validation error. Data Flow Task OLE DB Source [1]: A connection manager has not been assigned to the runtime connection "OleDbConnection" (10).   apiCentralizedDB.dtsx 0 0

    any idea, I just do not how to correct that...

    thanks

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Wednesday, January 09, 2013 2:19 PM
     
     
    Sign In to Vote
    0

    In your execute sql task your sql statement is this

    select   ConString, TableName From DBConnections

    and resultset is full resultset

    and resultset mapped to variable User::DBConnections

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

     
  • Wednesday, January 09, 2013 6:29 PM
     
     
    Sign In to Vote
    0

    hi dia, I did exactly what you said, and this User::DBConnections will be shredded in the next foreach loop task and  ConString, TableName will be mapped to  two variables in the variable mappings .   variable that contains value of ConString will be used in the expressions for the dynamic connection manager in the connectionString property, correct? 

    The problem is for the dynamic connection manager, it kept telling me that  a connection manager has not been assigned to the runtime connection "oleDBConnection", I just can not create the following DFT task because of that.

     any ideas?

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Wednesday, January 09, 2013 6:49 PM
     
     
    Sign In to Vote
    0

    Just making a guess here..did you played around with the Retain same connection property of the dynamic conn manager?

    Thanks,

    hsbal

     
  • Wednesday, January 09, 2013 7:19 PM
     
     
    Sign In to Vote
    0
    hi Harry, Retain same connection property is false as default. After I've changed to True, error retains the same. thanks

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Thursday, January 10, 2013 1:09 AM
     
     
    Sign In to Vote
    0

    Latest updates:

     After I've changed data source to IP address , I am able to have dynamic connection running. 

    The Data flow in the foreach loop contains however is giving me the trouble. Seems it can only run once and then stopped as it was supposed to run 30 times . 

    I have 30 connection strings in the table. 

      If I only run the script task and disable the DFT, the script tasks did run 30 times with different connection strings. 

    This is strange, any comments?

    thanks

    Hui

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Thursday, January 10, 2013 1:25 AM
     
     
    Sign In to Vote
    0

    Yeaaaaaaah something is running, so now just few things to make sure, You have Executesql task on top, then for each loop, and inside  you have script task and Dataflow.

    You have 4 Variables ConString (string)  , Query (string), DBConnections( object),Table (string)

    ConString (string) Data Source=(local);Initial Catalog=Test;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;

    Query (string)=SELECT '' TableName,N'' ConString,0 RecordCount,GETDATE() ActionTime

    Table (string)= any table that is blank, it will store the table name of current connection string.

     2 Connection managers  Local.Test and Dynamic

    Local to query DBConnections control table and Dynamic as the name suggests to set up dynamic connections  Now I guess this is will resolve the issue, right click on connection manager and in properties, go on expressions. in expression go to connectionstring and map it to your variable User::Constring

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

     
  • Thursday, January 10, 2013 6:04 PM
     
     
    Sign In to Vote
    0

    hi Dia, the conString I used is below

    ConString (string) =  Data Source=10.xxxx.xxxx.xxx ;Initial Catalog=Test;Provider=SQLNCLI10.1;User Id = xxxx; Password = xxxx; 

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Thursday, January 10, 2013 6:24 PM
     
     
    Sign In to Vote
    0
    the problems seems in the Foreach loop . The DFT is running the correct loops however, seems the connectionString passed to the dynamic connection manager seems to be the same in every loop. The odd thing is the script task is showing a different connString in each loop

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Thursday, January 10, 2013 7:07 PM
     
     
    Sign In to Vote
    0

    I think the variable that holds diff conn strings in script task is used to configure expression for the dynamic connection string.

    Thanks,

    hsbal

     
  • Thursday, January 10, 2013 7:08 PM
     
     
    Sign In to Vote
    0

    Dia, I think I've found the issue. The dynamic connectionmanger has the RetainSameConnection property to True . After I've changed to false, foreach loop is populating correct data into the target table.

    Thanks for your help

    --Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

     
  • Friday, January 11, 2013 1:29 PM
     
     
    Sign In to Vote
    0
    good I am Glad, I saw that reply of Harry :) I am happy I was able to help !

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .