Setting DQS Connection in SSIS Script Task


  • Hi,

       We have a current SSIS package which uses an OLE connection object to connect to our SQL Server database. We also have DQS installed on the same server and have written a DQS Cleansing task in SSIS which works nicely.

        If we deploy this to a different environment (e.g. Staging) the OLE connection is set by a parameter in the job which runs the SSIS package. Is it possible to do something in a script task which will set the DQS Connection to use the same server without having to change it manually?

      I have tried something along the lines of:

        int pos1;
                int pos2;
                ConnectionManager OLE = Dts.Connections["LoadingDB_OLE"];
                pos1 = OLE.ConnectionString.IndexOf("Data Source");
                pos2 = OLE.ConnectionString.IndexOf(";", pos1);

                Dts.Connections["DQSCleansing"].ConnectionString = OLE.ConnectionString.Substring(pos1, pos2-pos1);

       ...but it doesn't work.

    Monday, July 01, 2013 4:55 PM