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

Last updated on 09th Jul 2020, Blog, Tutorials

• SSIS stands for SQL Server Integration Services. It is a powerful data integration and ETL (Extract, Transform, Load) tool provided by Microsoft as part of SQL Server.
• SSIS allows users to create, manage, and automate data workflows to extract data from various sources, transform it as needed, and load it into destination systems, such as databases or data warehouses.
• SSIS is widely used in business intelligence, data warehousing, and data migration projects to streamline data integration and improve data quality and accessibility.

Significant SSIS Features

• Visual design environment with a drag-and-drop interface.
• Comprehensive data transformation capabilities.
• Seamless integration with SQL Server ecosystem.
• Robust error handling and logging mechanisms.
• Support for parallel execution for high performance.
• Connectivity to various data sources, including databases and files.
• Scheduling and automation of data integration tasks.
• Extensibility through custom components and Visual Studio integration.

Architecture for SSIS

SSIS architectural components:

The Key Components of SSIS (SQL Server Integration Services) Are:

• Control Flow
• Data Flow
• Connection Managers
• Data Sources
• Data Transformations

Control Flow

• The Control Flow is the backbone of an SSIS package. It defines the workflow and the sequence in which tasks should be executed. Control Flow components include containers and tasks that control the flow of execution within the package.

Data Flow

• The Data Flow is responsible for moving and transforming data between sources and destinations. It consists of data flow components such as sources, transformations, and destinations that handle the ETL (Extract, Transform, Load) process.

Connection Managers

• Connection Managers are used to establish connections to various data sources and destinations. They store connection strings and credentials required to interact with external data sources like databases or files.

Tasks

• Tasks are individual units of work within the Control Flow. Each task performs a specific action, such as data extraction, data loading, or executing scripts.

Data Sources

• Data Sources define the source from which SSIS should extract data. They provide information about the data source type, location, and connection details.

Data Transformations:

• Data Transformations modify, clean, or aggregate data during the ETL process. Transformations can include sorting, merging, splitting, and applying business rules to data.

Data Destinations 

• Data Destinations specify the destination where the transformed data should be loaded. This can be a database table, a flat file, or any other supported data storage.

Event Handlers

• Event Handlers respond to specific events occurring during package execution, such as OnError or OnPostExecute. They enable customized actions based on the package’s execution status.

Variables and Parameters

• Variables and Parameters are used to store and pass values dynamically during package execution. They enable flexibility and reusability in package design.

Package Configurations

• Package Configurations allow externalizing package settings, making them configurable and reusable across different environments.

Package Deployment and Execution

• This involves deploying SSIS packages to SQL Server or SSIS Catalogs for execution. Packages can be scheduled and executed manually or automatically based on specific requirements.

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.

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

SSIS (SQL Server Integration Services) offers a diverse range of tasks that cater to different aspects of data integration and ETL (Extract, Transform, Load) processes.

• Execute SQL Task: Executes SQL commands or stored procedures in the database.
• Data Flow Task: Moves and transforms data between sources and destinations.
• File System Task: Performs file system operations like copying, moving, or deleting files.
• Execute Package Task: Executes another SSIS package within the current package.
• Script Task: Allows custom scripting using C# or VB.NET within the package.
• FTP Task: Transfers files to and from FTP servers.
• Bulk Insert Task: Loads data from flat files into a database using bulk insert.
• Send Mail Task: Sends emails from the package.
• Web Service Task: Consumes or calls web services.
• Execute Process Task: Executes external processes or applications.
• Data Profiling Task: Analyzes data to determine data quality and patterns.
• WMI Event Watcher Task: Monitors Windows Management Instrumentation (WMI) events.
• Data Mining Query Task: Executes data mining queries against a data mining model.

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.

The Positive Aspects of SSIS:

• User-friendly interface with visual design.
• Versatile support for various data sources.
• Performance optimization with parallel execution.
• Robust data transformation capabilities.
• Seamless integration with SQL Server.
• Effective error handling and logging.
• Reusability and modularity of packages.
• Extensibility with custom script tasks.

Conclusion

• Our training on SSIS (SQL Server Integration Services) has provided a comprehensive understanding of data integration and ETL processes.
• We examined the main functions, duties, and features of SSIS, developing our skills in the planning, carrying out, and controlling of data processes.
• The training has improved our capacity to manage challenging data integration scenarios by arming us with the skills to extract, convert, and load data from various sources.
• With its user-friendly interface, performance optimization, and error-handling capabilities, SSIS proves to be a powerful tool for efficient and reliable data management.
• Overall, the training has given us the ability to use SSIS efficiently in our data-driven projects and improve our abilities as data professionals or analysts.