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 |
|
Catalog |
Set properties |
|
Folder |
Create, Delete, Set description, Set permissions |
|
Project |
Configure parameters and references*, Validate*, Delete, Set permissions |
|
Package |
Execute*, Validate* |
|
Environment |
Create, Delete, Set description, Set variables*, Set permissions |
