Step by step tutorial on how to connect to a SQL Server database using the SSIS connection manager to import or export data.
Tutorial on how to connect to a SQL Server database using the SSIS connection manager. 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.
Table of Contents
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:
- Open an SSIS package from the Solution Explorer
- Right-click in the Connection Managers area
- Select New OLE DB Connection.
On the configuration screen, then select New :
3. Set up the database connection in the package
Secondly, at this point you only need to fill in the SQL database connection options.
- Enter the name of the database server, localhost in the example
- The authentication type, here it is Windows Authentication
- And choose the database, Expert-Only
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.
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.
About Integration Services connectivity to SQL Server databases
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