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.
Applied SQL... 