Ssis Package Move File on Failure and Start Again
This article explains the process of configuring Error handling in SSIS package.
Introduction
In the last article, Overview of SSIS Package Logging, nosotros explored a method to log error in the package execution for different destination providers such as SQL table, XML and text files. The SSIS packages likewise known equally Excerpt Transform Load (ETL) packages. Usually, we get data from unlike sources, and nosotros integrate those data on a daily footing in SQL tables.
Suppose we get a flat file from a 3rd party daily and that file contains millions of records. Nosotros designed an SSIS bundle to import that data, and it is running fine. Occasionally, we go bad information (invalid grapheme, special character, invalid length or data blazon) in the flat files and package execution fails. It is a complicated task to place which particular row caused this outcome. We desire our SSIS packet to take intendance of this and exercise the followings:
- The SSIS package should execute successfully if all data is in order
- In case of any bad data, SSIS package should procedure all useful information, and bad data should be sent in a split file. We can open the file after and see what the bad records information technology contains. It would help us to process the rest of data and do non fail the bundle
Practical scenarios for mistake handling requirement
Let's understand this with the post-obit case. Nosotros accept a CSV file that contains sales records products. If you lot look at the following prototype, in the [Total Amount] cavalcade, it should have numeric value but records, it contains text format.
Create SSIS package for fault handling
Firstly, we create a package for mistake handling in SSIS package and come across what happens if we execute the package directly.
Allow'south open up the Visual Studio for information tools and create a new integration service project. In the control flow surface area, add a data flow task and rename it as [Acquire Mistake Treatment].
Correct-click on [Acquire Fault Handling] job and edit. It moves to data catamenia configuration page. Add together a Flat file destination.
Y'all tin notice the 3 things in the below prototype:
- Blue color pointer
- Cherry-red color arrow
- Ruby-red cantankerous on Excel source
Red Cross on Flat file source shows that nosotros need to configure earlier using it. Nosotros utilise it for fault handling in SSIS package.
Double-click on flat file source and configure a new connectedness by providing the location of the source file.
It automatically takes the configuration such as text qualifier, header row delimiter, header rows to skip. Permit's get with the default values.
Click on a cavalcade to verify the data in the source text file and available columns.
In the Advanced page, nosotros tin can change column properties such equally column names, column delimiter, data type, output column width.
Nosotros practise non want to alter whatsoever values at this point. Click OK, and it moves to data flow folio.
In the next step, add a data conversion job from the SSIS toolbox. We employ a data conversion task to modify the data type of the source column as per our requirements.
Nosotros do non see whatsoever red cross icon on the data conversion chore, but even so, it requires a configuration. Double-click on data conversion to open Data conversion transformation editor.
Select all source columns and modify the data type as per the following list:
- [Orderdate] data type should be string date[DT_DATE]
- [Region],[Proper noun],[items] information type should be string[DT_STR]
- [Units] data type should be four-byte signed integer [DT_I4]
- [Unit price] and [Total amount] data type should be currency [DT_CY]
In the following screenshot, y'all tin too run across Output alias for each column. We tin can give a proper name for each column alias or ignore it.
In the next step, add an OLE DB destination. This OLE DB destination will contain the tabular array name in which we want to insert data.
Double click on destination table, and it opens the OLE DB destination editor.
We practice non have an existing table; therefore, click on New to create a new tabular array. Information technology automatically gives the script for creating a new tabular array.
Look at the script carefully; we accept the columns with their original proper noun and alias name. Remove the original columns from the script, rename the allonym columns and specify a valid name (tabular array proper noun should be unique in a database). Nosotros do non remove the alias column because these columns contain the correct data blazon we require in the SQL tabular array.
The updated script should expect like the ane beneath.
Click OK, and it returns to OLE DB destination editor. In this window, verify the mapping between source and destination table.
In the above epitome, y'all can encounter the input and destination columns. You might think that mapping is correct, but we need to change the mapping. Nosotros use the information conversion operator, and information technology gives output column names starting with Re-create*. We require these re-create columns as input columns.
Select the columns from the driblet-down list, and you see the following configuration.
The overall SSIS bundle looks similar the following epitome.
Press F5 to execute the package and it fails at data conversion. We have not configured fault handling in SSIS packet as of at present.
Imagine a situation that the SSIS package failed in a SQL amanuensis job and now y'all need to find out the reason. If you lot have a million records in the flat file, it is a complicated task to identify the problematic row and column. You might demand to do a lot of manual tasks, in this case.
SSIS comes equally a practiced friend in this case. We can configure an mistake output. For each task, we run into 2 arrows, blueish and crimson arrow:
- Bluish pointer works for successful functioning. If the task is executed successfully, it moves the data to the next step
- The red pointer is for the error treatment in SSIS package, and we tin can redirect errors to required destinations
Drag a flat-file destination and join the crimson pointer with the flat file. It opens the following window to configure mistake output.
We can utilize all the columns listing and error configurations for error handling in SSIS package. Nosotros have following configuration available.
Failed Component
If the data flow task fails, information technology fails the packet. It is the default option.
Ignore failure
If we change the fault mode to ignore failure, it ignores the error bulletin and completes the execution. For case, in our instance, it will ignore the rows with bad data and go on working with other rows. Information technology does not throw any error message. Nosotros should not utilize information technology in the production environment.
Redirect rows
It is a useful configuration for this article. We can redirect the failed rows to a configured destination and view the bad data rows in that file. Nosotros can configure it for the truncation or the error message.
For this article, we want to configure for the error message only. Brand the changes as per the following screenshot.
Click OK and configure the flat file destination by specifying the file location. This file will go the information redirected past the error treatment in SSIS parcel.
The package configuration is complete, and it looks like the ane below.
Execute the package now, and it shows successful now. In the post-obit screenshot, you lot can note the following:
- Destination table output got 15 rows out of 18 rows bachelor in the source file
- The flat file destination (fault file) gets remaining three rows. The error treatment in SSIS parcel redirects three rows to the flat file
In the sample data shown initially, we had three wrong data rows. It matches with the output of mistake handling in SSIS package.
Let's go to the apartment file destination path and open the error file. We can see the bad data rows written into this file.
It is easy for us to wait at these wrong data rows and fix them. We do not need to go through all rows containing both skillful and bad data rows. The SSIS package execution also does not hamper, and it executes successfully with the adept information rows.
Decision
Fault handling in SSIS bundle the life of a DBA and developer more accessible by diverting the failed or incorrect information rows to a split up destination. We can quickly troubleshoot the bug in failed packages. You should take these error handlings in SSIS package.
- Author
- Recent Posts
How-do-you-do! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, gear up related issues, and Functioning Tuning with over fourteen years of experience.
I am the author of the volume "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical manufactures on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a unmarried topic, with his 50-part series on SQL Server Ever On Availability Groups.
Based on my contribution to the SQL Server community, I take been recognized equally the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (second Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Weblog: https://world wide web.dbblogger.com
I am always interested in new challenges so if you demand consulting help, reach me at rajendra.gupta16@gmail.com
View all posts past Rajendra Gupta
Source: https://www.sqlshack.com/an-overview-of-error-handling-in-ssis-package/
0 Response to "Ssis Package Move File on Failure and Start Again"
Post a Comment