SQL Server Integration Services: SQL Server 2005's New ETL Platform
Creating a Simple Package
Consider a simple example wherein you transfer data from one table to another table by executing a stored procedure. For each row in the source table, you will invoke the stored procedure to load the data into the destination table. (Click here to download the accompanying source code for the demo.)
Before looking at the package design, create the required tables and the stored procedure. You will create these database objects in the AdventureWorks database, which is one of the sample databases that ships with SQL Server 2005.
CREATE TABLE [dbo].[EmpName]( [FirstName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL ) ON [PRIMARY]
The destination table definition is as follows:
CREATE TABLE [dbo].[Emp]( [EmpID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [varchar](100) NOT NULL ) ON [PRIMARY]
The stored procedure to load the data into the destination table is as follows:
CREATE PROCEDURE [dbo].[InsertEmp] @FirstName [varchar](50), @LastName [varchar](50) WITH EXECUTE AS CALLER AS Insert into Emp(EmpName) Values (@LastName + ', ' + @FirstName) GO
Using the package, you will retrieve the FirstName and LastName columns from the EmpName table and load them into the Emp Table through the InsertEmp stored procedure. Inside the stored procedure, you will concatenate the FirstName and LastName columns and assign the concatenated value to the EmpName column of the Emp table. Now that you understand the package functionality, take a look at the design of the package, as shown in the next section.
Creating a Package Using Business Intelligence Studio
Before creating the package, you first need to create a project that can host the package. Open Business Intelligence Development Studio from the Start->Programs menu. Select File->New Project from the menu and you will see the dialog box in Figure 1.
Figure 1: New Project Dialog Box
As Figure 1 shows, select Integration Services Project as the project template and specify the name of the project as SSISDemo. Once the project is created, you will see the package designer window of the default package Package.dtsx.
For the requirements you've outlined, the package you are going to create is very simple and straightforward. It consists of one Data Flow task with two components inside the data flow: an OLE DB Source adapter and an OLE DB Command Transformation. To start, bring up the toolbox by selecting View->Toolbox from the menu. You will see the dialog box in Figure 2.
Figure 2: Toolbox Dialog Box
As you can see, the toolbox offers a number of options that you can leverage to construct your packages. Drag and drop a Data Flow Task from the toolbar onto the package designer. Next, double-click on the Data Flow Task and you will see the designer window in Figure 3, which you can use to build sophisticated data flows.
Figure 3: Data Flow Task Designer Window
Now, bring up the toolbox again by selecting View->Toolbox from the menu. From the toolbox, drag and drop an OLE DB Source onto the designer and double-click on the object to bring up the window in Figure 4. The window allows you to specify the data source as well as the name of the source table to use for transformation.
Figure 4: Designer View of OLE DB Source
Click on OK in the Figure 4 dialog box.
Now, drag and drop an OLE DB Command object onto the designer and connect the OLE DB Source object to the OLE DB Command object. Double-click on the OLE DB Command object to bring up its properties window. Modify the Connection Managers tab of the properties window to look like Figure 5.
Figure 5: Connection Managers Tab of the OLE DB Command Object's Properties Window
Page 2 of 3