SSIS User Handbook
If you are looking for a tool to automate the maintenance of the SQL Server database, which can perform data integration and workflow application, for the extraction, transformation, and loading of data, then SSIS can be taken into consideration. This cheat sheet will guide you through the basic concepts that are required to get started with it. It is a handy reference sheet for beginners and those who are familiar with various BI tools.
In this MSBI tutorial, we will be covering the following aspects of the SSIS user handbook:
- SQL Server Integration Server (SSIS)
- Features of SSIS
- Uses of SSIS
- SSIS Date/Time Data Types
- Data Warehouse
- Data Warehousing
- Transformations Available in SSIS
- SSIS Expressions
You can also download the printable PDF of this SSIS cheat sheet.
SQL Server Integration Service (SSIS):
SSIS is a component of the Microsoft SQL Server database that can be used to perform data migration tasks, by collecting data from various data sources and storing it in the central location. The main functions performed by SSIS are the extraction, transformation, and loading of the data.
SSIS Architecture
- Extraction: It is the collection of data from various sources.
- Transformation: It is converting the data in different forms collected from different sources according to the business requirement.
- Loading: Then, the data is loaded to a data warehouse.
- Data warehouse: It captures the data from various sources for useful analysis and access.
- Data Warehousing: It is the data storage system used for assembling and managing data from various sources for better decision-making.
Watch this SSIS Tutorial Full Course for Beginners video
Features of SSIS
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
- 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
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
Expressions: SSIS Expressions are the combination of literals, identifiers, and operators
Event handling: Handling a workflow that is designed for the number of events in different ways where it might occur and similar to building the packages.
Have a look at the top 7 MSBI advantages over other BI tools and clear your doubts related to it!
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 the 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 a 12-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
Get 100% Hike!
Master Most in Demand Skills Now !
Data Warehouse
A data warehouse is a collection of business data streaming in from different sources used by a business or enterprise for data analytics and decision-making. It is a single source of information where a company stores its data related to customers, sales, employees, etc.
The raw data is fetched from various sources and stored in the layer called the staging layer. From there it goes to the warehouse, where controlled access is given to various users with the help of SQL commands.
The key features of a data warehouse are as follows:
- Subject-oriented: This allows an organization to build a warehouse based on the type of information it wants to extract and analyze.
- Time-variant: Data is maintained through different time intervals, such as weekly, monthly, quarterly, etc.
- Integration: Organizations consistently add and connect data from diverse sources and arrange it uniformly.
- Non-volatile: The data only flows inside the warehouse. Once stored there, it cannot be changed or deleted by anyone.
Data Warehousing
Data Warehousing is the process of constructing and managing a data warehouse to collect data from various sources and provide business insights. It is carried out by transforming the normal transaction processes in the integration layer and storing the data in the data vault in the second normalized form (2NF).
From the data vault, separate entities called ‘data marts’ can be created for an individual user, having specific queries for the database. To create a data warehouse, organizations have to follow the ETL process, where ETL stands for extraction, transformation, and loading the data into the warehouse.
- Extraction: Getting the data from the original database into the stage layer
- Transformation: Transforming the data in a consistent format, e.g., transforming the profit made in different countries into a single currency type
- Loading: Once the data is transformed, it will be loaded to the data warehouse.
Kick-start your career in MSBI with our MSBI Certification now!
Transformations available in SSIS:
- Aggregate – It is used to apply aggregate functions to Record Sets to produce new output records from aggregated values.
- Audit – It is used to add Package and Task Level Metadata – such as Machine Name, Execution Instance, Package Name, Package ID, etc.
- Character map – It is used to perform SQL Server operations such as changing data from lower case to upper case.
- Conditional split – It is used to separate available input into separate output pipelines based on Boolean Expressions designed for each output.
- 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.
- Cache: It is used to write the data from a connected data source to a cache file
- Data conversion – It is used to convert columns data types from one type to another type. It stands for Explicit Column Conversion.
- Data mining query – It is used to perform data mining queries against analysis services and to manage Predictions, Graphs, and Controls.
- Derived column – Used to create a new column from given expressions.
- Export column – It is used to export an Image specific column from the database to a flat file.
- Fuzzy grouping – It is used for data cleansing by finding rows that are likely duplicates.
- Fuzzy lookup – It is used for Pattern Matching and Ranking based on fuzzy logic.
- Import column – It reads the image-specific column from the database onto a flat-file.
- Lookup – It performs a search of a given reference object set against a data source and is used for exact matches only.
- Merge – It is used to Merge two sorted data sets into a single data set into a single data flow.
- Merge join – It is used to merge two data sets into a single dataset using a join junction.
- Multicast – It sends a copy of supplied Data Source onto multiple Destinations.
- Row count – It is used to store the resulting row count from the data flow/transformation into a variable.
- Row sampling – It captures the sample data by using a row count of the total rows in dataflow specified by rows or percentage.
- Union all – It is used to Merge multiple data sets into a single dataset.
- Pivot – It is used for data source normalization by converting rows into columns
Get ready for the high-paying MSBI jobs with these top MSBI Interview Questions and answers!
SSIS Expressions:
Some common expressions used to interact with your data with SSIS packages are given in the below table:
| Statement |
Expression |
| Create a file name with today’s date |
The expression on the Flat File or File Connection Manager:
“C:\\Project\\MyExtract”+(DT_WSTR,30)(DT_DBDATE)GETDATE() + “.csv”
Expression Output Example: C:\Project\MyExtract2009-03-20.csv |
| Use a two-digit date |
RIGHT(“0” + (DT_WSTR, 2)MONTH(GETDATE()),2)
Expression Output: 03 (if the month is March) |
| Multiple conditions if statement |
In this example, the statement determines that if the ColumnName is blank or NULL, it will be set to unknown. To make a Logical AND condition, use “&&” instead of the “||” operator.
ISNULL(ColumnName)||TRIM(ColumnName)==””?”Unknown”: ColumnName |
| Returns the first five characters from a zip code |
Derived Column Transform in the Data Flow:
SUBSTRING(ZipCodePlus4,1,5) |
| Remove a given character from a string |
Derived Column Transform in the Data Flow:
REPLACE(SocialSecurityNumber, “-“,””) |
| Uppercase data |
Derived Column Transfrom in the Data Flow:
UPPER(ColumnName) |
| Replace NULL with another value |
Derived Column Transform in the Data flow:
ISNULL(ColumnName)?”New Value”: ColumnName |
| Replace blanks with NULL values |
Derived Column Transform in the Data Flow:
TRIM(ColumnName)==”” ? (DT_STR, 4, 1252)NULL(DT_STR, 4, 1252) : ColumnName |
| Remove any non-numeric data from a column |
Script Transform in the Data Flow Task with the code as follows:
Imports System.Text.RegularExpressions
Public Overrides Sub Input()_ProcessInputRows(ByVal Row As Input()Buffer)
If Row.ColumnName_IsNull = False Or Row.ColumnName = “” Then
Dim pattern As String = String.Empty
Dim r As Regex = Nothing
pattern = “[^0-9]”
r = New Regex(pattern, RegexOptions.Compiled)
Row.ColumnName = Regex.Replace(Row.ColumnName, pattern, “”)
End If
End Sub |
| Convert text to proper case |
Script Transform with the line of partial code as follows:
Row.OutputName = StrConv(Row.InputName, VBStrConv.ProperCase) |
| Build dynamic SQL statement |
Expression on the SQLStatementSource property of Execute SQL Task(VB 2008):
“SELECT Column From ” + @[User::TableName] +WHERE
DateFilterColumn = ‘” + (DT_WSTR,4)YEAR(@
[User::DateTimeVar]) + RIGHT(“0” + (DT_WSTR,2)MONTH(@
[User::DateTimeVar]),2) + RIGHT(“0” + (DT_WSTR,2)DAY(@
[User::DateTimeVar]),2) + “‘” |
| Calculate the beginning of the previous month |
Expression on component or task:
(DT_DATE)(DT_DBDATE)DATEADD
(“dd”,-1 * (DAY( GETDATE() )-1), DATEADD(“month”,-1, GETDATE() )) |
| Round to the nearest two-decimal mark |
The expression on Derived Column Transform:
ROUND(YourNumber, 2)
Expression Output Example: 1.2600000 |
Uses of SSIS
- To combine data from multiple data sources
- For populating data warehouses and data marts
- For cleaning and standardizing the data
- To integrate BI with the data transformation process
- For automating administrative functions and loading the data
Download a Printable PDF of this Cheat Sheet
Conclusion
With this, we come to the end of the SSIS cheat sheet. To get in-depth knowledge, check out our interactive, live-online SSIS Certification Training here, which comes with 24/7 support to guide you throughout your learning period. Intellipaat’s SSIS online training will make you proficient in gathering data from flat files, XML, and relational data sources. It involves working on data extraction, integration, and transformation to align with business needs.
For more information on SSIS, visit our Business Intelligence Community!