SQL Server Integration Services: SQL Server 2005's New ETL Platform
Navigate to the Component Properties tab and specify the stored procedure details in the SqlCommand properties text box as Figure 6 shows.
Figure 6: Stored Procedure Details in the SqlCommand Properties Text Box
As you can see from Figure 6, the InsertEmp procedure is used to load the data into the Emp table and the parameters to that procedure will be supplied at run time. The mapping between the source table columns and the stored procedure parameters is what you will specify in the Column Mapping tab. Note that in the Component Properties tab; once you specify the SqlCommand property value and hit the Refresh button, the title of the Input Columns tab in the Figure 6 dialog box automatically changes to Column Mappings.
In the Column Mappings tab, you can either drag a column (between the input columns and destination columns) or you can use the dropdown list to select the corresponding input and output columns. Once you perform the mappings, a window like Figure 7 appears.
Figure 7: The Resulting Mappings from Column Mappings Tab
Click OK in the Column Mappings dialog box. That's all there is to creating a package with the new package designer. Now, you are ready to execute the package. Just press F5, and you will see the output in Figure 8.
Figure 8: Output of a Successful Package Execution
If the package executes successfully, you will see an output that is somewhat similar to Figure 8, which shows the objects on a green background. The above screenshot also shows the number of rows transferred between the source and destination tables.
A Solid Foundation for Creating Packages
With the release of SQL Server Integration Services, Microsoft now has a powerful ETL tool that is not only enterprise class but can also go a long way in increasing the productivity of developers. Its feature set makes it extremely easy and seamless to build sophisticated, high-performance ETL applications. This article has only scratched the surface of what is possible with SSIS, but the simple example it gave should provide a solid foundation for creating packages with SSIS. For more information, check out the following Web sites:
Download the Code
To download the accompanying source code for the demo, click here.
About the Author
Thiru Thangarathinam has six years of experience in architecting, designing, developing, and implementing applications using object-oriented application development methodologies. He also possesses a thorough understanding of the software life cycle (design, development, and testing). He holds several certifications, including MCAD for .NET, MCSD, and MCP. Thiru is an expert with ASP.NET, .NET Framework, Visual C# .NET, Visual Basic .NET, ADO.NET, XML Web services, and .NET Remoting. Thiru also has authored numerous books and articles. Contact him at email@example.com.
Page 3 of 3