SSIS Cheat Sheet: Complete Guide Tutorial For Free | CHECK-OUT

Last updated on 09th Jul 2020, Blog, Tutorials

(5.0) | 18647 Ratings 3559

Share:

• 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 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 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 Architecture

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)

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.

 Types of containers in SSIS are:

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

Sequence Container:

•  allows you to organize subsidiary tasks by grouping them, and allows you to you apply transactions or assign logging to the container.

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.

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.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.

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.

Other Important ETL Tools

• SAP Data Services
• SAS Data Management
• Oracle Warehouse Builder (OWB)
• PowerCenter Informatica
• IBM Infosphere Information Server
• Elixir Repertoire for Data ETL
• Sargent Data Flow

Studio Environments In SSIS

• There are two studio environments in SSIS: SSDT and SSMS.
  • SSDT (SQL Server Data Tools):
    • It helps in developing the integration of service packages. Here are a few highlights of the tool:

• It helps in copying basic package data from the source to the destination
• When creating packages, it helps with excellent data flow and complete flow control management.
• It helps in updating the properties of packages during the run time.
• It helps in the quick deployment of packages.
• It saves the backup copy of packages for future use.

• SSMS (SQL Server Management Studio):
  • it helps in managing packages in a production environment. Here are a few highlights of the tool:

• It creates folders to organize packages.
• It helps in storing and running packages to the local computer.
• It generates a command line when we execute the package utility.
• It stores and fetches packages to and from the SQL Server msdb.

Enroll in SSIS Training & Certification Course & Get Hired By TOP MNCs

Weekday / Weekend BatchesSee Batch Details

SSIS Packages And Expressions

1. 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.
2. 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.

• 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. Here is the output for your reference:

A Range Of SSIS Tasks

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

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

Explore Curriculum

• In SSIS, a task is added to manage the control flow. Here are different types of SSIS tasks that perform a different type of work. Let us have a quick discussion on each of the SSIS tasks one by one:

1. Execute SQL Task: It executes the SQL statement against a relational database system.
2. Data Flow Task: It reads the data from one or more sources, transforms the data, and writes it out against multiple destinations.
3. Analysis Service Processing Task: It is the task to process objects of a tabular model as an SSAS
4. Execute Package Task: It is used to execute one or more packages within a single project.
5. Execute Process Task: This task is used to specify command-line parameters.
6. XML Task: As the name suggests, this task helps you to merge, format, and split an XML file.
7. Web Service Task: This task is used to execute a method on a web service.
8. Script Task: It is used to run C# and VB.net coding in a visual studio environment.
9. WMI Event Watcher Task: The task allows SSIS packages to wait and respond to certain WMI events.
10. Bulk Insert Task: it is used to load bulk data into tables using Bulk Insert command.
11. Send Email Task: it is used to send emails to notify users that your package is finished or there is some error as well.
12. FTP Task: It is used to perform basic FTP functionalities.
13. File System Task: It is used to perform manipulations on file systems  like moving, renaming, deleting, or creating directories.

SSIS Date/Time Data Types:

• DT_BOOL: It is a 1-bit Boolean Value
• DT_BYTES:  It is a binary data value and the length is variable, the maximum length is up-to 8000 bytes.
• 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
• 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
• DT_DBDATE: A date structure that consists of year, month, and day.
• DT_DBTIM (Format: hh:mm: ss): A time structure that consists of hour, minute, and second.
• 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
• 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
• 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
• 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
• 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
• 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
• DT_GUID: It is a globally unique identifier (GUID).
• DT_I1: It is a one-byte, signed integer.
• DT_I2: It is a two-byte, signed integer
• DT_I4: It is a four-byte, signed integer.
• DT_I8: It is an eight-byte, signed integer
• 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.
• DT_R4: It is a single-precision floating-point value.
• DT_R8: It is a double-precision floating-point value
• DT_STR: It is a null-terminated ANSI/MBCS character string with a maximum length of 8000 characters
• DT_UI1: It is a one-byte, unsigned integer
• DT_UI2: It is a two-byte, unsigned integer
• DT_UI4: It is a four-byte, unsigned integer.
• DT_UI8: It is an eight-byte, unsigned integer.
• DT_WSTR: It is a null-terminated Unicode character string with a maximum length of 4000 characters
• DT_IMAGE: It is a binary value with a maximum size of 2^31 -1 byte
• DT_NTEXT: It is a Unicode character string with a maximum length of 2^30-1 characters
• DT_TEXT: An ANSI character string with a maximum length of 2^31-1 characters

Benefits Of SSIS:

• It supports broad documentation in different styles.
• It is easy to use, and implementation speed is also effective.
• It is tightly integrated with Visual Studio and SQL
• It offers message-based capabilities in real-time.
• It supports the distribution model as needed.
• It helps you in removing the network as a bottleneck for the data insertion by SSIS into SQL.
• It is using the SQL Server destination for the fast transfer of the data.

 Advantages:

• Broad documentation and support
• Ease and speed of implementation
• Tight integration with SQL Server and visual studio
• Standardized data integration
• Offers real-time, message-based capabilities
• Support for distribution model
• Helps you to remove network as a bottleneck for insertion of data by SSIS into SQL
• SISS allows you to use the SQL Server Destination instead of OLE DB to load the data faster

Conclusion

• We discussed everything about integration service from basics to advanced level.
• We learned the architectural components, benefits, and drawbacks of using SSIS, best practices of using SSIS, and a practice example for creating an event handler in SSIS.
• This is how the tutorial on SSIS is written to make you understand the concepts of SSIS. These days ETL tools are high in demand, and ETL developer is a cool profile to start a career in the IT domain.
• If you want to learn about ETL tools and how can we use them at the workplace then join the SQL Server Certification course from JanBask training and master the database concepts from scratch.
• Put a request for demo class now and change your career graph right away with the right education and skills.