Connection Manager in SSIS is used to connect to various data sources such as Relational Databases, Analysis Services Databases, Flat Files and files in CSV and XML formats.
A Connection Manager can be created at
In Real Life Projects,
Refer this tutorial where we have used both project/package level Connection Manager. Project level to connect to SQL Server Database and package level to import the data from a flat file.
In this post, we will see how to create a Shared Connection Manager i.e. Connection Manager at Project Level. we will create OLE DB Connection Manager to connect to SQL Server and we will use the same in various packages that we will create/created in various tutorials on the blog.
Step 1: Run SQL Server Data Tools. Create a New SSIS Project SSIS-Tutorials.
Step 2: Right Click Connection Managers in the Solution Explorer and Add New Connection Manager
This will open SSIS Connection Manager. Select OLEDB from the available sources and click Add
This will open Configure OLE DB Connection Manager Window. Click New
This will open Connection Manager window. Select SQL Server and provide Authentication to logon to SQL Server and select Database and click Ok
At this step we are done with creating the shared Connection Manager which would start appearing under Connection Managers like below.
A Connection Manager can be created at
- Project Level: Available to all the packages. That is why it is also called as shared Connection Manager.
- Package Level: Available to the specific package
In Real Life Projects,
- We create connection manager at project level when we have to connect to same data source for various packages such as OLE DB Connection Manager. We use OLE DB connection Manager to connect to SQL Server which is generally required to connect in various packages.
- We create connection manager at package level when we have to connect to a particular data source for a specific package such as Flat File Connection Manager. We use Flat File Connection Manager to import/export data to a flat file which is generally specific to a particular package.
Refer this tutorial where we have used both project/package level Connection Manager. Project level to connect to SQL Server Database and package level to import the data from a flat file.
In this post, we will see how to create a Shared Connection Manager i.e. Connection Manager at Project Level. we will create OLE DB Connection Manager to connect to SQL Server and we will use the same in various packages that we will create/created in various tutorials on the blog.
Step 1: Run SQL Server Data Tools. Create a New SSIS Project SSIS-Tutorials.
Step 2: Right Click Connection Managers in the Solution Explorer and Add New Connection Manager
This will open SSIS Connection Manager. Select OLEDB from the available sources and click Add
This will open Configure OLE DB Connection Manager Window. Click New
This will open Connection Manager window. Select SQL Server and provide Authentication to logon to SQL Server and select Database and click Ok
At this step we are done with creating the shared Connection Manager which would start appearing under Connection Managers like below.
No comments:
Write Comments