CODEWITHSUNDEEP
sql-serverssis
Ravi Khambhati
Ravi Khambhati

Reputation: 743

Passing Source and Destination for SSIS package created from Export/Import Wizard of SSMS

I have created SSIS package with SQL Server Management Studio. And its working.

But What I would like to do is to pass Source connection string and Destination connection string to this SSIS package when I run this.

One approach that I am planning is to run this package from dtexec tool. Are there any other options?

Upvotes: 0

Views: 370

Answers (1)

billinkc
billinkc

Reputation: 61249

Overview

SSIS, via the Import Export Wizard, is available in all editions of SQL Server. However, SQL Server Express does not allow you the ability to save a package generated using the wizard. And a saved package is what we will be operating on.

What can I make dynamic

You have correctly identified that you can change the connection strings within an SSIS package. Future readers, a connection string does not specify the table, or schema, name. If you want to make the object name dynamic, then you cannot accomplish this with packages built using the Import/Export Wizard.

How do I change the Connection Strings

We have established that packages have been saved out of the Import/Export Wizard although a package built using BIDS/SSDT-BI will by equally valid. The mechanics of running a package is commonly performed through the dtexec utility. Among other things, it allows you to specify a new value for Connections

/Conn[ection] id_or_name;connection_string [[;id_or_name;connection_string]…]: (Optional). Specifies that the connection manager with the specified name or GUID is located in the package, and specifies a connection string.

This option requires that both parameters be specified: the connection manager name or GUID must be provided in the id_or_name argument, and a valid connection string must be specified in the connection_string argument.

Open up your favorite text editor and point it at the package you saved out.

  <DTS:ConnectionManagers>
    <DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[DestinationConnectionOLEDB]"
      DTS:CreationName="OLEDB"
      DTS:DTSID="{6D24E79D-7912-4CB2-88E1-D85A37C21ECF}"
      DTS:ObjectName="DestinationConnectionOLEDB">
      <DTS:ObjectData>
        <DTS:ConnectionManager
          DTS:ConnectionString="Data Source=.\dev2014;Initial Catalog=master;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=false;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
    <DTS:ConnectionManager
      DTS:refId="Package.ConnectionManagers[SourceConnectionOLEDB]"
      DTS:CreationName="OLEDB"
      DTS:DTSID="{A9768F28-28F6-4E61-8717-24B39DB0BE77}"
      DTS:ObjectName="SourceConnectionOLEDB">
      <DTS:ObjectData>
        <DTS:ConnectionManager
          DTS:ConnectionString="Data Source=AVATHAR\DEV2014;Initial Catalog=PHOENIX;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=false;" />
      </DTS:ObjectData>
    </DTS:ConnectionManager>
  </DTS:ConnectionManagers>

That results in a call like

dtexec /file C:\ssis\pkg1.dtsx /conn "SourceConnectionOLEDB";"\"Data Source=localhost\TestSQL2008R2;Initial Catalog=ConnDB;Integrated Security=SSPI;\"

Upvotes: 2

Related Questions