Introduction of SQL Server Integration Services (SSIS)

SQLServerF1

Microsoft SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. Integration Services solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. These packages can work alone or together with other packages to address all business needs. Integration Services can be used to perform extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

SQL Server Integration Services was introduced starting with SQL Server 2005, prior to that Data Transformation Services (DTS) was available. SQL Server Integration Services (SSIS) is mainly used to perform ETL operations which include Extract, Transform and Load.  ETL processing is commonly used for data warehousing (DW) applications, however it is not just limited to Data Warehouse applications, but can be used to satisfy various business solutions. We can use SQL Server Business Intelligence studio or Visual Studio to build the Sql Server Integration Services package and once developed and build, we can import it to any SQL Server using SQL Server Management Studio (SSMS) and use it through SQL Server jobs to execute regularly.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

Integration Services can connect to a wide variety of data sources, including multiple sources in a single package. A package can connect to relational databases by using .NET and OLE DB providers, and to many legacy databases by using ODBC drivers. It can also connect to flat files, Excel files, and Analysis Services projects. Integration Services includes source components that perform the work of extracting data from flat files, Excel spreadsheets, XML documents, and tables and views in relational databases from the data source to which the package connects. The data can be then transformed by using the transformations that Integration Services provides. The data can be transformed to compatible formats which can be merged physically into one dataset. After the data is merged successfully and transformations are applied to data, the data is usually loaded into one or more destinations. Integration Services includes destination for loading data into flat files, raw files, and relational databases. The data can also be loaded into an in-memory recordset and accessed by other package elements.

Some of the uses of SQL Server Integration Services include populating data into data warehouses and data marts, cleaning and standardizing the data, Building Business Intelligence into a Data Transformation Process, and Automating Administrative Functions and Data Loading, etc.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

SQL Server Agent SSIS Job Fails with TIMEOUT EXPIRED Error

SQLServerF1

SQL Server Integration Services (SSIS) packages can be created and used to perform various tasks related to ETL. Once the package is built, it can be reused or scheduled in a SQL Server Agent job so that it runs regularly to perform the task regularly based on the defined schedule.

SQL Server Agent allows running SSIS packages using the Integration Services Package as the job step type. Most often or not the SSIS packages runs fine through SQL Server Agent jobs, however sometimes it may fail due to various error. One of the common error SSIS package job may fail is due to TIMEOUT. Below is the error that you may see in the job history.

Code: 0xC0047062 Source: DTSTask_DTSDataPumpTask_1 ADO NET Source
Description: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper) End Error

This error can occur due to command timeout. SSIS package internally converts the task into SQL queries and sends the queries to run against the SQL Server instance. By default the timeout value will be 30 seconds. I a query does not complete by 30 seconds, then it will fail with TIMEOUT error.

To resolve this issue we will need to identify the queries which are taking more than 30 seconds. One way to find this is using SQL Server Profiler or Extended Events. Once the offending query is identified, we need to find options to tune this query.

If the package is related to vendor application or if it is acceptable for the query to take more time to complete, then you can consider increasing the timeout value from 30 seconds to higher value so that the query can take upto the number of seconds specified.

In this case for the above mentioned error, it was using ADO.NET connection, so in ADO.NET source task, click on the Properties and set the CommandTimeout from 30 to higher value or to zero (0) which indictes that the query can take any longer until it completes of fails.

What is SQL Server Integration Services?
Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

This is applicable on below versions of SQL Server

SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.