How to create a connection to a SQL Server database in SSIS to be used in a package?
To connect to a SQL Server database from an SSIS package, it is necessary and recommended to use the 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.
Create a connection to SQL Server from an SSIS package
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.
Prerequisites for this tutorial: 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.
1. Create a connection to a database 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 :
2. Set up the database connection
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.
3. Test the connectivity to the database
Then test the connection to verify connectivity. It is highly recommended to test the connection to avoid errors later in the package development.
Video tutorial to create a database connection with Integration Services
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.