none
SSIS - dynamic DB connection fails !!!

    Question

  • This i the error i get. I can download rows from one server or one IP address. But, cannot download rows from the other IP.

    
    [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DynamicConnector" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    [SSIS.Pipeline] Error: One or more component failed validation.

    Error: There were errors during task validation.

    My problem is similar to this post - http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/acace8a1-ec18-44fb-86d6-db95f4ceeded

    Tried setting delay validation = true in package, execute sql task, foreach loop and data flow task. But, still i get the error.

    • Edited by Greg3tl Wednesday, May 15, 2013 12:33 AM
    Wednesday, May 15, 2013 12:30 AM

All replies

  • Job - export table rows from external sql servers and store them in one data base. I used a tutorial http://www.codeproject.com/Articles/14341/Using-the-Foreach-ADO-Enumerator-in-SSIS to do this.

    I think I understand it correctly now. But, not sure about one page in the tutorial - please see image below.

    Tuesday, May 14, 2013 10:03 AM
  • Hello,
    As far as I know, the value stored in ConnectionString (next to the red arrow) will be replaced by the value stored in the variable when you save the package.

    I may have misunderstood your question but I'm pretty sure it works like this.

    • Proposed as answer by Romain Keiser Tuesday, May 14, 2013 2:55 PM
    • Unproposed as answer by Romain Keiser Tuesday, May 14, 2013 2:55 PM
    Tuesday, May 14, 2013 10:12 AM
  • The value for the connection string will always come from the variable.  The value that is showing in the Connection String property box will be what you originally configured the connection manager to use. But that value will not be used when you run the package.  It will always use the variable value.

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

    • Proposed as answer by Koen Verbeeck Tuesday, May 14, 2013 11:39 AM
    Tuesday, May 14, 2013 11:05 AM
  • So, basically as my hunch went, the "static connection string" tells SSIS the mapping of the tables (you cannot proceed without proper mapping). It tells SSIS what the source table(S) looks like and how the destination will be mapped to the source(S). The actual data comes from the connection specified in the variable "connection---string". Right ?

    its something like this in a programming language ???

    int i = 2013;//like your static connection string

    for(i = 0; i < some value; i ++){ 

    //do something

    }




    • Edited by Greg3tl Tuesday, May 14, 2013 4:30 PM
    Tuesday, May 14, 2013 4:21 PM
  • Let's take an example :

    You firstly created your connection manager by setting the server = ServerA and database = DatabaseA

    You create your package using this connection. In every source, the tables will come from ServerA and DatabaseA.

    Then, you create your variable with server = ServerB and database = DatabaseB and assign it to the "ConnectionString" expression of the "Connection Manager".

    When you save your package, the ConnectionString (next to the arrow) won't be "ServerA;DatabaseA" but "ServerB;DatabaseB".
    Then, all sources that were bound to DatabaseA & ServerA will be bound to DatabaseB & ServerB. That's why you may find validation errors if tables are different between the two databases.

    Sorry to explain it like that but I don't understand what you mean with the programming example.

    Hope this help.


    Tuesday, May 14, 2013 4:28 PM
  • SHow us how are you setting the connection string dynamically

    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, May 15, 2013 1:36 AM
  • I want to/have to use SQL authentication to connect to a remote sql server via SSIS connection manager. In connection manager go to edit. Then, i set log on mode to  SQL authentication.  I also set "save my password". Then, I test connection and it works. I save my package. But, when i go back to the same edit screen, i see mode is changed back to windows auth. Why ? I think that my project is failing because of this. I keep on getting error - 

    [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DynamicConnector" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [SSIS.Pipeline] Error: One or more component failed validation.

    Error: There were errors during task validation.

    Wednesday, May 15, 2013 4:18 AM
  • Is there any connection string or server name variable that you assign at runtime ? May be that variable is not correctly assigned.

    Regards, RSingh

    Wednesday, May 15, 2013 4:23 AM
  • I will post a series of photos as steps. Please ignore the cross X signs on all objects. Those do not occur on production/real package. This tutorial package was made on another computer (reason ? That's a long story).

    Quick intro -  7 databases contain table with name ABCDEF. I have to extract all columns, all rows from  each of the seven servers and then 

    put it into the table ABCDEF of a single server or data warehouse. Warehouse is same as 7 servers. I do all my SSIS, SQL work inside the warehouse server. 

    We first create two variables which will be used later.

     

    • Edited by Greg3tl Wednesday, May 15, 2013 5:06 AM
    Wednesday, May 15, 2013 4:54 AM
  • two 

    Wednesday, May 15, 2013 4:54 AM
  • three

    Wednesday, May 15, 2013 4:55 AM
  • four

    Wednesday, May 15, 2013 4:55 AM
  • five - NOTE ! Execute sql task of 1st photo is also assigned connection - one server

    OLE db source data access mode (DAM) is sql. The sql is correct and i can see preview of it. 



    • Edited by Greg3tl Wednesday, May 15, 2013 5:02 AM
    Wednesday, May 15, 2013 4:55 AM
  • six

    Wednesday, May 15, 2013 4:57 AM
  • seven

    Wednesday, May 15, 2013 4:57 AM
  • eight

    Wednesday, May 15, 2013 4:57 AM
  • nine

    Wednesday, May 15, 2013 4:57 AM
  • ten

    Wednesday, May 15, 2013 4:58 AM
  • Hi etl man,

    Please check if "Integrated Security=SSPI;" is available in your connection string, if so do remove it as this is the one responsible for changing the connection back to Windows Auth mode.

    As your Connection manager name is: "DynamicConnector", I believe you must be trying to do it dynamic through expression or variables, so change it accordingly.

    Regards,...


    Regards, Pratik

    Wednesday, May 15, 2013 5:47 AM
  • Hi etl man,

    Please make sure you are not using the "Integrated Security=SSPI;" in the OneServer variable value.

    Remove it and test it again.

    -Thanks


    Regards, Pratik

    Wednesday, May 15, 2013 5:50 AM
  • Things look good to me, what are the sample values for User:OneServer, i can see that it has the values returned from the dataset, show the values on the tables, as you are asisnging the connection string property this should have the full connection string

    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, May 15, 2013 5:51 AM
  • DataSource=<One IP address here>,Initial Catalog=MyDatabase;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;

    I don't really what this sqln10, sspi, auto translate means. Also, i use a SUPER windows login to access all remote servers. My connections look like - 

    One server -

    IP address = ServerX, 

    user name - SUPER

    pass - passForSuper

    DynamicConnector -

    IP address = ServerZ, 

    user name - SUPER

    pass - passForSuper

    To remind you, my SSIS package is inside Warehouse or ServerX. That server can access two remote servers, but not the others. To access every server. user name SUPER must be used. 

    I save the user name and password. But, when i come back to the password, username menu, there is no password. 

    I am using SSIS 2008
    • Edited by Greg3tl Wednesday, May 15, 2013 6:28 AM
    Wednesday, May 15, 2013 6:27 AM
  • Hi etl man,

    Please make sure you are not using the "Integrated Security=SSPI;" in the OneServer variable value.

    Remove it and test it again.

    -Thanks


    Regards, Pratik

    It still fails. What made you think that could be the reason for the problem ?

    Wednesday, May 15, 2013 6:37 AM
  • nine

    In this post, you have shown that you are using "SQL Server Auth", and if you are creating the connection string manually and also using the "Integrated Security=SSPI;" , then it will change back to Windows Auth mode. I kind of thought that you are storing the same in your OneServer variable value.

    I tested the same and got the same error with this change... :(

    I'm sorry, if this didn't workout...!!!


    Regards, Pratik


    • Edited by Pratik Dey Wednesday, May 15, 2013 7:00 AM
    Wednesday, May 15, 2013 6:58 AM
  • DataSource=<One IP address here>,Initial Catalog=MyDatabase;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;

    I don't really what this sqln10, sspi, auto translate means. Also, i use a SUPER windows login to access all remote servers. My connections look like - 

    Hey etl man,

    You are using the same in your OneServer variable "Integrated Security=SSPI". Remove it and test again...!!!

    Your DataSource should be like this:

    DataSource=<One IP address here>,Initial Catalog=MyDatabase;Provider=SQLNCLI10;Auto Translate=False;

    Also try "Provider=SQLNCLI10.1" as it is nothing but SQL Server Native Client version. My version is 10.1, may be urs is 10 (default is 10 which was released with SQL Server 2008, unless you have upgraded your SQL Server.)


    Regards, Pratik


    • Edited by Pratik Dey Wednesday, May 15, 2013 7:09 AM
    Wednesday, May 15, 2013 7:03 AM
  • How do i find out the right provider for my DB ? This connection string stuff is complicated. How do you figure out the right parameters to use in that string according to your unique situation.

    Wednesday, May 15, 2013 7:15 AM
  • Hi etl man,

    1. Create a normal Connection manager as Windows Auth mode (forget about dynamic connactions for now...!!!)

    2. Once created, go to the proporties of the ConnectionManager and look for the connection string value, copy somewhere.

    3. Now open the ConnectionManager and change it to SQL Server Auth mode, save it.

    4. Follow step 2 to get the new Connection string.

    5. With this test, you will be clear about 2 things

        a) What is your SQL Server Provider version (10 or 10.1)

        b) and second is what is the connection string difference when you change from Windows Auth mode to SQL Server Auth mode.

    I hope you can understand from this small test and is your package working or not by removing the "Integrated Security=SSPI"???

    - PD


    Regards,
         Pratik
    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if it was...


    • Edited by Pratik Dey Wednesday, May 15, 2013 7:45 AM
    Wednesday, May 15, 2013 7:44 AM
  • 3. Now open the ConnectionManager and change it to SQL Server Auth mode, save it.

    how do you do that ? 

    Wednesday, May 15, 2013 8:03 AM
  • Hi etl man,

    Below is the details of the Step 3:

    1 DoubleClick on the saved ConnectionManager > Click on "Use SQL Server Authentication" > provide "User Name" and "Password".

    2. This is only for your understanding, so no need to click on the "Test Connection" button, just provide any user id & password.

    3. Click on "OK" button, it will save the connection manager.

    Hope you arre able to understand now...

    Regards,


    - Pratik

         Please use Marked as Answer if my post solved your problem and use Vote As Helpful if it was...


    • Edited by Pratik Dey Wednesday, May 15, 2013 8:11 AM
    Wednesday, May 15, 2013 8:10 AM
  • Hi Etlman,

    Change the package protection level to "EncryptSensitiveWithUserKey" by setting this you dont need to put password again and again. 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful


    MH

    Wednesday, May 15, 2013 11:06 AM
  • The problem is that your user name and password are absent from connection string, the current string assumes that the user name executing would have the access to database, which is windows authentication.

    You have to resolve it like below:

    a) As you said you have a superadmin account, which has the access to the database.You SSIS package woudl execute as this user then you would be able to connect, How to do this:

    Start your BIDS RUN AS--> SuperAdmin.

    IN Prod environment: While executing your dtexec create a proxy account to run the SSIS package or start SQL server agent service with the SuperAdmin Account

    I recommend, use the same the same connection string as is:

    for testing while devt on dev environment, start your BIDS environment with the same user.

    Though easiest and not recommended way would be your connection string to look like

    Provider=SQLNCLI10;Password=<Password>;Persist Security Info=True;User ID=<UserName>";Initial Catalog=<ServerName>;Data Source=<DB Name>

    Repalce things inside the <>


    Abhinav
    http://bishtabhinav.wordpress.com/

    Wednesday, May 15, 2013 4:43 PM