Execution Patterns - SQL Server Integration Services Design Patterns - page 69

ApplicationName, String data type , with a default value of testSSISApp . Set the
Required property to True .
Add an Execute SQL task to the control flow and rename it Get Packages . Open
the editor and set the ConnectionType property to ADO.NET . In the Connection prop-
erty drop-down, select (or create a connection to) the SSISDB database. In the
SQLStatement property, enter custom.GetApplicationPackages . Set the
IsQueryStoredProcedure property to True . Change the ResultSet property to
Full result set .
Navigate to the Parameter Mapping page and click the Add button. Click the Vari-
able Name drop-down and select $Package::ApplicationName at the very top of the
list. Change the Data Type to String and the Parameter Name to ApplicationName .
This maps the value in the parent package parameters into the ApplicationName
parameter sent to the custom.GetApplicationPackages stored procedure
when it is called by the Execute SQL task.
Navigate to the Result Set page and click the Add button. If the Add button is dis-
abled, you did not change the ResultSet property on the General page from the default
setting (None). If ResultSet is set to any other setting, the Add button is enabled. Enter
0 for the Result Name. In the Variable Name drop-down, create a variable named
Packages . For this variable, set the Value Type property to Object .
Note Object is an interesting data type. Akin to a variant, Object can contain a
scalar like a date or integer. It can also hold a collection or string array. In this example,
Object will contain an ADO.Net Dataset value. If we had set the ConnectionType
property to OLEDB (the default), this result set variable would be populated with an
ADO Recordset. Yes, that is a COM object—in 2014. COM (and COBOL) will never
die....
Let's review. First, the task will use an ADO.NET connection to the SSISDB data-
base to execute the custom.GetApplicationPackages stored procedure we
created earlier. Because we set the IsQueryStoredProcedure to True , we do
not need to add placeholders for parameters or the exec command. Since we used
ADO.NET, we can address parameters by name instead of ordinal ( ApplicationName
instead of 0 ) on the Parameter Mapping page. Finally, we configured the Execute SQL
task to push the results of the stored procedure execution into an object variable named
Packages .