SSIS Dynamic Connection Manger Validation error

Question

SSIS Dynamic Connection Manger Validation error

i want to change the connection manager connection in ssis at run time so that it usese the connectionstring to connect and give me the result. but it is giving me this error i have set the delay validation property to false on the dataflow task???????

Error: 0xC0202009 at Package, Connection manager "LHRPC-00916.fnp_scenter_test": SSIS        Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC020801C at Data Flow Task, OLE DB Source [1]: SSIS Error Code   DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the   connection manager "LHRPC-00916.fnp_scenter_test" failed with error code 0xC0202009.  There   may be error messages posted before this with more information on why the AcquireConnection   method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "OLE DB Source" (1) failed    validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

ControlFlow control flow diagram

dataflow diagram

connectionmanager http://www.dropbox.com/s/ozk4qynbelcr2n1/Untitled.png Dynamic Connection manager I have two one static and on dynamic

sql

sql-server-2008

ssis

business-intelligence

asked on Stack Overflow Jul 30, 2013 by

Kinnan Nawaz • edited Jul 30, 2013 by

Kinnan Nawaz

2 Answers

Use an ADO.NET managed connection for this purpose. I had a similar situation and simply created a second connection manager with the same parameters as the OLE DB one.

Also, instead of changing the connection string in your script task, parametrize the connection manager (right-click\parametrize). If you need more than one set of parameters, use more than one connection managers.

With a managed conn manager, this would be how you would open the connection in your code:

SqlConnection GetConnection(string ConnMgrName)
{
    ConnectionManager cm = Dts.Connections[ConnMgrName];
    SqlConnection conn = (SqlConnection)cm.AcquireConnection(null);
    return conn;
}

answered on Stack Overflow Jul 30, 2013 by

Metaphor

Wrote the below script in the script task

 public void CheckConnectionString()
    {
        try
        {

            if (Dts.Connections["DynamicConnection"] != null)
            {
                ConnectionManager ConMGr = Dts.Connections["DynamicConnection"];
                ConMGr.AcquireConnection(null);
                Dts.Variables["ConnectionAvailable"].Value = true;
            }
        }
        catch (Exception ex)
        {
            Dts.Variables["ConnectionAvailable"].Value = false;
            Dts.Variables["ErrorDescription"].Value = ex.Message;
        }
        finally
        {
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }

Added the precedence constraint condition

enter image description here

answered on Stack Overflow Aug 6, 2013 by

Kinnan Nawaz

User contributions licensed under CC BY-SA 3.0