SQL Server Integration Services – Packages, Datasources and Connections


In this article we will see how we can create a SSIS project via visual studio and then create a package and linking the packages to data sources along with connections. For this demo i will be using SQL Server 2008. The steps remain same for SQL Server 2008 R2 and SQL Server 2012.

creating a SSIS project…

Step 1: Open SQL Server Business Intelligence Development Studio(BIDS). Go to File -> New -> Project
IS1

 

 

 

 

 

 

 

Step 2 : ON the new Project dialog box choose Integration Services Project. Give the Project a name and location as shown below. Click on OK. The  Project will appear on the Solution explorer. By default a package is already created named Package.dtsx
IS2IS3

Step 3: let us rename this package as MyPackage1. Right click on the Package and select rename. Change the name to MyPackage.dtsx. Click Yes on the confirmation dialog box.
IS4IS5
Step 4: We will create two more packages named DC.dtsx and DP.dtsx. Right click on SSIS package on solution explorer and click on New SSIS Package. A package would be automatically added with default name Package(n).dtsx. We will rename the package as DC.dtsx. Repeat the same steps and create another package named DP.dtsx. Click on save all button to save all the changes made.
IS6 IS7So this is how we will create a SSIS project with packages. In the next section we will create data sources for the project.

 creating data sources..

Step 1: Open the Project in BIDS. Right click on Data Sources and select New Data Sources. On the Data source wizard click on new and select a server name and database name. In this case we will choose AdventureWorks as our database.
IS8 IS9 IS10Step 2: Click on OK. Click Next and click on Finish. The new data source will now appear under Data Sources
IS11 IS12 In the next section we will create new package connections from the Data Sources we created.

Creating Connections

Step1 : Click any one of the packages. Right Click on the connection managers window and select New Connection from Data Source. Select the Data source we created name AdventureWorks. Click on OK.
IS13 IS14The new connection connection will now appear in the connection managers window for the package. We need to create the same for the rest of the two packages.
IS15

 

Advertisements

Tagged: , , , , , ,

4 thoughts on “SQL Server Integration Services – Packages, Datasources and Connections

  1. […] my previous article on SSIS we spoke about Packages,Connections and Data Sources. In this article we will talk about Control Flow Objects and how we can use […]

    Like

  2. […] Now we will implement the sequence container with a demo. In the first example we will not use TransactionOption but in the second example we will. Let us begin. We will be using the project we created in my article. […]

    Like

  3. […] implement the For loop container. I will be using the project I created in the my article. Step 1: Open the project in BIDS -> Add a Execute SQL task on the UI design surface. Insert the […]

    Like

  4. […] 1: We will be using the package we created in my article. Drag and drop a Foreach loop container in the design UI surface -> Drag and drop a File system […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: