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
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
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.
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.
So 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.
Step 2: Click on OK. Click Next and click on Finish. The new data source will now appear under Data Sources
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.
The 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.
Tagged: Business intelligence, Data Sources, Integration Services, MSSQL Server, Packages, SQL Server, SSIS
Applied SQL... 
[…] 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 […]
LikeLike
[…] 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. […]
LikeLike
[…] 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 […]
LikeLike
[…] 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 […]
LikeLike