SSIS Cheat Sheet

SSIS Cheat Sheet

If you are looking for a tool to automate maintenance of SQL Server database which can perform data integration and workflow application, for extraction, transformation, and loading of data. Then SSIS can be taken into consideration. This cheat sheet will guide you with the basic concepts which are required to get started with it. It is a handy reference sheet for the beginners and the one having knowledge about the BI tools.

SSIS is a component of the Microsoft SQL Server database which can be used to perform data migration tasks, by collecting the data from various data sources and storing it in the central location. The main function performed by SSIS is Extraction, Transformation, and Loading.

What is SSIS?

SQL Server Integration Service (SSIS) is a component of the Microsoft SQL Server database software that can be used to execute a wide range of data migration tasks. SSIS is a fast & flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc.

It makes it easy to move data from one database to another database. SSIS can extract data from a wide variety of sources like SQL Server databases, Excel files, Oracle and DB2 databases, etc.

SSIS also includes graphical tools & wizards for performing workflow functions like sending email messages, FTP operations, data sources, and destinations.

SSIS Architecture :

• 1. Extraction: It is the collection of data from various sources
• 2. Transformation: It is a different form of data collected from different sources and is converted according to the Business requirement
• 3. Loading: Data warehouse is used which contains the loaded data
• 4. Data warehouse: It captures the data from various sources for useful analysis and access
• 5. Data Warehousing: It is a collection of data accumulated which is used for assembling and managing data from various sources for better decision making in the Business

Following are components of SSIS architecture:

• Control Flow (Stores containers and Tasks)
• Data Flow (Source, Destination, Transformations)
• Event Handler (sending of messages, Emails)
• Package Explorer (Offers a single view for all in package)
• Parameters (User Interaction)

Let’s understand each component in detail:

1. Control Flow

Control flow is a brain of SSIS package. It helps you to arranges the order of execution for all its components. The components contain containers and tasks which are managed by precedence constraints.

2. Precedence Constraints

Precedence constrain are package component which direct tasks to execute in a predefined order. It also defines the workflow of the entire SSIS package. It controls the execution of the two linked tasks by executing the destination tasks based on the result of the earlier task — business rules which are defined using special expressions.

3. Task

A ‘Task’ is an individual unit of work. It is the same as a method/function used in a programming language. However, in SSIS, you don’t use coding methods. Instead, you will use drag & drop technique to design surface and to configure them.

4. Containers

The container is units for grouping tasks together into units of work. Apart from offering visual consistency, it also allows you to declare variables and event handlers which should be in the scope of that specific container.

Three types of containers in SSIS are:

• A Sequence Container
• A For Loop Container
• Foreach Loop Container

1. Sequence Container: Allows you to organize subsidiary tasks by grouping them, and allows you to you apply transactions or assign logging to the container.

2. For loop container: Provides the same functionality as the sequence Container except that it also lets you run the tasks multiple times. However, it is based on an evaluation condition, like a looping from 1 to 100.

3. For each Loop Container: It also allows looping. But the difference that instead of using a condition expression, loop s done over a set of objects, likes files in a folder.

5. Data Flow

The main use of the SSIS tool is to extract data into the server’s memory, transform it, and write it to another destination. If Control Flow is the brain, Data Flow is the heart of SSIS

6. SSIS Packages

Another core component of SSIS is the notion of a package. It is a collection of tasks which execute in an orderly fashion. Here, president constraints help manage the order in which the task will execute.

A package can help you to saves files onto a SQL Server, in the msdb or package catalog database. It can save as a .dtsx file, which is a structured file very similar to .rdl files are to Reporting Services.

Best SSIS Training to Enhance Your Career By TOP-Rated Instructors

• Instructor-led Sessions
• Real-life Case Studies
• Assignments

Explore Curriculum

7. Parameters

Parameters behave much like a variable but with a few main exceptions. It can be set outside the package easily. It can be designated as values that must be passed in for the package to start.

Features of SSIS:

1. 1. Studio environment: SSIS includes two studios
   • SSDT: Referred to as SQL Server Data Tools which is used to develop integrated service packages and perform several tasks like
   • • Copying basic package data from source to destination.
     • Updating the properties of packages while run time.
     • Creating deployment.
     • Saving the package copies to SQL Server
2. 2. SSMS: Referred to as SQL Server management studio which is used for the package management in a production environment and perform tasks such as
   • To create a folder for package management
   • Executive Package utility is used to run the packages and store on a local system
   • Generates a command line while running the executive package utility
   • To store and fetch packages from SQL Server
3. 3. Packages: The SSIS package is a collection of control flow and data flow. Control flow includes tasks and data flow task and data flow including source, transformation, and destination
4. 4. Expressions: SSIS Expressions are the combination of literals, identifiers, and operators
5. 5. Event Handling: Handling a workflow that is designed for the number of events in different ways where it might occur. It is similar to building the packages

Uses of SSIS:

• It is used to combine data from multiple data sources
• Populating data warehouses and data marts
• For Cleaning and standardizing data
• To Build BI into the data transformation process
• Used for automating administrative functions and loading the data

SSIS Date/Time Data Types:

1. 1. DT_BOOL : It is a 1-bit Boolean Value
2. 2. DT_BYTES : It is a binary data value and the length is variable, the maximum length is up-to 8000 bytes.
3. 3. DT_CY : It is a currency value and this data type is an eight-byte signed integer with a scale of 4 and a maximum precision of 19 digits
4. 4. DT_DATE (Format: yyyy-mm-dd) : It is a data structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The maximum scale of fractional seconds is 7 digits
5. 5. DT_DBDATE : A date structure that consists of year, month, and day.
6. 6. DT_DBTIM (Format: hh:mm: ss) : A time structure that consists of hour, minute, and second.
7. 7. DT_DBTIME2 (Format: hh:mm: ss[.fffffff]) : A time structure that consists of hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 7 digits
8. 8. DT_DBTIMESTAMP (Format: yyyy-mm-dd hh:mm: ss[. fff]): A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 3 digits
9. 9. DT_DBTIMESTAMP2 (Format: yyyy-mm-dd hh:mm: ss[.fffffff]) : A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 7 digits
10. 10. DT_DBTIMESTAMPOFFSET (Format: yyyy-mm-dd hh:mm: ss[.fffffff] [{+|-} hh:mm]) : A timestamp structure that consists of year, month, day, hour, minute, second, and fractional seconds. The maximum scale of fractional seconds is 7 digits
11. 11. DT_DECIMAL : An exact numeric value with a fixed precision and a fixed scale. This data type is a12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 29
12. 12. DT_FILETIME (Format: yyyy-mm-dd hh:mm:ss:fff) : A 64-bit value that represents the number of 100-nanosecond intervals maximum scale of fractional seconds is 3 digits
13. 13. DT_GUID : It is a globally unique identifier (GUID).
14. 14. DT_I1 : It is a one-byte, signed integer.
15. 15. DT_I2 : It is a two-byte, signed integer
16. 16. DT_I4 : It is a four-byte, signed integer.
17. 17. DT_I8 : It is an eight-byte, signed integer
18. 18. DT_NUMERIC : An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign.
19. 19. DT_R4 : It is a single-precision floating-point value.
20. 20. DT_R8 : It is a double-precision floating-point value
21. 21. DT_STR : It is a null-terminated ANSI/MBCS character string with a maximum length of 8000 characters
22. 22. DT_UI1 : It is a one-byte, unsigned integer
23. 23. DT_UI2 : It is a two-byte, unsigned integer
24. 24. DT_UI4 : It is a four-byte, unsigned integer.
25. 25. DT_UI8 : It is an eight-byte, unsigned integer.
26. 26. DT_WSTR : It is a null-terminated Unicode character string with a maximum length of 4000 characters
27. 27. DT_IMAGE : It is a binary value with a maximum size of 2^31 -1 byte
28. 28. DT_NTEXT : It is a Unicode character string with a maximum length of 2^30-1 characters
29. 29. DT_TEXT : An ANSI character string with a maximum length of 2^31-1 characters

Transformations available in SSIS:

• 1. Aggregate – It is used to apply aggregate functions to Record Sets to produce new output records from aggregated values.
• 2. Audit – It is used to add Package and Task Level Metadata – such as Machine Name, Execution Instance, Package Name, Package ID, etc.
• 3. Character map –It is used to Performs SQL Server operations such as changing data from lower case to upper case.
• 4. Conditional split – It is used to separate available input into separate output pipelines based on Boolean Expressions designed for each output.
• 5. Copy column – It is used to add a copy of the column to the output so that we can later transform the copy keeping the original for auditing.
• 6. Cache- It is used to write the data from connected data source to a cache file
• 7. Data conversion – It is used to convert columns data types from one type to another type. It stands for Explicit Column Conversion.
• 8. Data mining query – It is used to perform data mining queries against analysis services and to manage Predictions, Graphs, and Controls.
• 9. Derived column – Used to create a new column from given expressions.
• 10. Export column – It is used to export an Image specific column from the database to a flat-file.
• 11. Fuzzy grouping – It is used for data cleansing by finding rows that are likely duplicates.
• 12. Fuzzy lookup – It is used for Pattern Matching and Ranking based on fuzzy logic.
• 13. Import column – It reads the image specific column from the database onto a flat-file.
• 14. Lookup – It performs a search of a given reference object set against a data source and is used for exact matches only.
• 15. Merge – It is used to Merges two sorted data sets into a single data set into a single data flow.
• 16. Merge join – It is used to merge two data sets into a single dataset using a join junction.
• `7. Multicast – It sends a copy of supplied Data Source onto multiple Destinations.
• 18. Row count – It is used to store the resulting row count from the data flow/transformation into a variable.
• 19. Row sampling – It captures the sample data by using a row count of the total rows in dataflow specified by rows or percentage.
• 20. Union all – It is used to Merge multiple data sets into a single dataset.
• 21. Pivot – It is used for data source normalization by converting rows into columns
• 22. Unpivot – It is used for de-normalizing the data structure by converting columns into rows in case of building Data Warehouses.

SSIS Salient Features :

    Here, are some important SSIS features:

• Studio Environments
• Relevant data integration functions
• Effective implementation speed
• Tight integration with other Microsoft SQL family
• Data Mining Query Transformation
• Fuzzy Lookup and Grouping Transformations
• Term Extraction and Term Lookup Transformations
• Higher speed data connectivity components such as connectivity to SAP or Oracle

SSIS Packages And Expressions :

An SSIS package is a perfect combination of the data flow and control flow. Data flow includes the source, destination, and transformation. At the same time, control flow includes tasks and data flow tasks.

SSIS expression is a combination of operators, literals, and identifiers. An interpreted variable is named as the literal, and it can be divided into the following categories: Numeric Literals, String Literals, and Boolean Literals.

Create An Event Handler In SSIS

The creation of an event handler is similar to building a package in SSIS. Here are a few event handlers in SSIS that you should know:

OnError, ONexecStatusChanged, OnInformation, OnPostExecute, OnPostValidate, OnPreExecute, OnProgress, OnTaskFailed, OnVariableValue

Take the example of two excel sheets  to understand the concept. You have to translate the File 1 into the second excel file that is File 2 should be converted to a compatible format. Here are the steps to follow when creating an event handler in SSIS.

TABLE

Step 1 – Create a New Project by clicking on File -> New -> Project and select the integration services for the group. It will open the SSIS designer that can be used to create and maintain integration service packages. Also, in the SSIS Package folder, you can see the default package with the name “Package.dtsx.”

Step 2 – In the second step, you should create a connection manager for the excel sheet. Select the excel sheet and click on the add Now, select the browse button and choose the excel file path.

Step 3 – Once the connection manager is created for the excel sheet, now you should change its name. Right-click on the connection manager and rename it as a source connection manager.

Step 4 – Now repeat the same step and create one more connection manager for the resultant file. Change the name and rename it as a destination connection manager.

Step 5 – Create a Control Flow to transfer the data from the source file to the destination file. You should select the control flow in the SSIS designer. Now drag the data flow task from the toolbox to the designer. Rename the data flow task something logical here.

Step 6 – Create a Data Flow to set the flow of the data among source and destination excel file.

Step 7 – In the next step, you should create an excel source. For this purpose, choose the excel source from the source group and put it to the designer.

Step 8 – It is time to configure the excel sheet. Now double click on the excel source to make settings as per your requirements.

Step 9 – Create a derived column, connect the source to the derived column, and configure the derived column.

Step 10 – create an excel destination, connect a derived column to the excel destination, and configure the excel destination. Now execute the pages. And you can see that whole data is copied to the second excel file as expected.

SSIS Transformations :

The SSIS transformations are the data flow components that are used to perform aggregations, sorting, merging, modifying, joining, data cleansing, and distributing the data.

Apart from these, there is an important and powerful transformation in SSIS called Lookup transformation to perform lookup operations. In this article, we will show you the list of available SSIS transformations and explains their working functionality.

SSIS Transformations with Examples :

The following are a list of transformations available in SQL Server Integration Services. I suggest you click on the URL (red color links) to navigate to the required SSIS transformation and find the example with a detailed explanation.