SQL Server 2005 Integration Services (SSIS) is a powerful and flexible data-related development platform. In this article, we will discuss three important concepts in SSIS that are often used together: the Execute SQL Task Resultset property, variables of the Object data type, and Foreach Loop Containers.
The Execute SQL Task Resultset Property
The Execute SQL Task in SSIS provides various functionalities for executing SQL statements. However, many developers are not fully aware of its capabilities. One powerful feature is the ability to execute SQL Server stored procedures using the ADO.Net ConnectionType. This can significantly improve performance compared to using the OLE DB Exec T-SQL statement.
To configure the Execute SQL Task for a stored procedure call, set the ConnectionType property to ADO.Net and the IsQueryStoredProcedure property to True. Enter the stored procedure name in the SQLStatement property and configure the parameters for the stored procedure. This method has been shown to be twice as fast as using the OLE DB Exec T-SQL statement.
Storing Results in Object Variables
When executing a SQL statement using the Execute SQL Task, you may sometimes get the results of a SELECT statement. SSIS allows you to store these results in a variable of the Object data type. This can be useful for further processing or analysis.
To store the results in an Object variable, configure the Resultset property of the Execute SQL Task to “Full result set”. This option returns an ADO.Net dataset object, which can be stored in an Object variable. You can then access and manipulate the dataset using SSIS variables.
Using Foreach Loop Containers
Shredding the data from an Object variable is a common task in SSIS. The Foreach Loop Container is a useful tool for iterating through the values in an Object variable. It allows you to process each value individually and perform specific actions based on the data.
To use the Foreach Loop Container, select the appropriate enumerator based on your data source. In this example, we used the Foreach ADO Enumerator to iterate through the rows in an ADO.Net dataset. You can then map the values to variables and perform actions within the loop.
Conclusion
SQL Server Integration Services (SSIS) provides a flexible and powerful platform for data-related development. By understanding and utilizing concepts such as the Execute SQL Task Resultset property, Object variables, and Foreach Loop Containers, you can enhance the functionality and performance of your SSIS packages.