How to connect to SQL Server with SSIS ?

How to connect to a SQL Server database with SSIS to import or export data?

Tutorial on how to connect to SQL Server with an SSIS package. To connect to a SQL database from an SSIS package, it is necessary and recommended to use the SSIS Connection Manager. It is also possible to use variables to manage connections strings, but it is an advanced topic and it will be covered in another tutorial. There are different types of connections available, like OLE DB, Flat file, ADO, Analysis Services, etc.

Here is a short, step-by-step tutorial for creating a connection to an MS SQL database from an SSIS package. A connection manager can be used by multiple sources and destinations within a package. And also by multiple packages in a project if it is saved at the project level.

1. First create an SSIS project

First of all, the previous tutorial explains how to create a SSIS project step by step with Visual Studio 2019 and add a new package, i.e. a file in dtsx format.

2. Connect to SQL Server with the SSIS Connection Manager

First, use the Connection Managers area that is open by default when the SSIS package is opened. This area is located at the bottom of the screen in Visual Studio. It is possible to create different connection types for heterogeneous data sources, as below.

In this case, it is New OLE DB Connection to create a connection with the recommended driver for direct connections to SQL Server databases. To create a connection to a SQL Server database in SSIS, follow these steps:

  1. Open an SSIS package from the Solution Explorer
  2. Right-click in the Connection Managers area
  3. Select New OLE DB Connection.
Create a new OLE DB Connection using the SSIS Connection Manager
Create a new OLE DB Connection using the SSIS Connection Manager

On the configuration screen, then select New :

Add an OLA DB connection with the SSIS Connection Manager
Add an OLA DB connection with the SSIS Connection Manager

3. Set up the database connection in the package

Secondly, at this point you only need to fill in the SQL database connection options.

  1. Enter the name of the database server, localhost in the example
  2. The authentication type, here it is Windows Authentication
  3. And choose the database, Expert-Only
How to connect to a SQL Server database with SSIS ?
How to connect to a SQL Server database with SSIS ?

If you need to create an MS SQL database first, then make sure you double check the connection details to avoid connection errors in the process.

4. Test the connectivity to the SQL database

Then test the connection to verify connectivity. It is highly recommended to test the connection to avoid errors later in the package development.

Connect successfully to the SQL Server database from SSIS
Connect successfully to the SQL Server database from SSIS

5. Video tutorial to create a SQL connection with SSIS

A usual for the new tutorials, it is available in video format with all the steps illustrated with Visual Studio 2019.

The logical next step is to create a simple data stream within the SSIS control flow. In general, the purpose of an ETL package is to transfer and eventually transform data from a source to a target. Here another tutorial to solve a typical Excel data integration issue with SSIS, i.e., how to manage Excel data type within a package.

Be the first to comment

Leave a Reply

Your email address will not be published.


*