Data Migration & Integration Configuration

Configuring and Using Microsoft SQL Server 2005 SSIS

Typically, SSIS packages use OLE DB or .Net Providers for communication to external data sources and there are several means of establishing connections using thesse Providers.

This document aims to briefly demonstrate the common methods using OpenLink and Microsoft Providers.

Business Intelligence Development Studio

The Business Intelligence Development Studio is basically Microsoft Visual Studio 2008 with additional project types that are specific to Microsoft SQL Server business intelligence. Business Intelligence Development Studio is the primary environment that you will use to develop business solutions that include Analysis Services, Integration Services, and Reporting Services projects.

See the following link on MSDN for more information - http://msdn.microsoft.com/en-us/library/ms173767.aspx

There are several interfaces to establish connections to external data sources and within each interface there is support for both OLE DB and .Net Providers.

This document will concentrate on the following --

• Interfaces
  
  • SSIS Import and Export Wizard...
    
  • Comnnection Manager
    
  • Solution Explorer -> Data Sources
    
  • Server Explorer -> Data Connections
    
• Providers
  
  • Microsoft .Net Provider for ODBC
    
  • OpenLink .Net Provider for ODBC (.Net to ODBC Bridge)
    
  • OpenLink .Net Generic Client Provider (Multi-tier client)

The OpenLink OLE DB Provider (OpenLinkODBC) will not be covered since in much the same vein as its Microsoft counterpart it should be deprecated in favour of its .Net alternative.

Create a new Integration Services Project

An Integration Services Project is one of the additional project types (mentioned above) that is included with the Microsoft SQL Server 2005 so you should start by selecting -File -> New -> Project

Highlight a Project Type of Business Intelligence Projects then highlight the Visual Studio installed template Integration Services Project

Enter a suitable name, location, etc.; then select OK

Establishing a connection

SSIS Import and Export Wizard...

The quickest method of building a complete SSIS package ready to run is by using the SSIS Import and Export Wizard...

Select Project -> SSIS Import and Export Wizard... to start the wizard.

You will then be prompted to give connection information pertaining to the "source from which to copy data"

Select your chosen data provider in the "Data Source" drop down list.

Enter the requested connection information which will vary depending upon the Provider chosen above

(In the case of the aforementioned providers addressed in this document, specific connection details will be given below)

Next, repeat the last steps choosing a destination for the data then Next

Choose whether you would like to "Copy data from one or more tables or views" or "Write a query to specify the data to transfer" then Next

If you choose "Write a query to specify the data to transfer" then you will be prompted to enter a valid SQL statement prior to reaching the "Select Source Tables and Views" dialog.

At the "Select Source Tables and Views" dialog you have several options which include; editing the destination table name, editing the data type mappings and previewing the data, etc, etc...

Finally, select Next, then Finish to complete the wizard and perform the Import and Export.

Connection Managers

Connections for use with manually crafted SSIS packages can be created via the "Connection Managers" lower central pane in the IDE.

Right-click the pane, as prompted, to add a new connection manager to the SSIS package.

Select New ADO.NET Connection

In the resulting "Configure ADO.NET Connection Manager" dialog, select New

In the "Provider" drop down list select the required .Net Provider then select OK

The main portion of the windows should now change to reflect the Provider selected above.

This dialog differs from the Import and Export wizard insomuch that there are two views available - a "Connection" view and an "All" view which can be selected by clicking the respective large button to the left of the dialog.

In short, the "Connection" button produces a form type display populated with text fields and dropdown lists etc that allow you to configure the chosen Provider.

Alternatively, the "All" view gives a kind of key/value list similar to that presented in the Import and Export wizard.

Once you have configured the Provider you can test the connection then select OK to save the configured connection which then appears in the "Data Connections" pane of the previous dialog and subsequently in the main "Connection Managers" pane on the main display.

This connection is now available for use in a "DataReader Source" component.

Solution Explorer -> Data Sources

Connections can also be created using the Data Source Wizard.

These Data Sources are solution wide data connections which can be used across many Packages that may make up the Project.

Ensure the "Solution Explorer" is visible -
View -> Solution Explorer

Right click on Data Sources then select New Data Source

You should now see the "Data Source Wizard".

Progress through the wizard and follow the instructions for creating a "Connection Manager" (see above) or select a previously defined connection from the "Data Connections" pane.

Once created the new "Data Source" is available for use when creating a new "Connection Managers" (above) by selecting "New Connection From Data Source...".

Server Explorer -> Data Connections

Connections can also be added through the "Server Explorer".

The Server Explorer displays database connections beneath the Data Connections node.

After a connection is established, you can design programs to open connections and retrieve and manipulate the data provided, or directly access and work with the data using Visual Database Tools.

Ensure the "Server Explorer" is visible -
View -> Server Explorer

Right click on Data Connections then select Add Connection

Against the "Data Source" choice select the Change button to choose the required Provider.

Once again, the main dialog, which looks like a form, will change to reflect the chosen Provider.

Complete the fields then test the connection before selecting OK

Once created the new "Data Connection" is available for interrogation at a table and column level.

Configuring the Providers

Microsoft .Net Provider for ODBC

Typically, you will be asked to provide a connection string which should be of the form--

DSN=<odbc_dsn_name>;UID=<username>;PWD=<password>

OpenLink .Net Provider for ODBC (.Net to ODBC Bridge)

Typically, you will be asked to provide a connection string which should be of the form--

DSN=<odbc_dsn_name>;UID=<username>;PWD=<password>

Alternatively, you can provide the individual parameters in their own fields beneath the ConnectionString field.

OpenLink .Net GenericClient Provider (Multi-tier client)

Typically, you will be asked to provide a connection string which should be of the form--

HOST=<multi_tier_server_hostname>;PORT=<port>;SVT=<server_type>;DATABASE=<database>;OPTIONS=<connection_options>;UID=<username>;PWD=<password>;

Additional Information

There is one very important consideration to bear in mind when dealing with 64-bit Operating Systems.

Irrespective of whether your Microsoft SQL Server instance is 64-bit, the Visual Studio IDE is a 32-bit application and as a result all OLE DB and .Net Providers must be 32-bit, along with any subsequently interfaced components such as ODBC drivers.

This means that when you are developing and debugging SSIS applications you will require a 32-bit Provider and if applicable a 32-bit ODBC driver.

When you come to run your final SSIS package, if it is deployed against a Microsoft SQL Server instance which is 64-bit, it will require a 64-bit Provider and if applicable a 64-bit ODBC driver.

Therefore, with 64-bit instances of Microsoft SQL Server you will require both 32-bit and 64-bit components to take full advantage of all the available features --

• 64-bit Microsoft SQL Server
  
• 32-bit Visual Studio IDE
  
• 32-bit .Net Provider (developing & debugging)
  
• 32-bit ODBC Driver (developing & debugging)
  
• 64-bit .Net Provider (final deployment)
  
• 64-bit ODBC Driver (final deployment)