SSIS HELP

{TOC}

IMPORTANT FOR DATA MASKING SSIS FEATURES

Sources

Sources in SSIS are components that allow to connect to a data source, be it a database, a file, or a service.

Files

TEXT

Databases

SSIS connect to any database that support the following drivers: ODBC, OLE DB, ADO.NET, ADO, and SQL Server Native Driver. Relational Data Management Systems with such connectivity include well known vendors ( Oracle's Oracle and MySQL databases, Microsoft's SQL Server, Access, and SQL Server in Azure, Sybase, IBM's database varieties, and not so well known such as Postgres, Ingres, SQL Lite, etc.)

Services

TEXT

Components Editor

TEXT

Destinations

TEXT

Files

TEXT

Databases

TEXT

Services

TEXT

Variables and Parameters

SSIS allows to create variables and parameters of different data types to hold values used in between tasks, environmental values, and values used in dynamically created statements. Variable and parameters differ in their scope definition and corresponding design and run-time use.

Parameters

SSIS 2012 introduced the notion of the "project-level scope" into the development of SSIS solutions. With this notion, it enabled the developers to use project scope variables called "parameters", share connection managers across packages and deploy the whole project as a whole.

Parameters differ from variables. They can be either project level or package level in scope.

Project level variables can hold value between execution of different packages or during an execution of the same package - and this value cannot change during package execution. They are also often used in run-time to hold values pertaining to specific environment. The value could be set either at development time, or during the run time and are very useful in deployments. Here is how the very same parameter looks in Integration Services Catalogs Configuration Manager:

These configurations allow changing parameters values based on different environments.

Also, very useful feature of the parameter is its ability to pass value from the parent to the child package.

Parameters, however, allow less data types than variables. Parameters do not support the following data types: Char, DBNull, Object.

Variables

In 2008, there are only variables. The SSIS 2008 does not have project model, and as such variables are the only way to introduce temporary storage. Starting with 2012, SSIS introduces the project model used in deployment and for shared connections, and parameters start playing a role of project or package level variable. Variables still exist and can be scoped at the package, container, task or event handler level.

The variables are used in the scenarios when their value should change within the scope of the package, container, task execution or event change. An example of their use is in loops, conditional or dynamic expressions.

Here are the examples of variable declarations:

Variables window may be opened many ways, with the most common two being right-clicking in the control-flow or data-flow area and choosing "Variables" or by choosing "View" -."Other Windows" ->"Variables" from the Visual Studio Menu pane.

Variables can be of many different data types, among the integers, string, datetime, object, DBNull and more.

Usually, in defining variable one would provide the scope followed by double colon and variable name User::variable_name

Each package be default has system variables defined and defaulted to the package attribute value:

You can see some good examples of them when you create and open "Derived Column Transformation Editor". Other components and tasks that make a good use of the variables are all the transformation editor components, as well as components using expression builder, such as "Expression Task" component, "For Loop Container" and the rest of the loops, in Data Flow tab it would be "Conditional Split", "Derived Column", and any component which attributes can be assigned dynamically and as such use Property Expression Editor. Such are connections, "FTP Task", or "Send Mail Task".