Helpful information and examples on how to use SQL Server Integration Services.
Today’s post is from Ke Yang – a developer on the SSIS team based in Shanghai.
At times, we need T-SQL scripts to do batch jobs or to communicate with others. Since IS Catalog provides rich functionalities with over 40 stored procedures and functions, it can take you a while to learn how to use them and write your scripts. Luckily, you can easily see examples of how the T-SQL API functions by using the “Script” button on the SSMS dialogs.
Below screenshot shows the example of getting scripts from Execute Package dialog. After you finish the settings through the UI, you click “Script” and obtain the whole batch of runnable scripts including [create_execution], [set_execution_parameter_value], [start_execution]. Quite a jump start, isn’t it?
Below lists the operations that scripts are provided from dialogs for each type of SSIS object. I personally find the ones with “*” marks particularly helpful, because their scripts can be long and complex.
SSIS Object Type
Operations that scripts are provided from dialogs
Create, Delete, Set description, Set permissions
Configure parameters and references*, Validate*, Delete, Set permissions
Create, Delete, Set description, Set variables*, Set permissions