Jumping-start SSIS Catalog procedures from SSMS Dialogs


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?

screenshot

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

Comments (0)