UdaWikiWeb.ConfigSSISOPL
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
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
The
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 "
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>
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
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 64bit Operating Systems.
Irrespective of whether your Microsoft SQL Server instance is 64bit the Visual Studio IDE is a 32bit application and as a result ALL OLE DB and .Net Providers MUST be 32bit along with any subsequently interfaces components such as ODBC drivers.
So, this means that when you are developing and debugging SSIS applications you will require a 32bit Provider and if applicable a 32bit ODBC driver.
When you come to run you final SSIS package it is deployed against the MIcrosoft SQL Server instance which is 64bit which will require a 64bit Provider and if applicable a 64bit ODBC driver.
Therefore with 64bit instances of Microsoft SQL Server you will require both 32bit and 64bit components to take full advantage of the features available --
64bit Microsoft SQL Server
32bit Visual Studio IDE
32bit .Net Provider (developing & debugging)
32bit ODBC Driver (developing & debugging)
64bit .Net Provider (final deployment)
64bit ODBC Driver (final deployment)
