Microsoft – SQL Server – Integration Services (SSIS) – Business Intelligence Studio – Dealing with data integrity errors

Background

During Data transfer operations, data compatibility mismatch will occasionally surface.

Microsoft Business Intelligence Studio has a built in tooling to address such problems.  Let us touch on one of them.

 

 

Set up Lab

Let us create our database objects.

We have a simple database structure with two tables; dbo.custList and dbo.custListDest.  The only difference between the two tables is that the “inceptionDate” can be null in the source table, but not in the destination table.

Here is what our tables look like in SQL Server Management Studio.

Design Table – dbo.custList ( Source Table )

 

dbo-custList

 

 

Design Table – dbo.custListDest ( Destination Table )

 

dbo-custListDest

 

 

Data – dbo.custList ( Source Table )

Here is what our data looks like.

Notice that using CTRL-0 we intentionally nulled out some rows’ inceptionDate column.

 

dbo-custList (data)

 

Business Intelligence Studio (BIDS)

 

Control

Here is what our BIDS Task Flowchart looks like:

 

Control

 

 

Task – Execute SQL Task

Our first task is to remove all records from the destination table, truncate table dbo.custListDest.

 

 

ExecuteSQLTask

 

Task – Data Flow Task

 

Data Flow Task

Here is our Data Flow Task

DataFlowTask

 

Thank goodness the flow is  straightforward.  It starts off with our OLE DB Source, into our OLE DB Destination, and errors are logged into the Flat File Destination.

 

OLE DB Destination – Connection Manager

 

OLEDBDestinationEditor--ConnectionManager

 

 

OLE DB Destination – Mappings

 

OLEDBDestinationEditor--Mappings

 

 

OLE DB Destination – Error Output

 

OLEDBDestinationEditor--ErrorOutput

 

OLE DB Destination Editor – Settings

 

Tab Element Value
Connection Manager
Data Access Mode Table or view fast load
Keep Identity  Yes
Keep nulls  Yes
Table lock  Off
Check Constraints Yes
 Rows per batch  Please use an optimal value based on what your environment can support
 Mappings
 Please map the columns based on your need
Error Output
 Error  Redirect Error

 

 

 

Data Flow Path Editor

Pasted below is us passing off errors unto an OLEDB Destination Output.

DataFlowPathEditor

 

Settings

DataFlowPathEditor

 

 

Flat File Destination

Flat File Destination Editor – Connection Manager

 

FlatFileDestinationEditor-ConnectionManager-Header

 

Flat File Destination Editor – Flat File Connection Manager Editor

Within the FlatFile Destination Editor, we clicked on the Edit button to customize our Error File.

FlatFileConnectionManagerEditor

 

Flat File Destination Editor – Mappings

Here are the columns that we want to capture in our error file.

FlatFileDestinationEditor-Mappings

 

Settings

 

Tab Element Value
Connection Manager
Overwrite data in the file Yes
File name Please choose an existing folder in your user folder
Unicode  Yes ( to support internationalization )
Format Delimited
Column names in the first data row Yes

 

 

 

 

 

 

Profiling

 

SQL Server Profiler

As good developers we ran SQL Server profiler and took a quick look at the SQL Traffic.  Seeing that “insert bulk” is in use, we feel comfortable about the throughput.

 

SQLServerProfiler-InsertBulk

 

 

 

Listening To

Listening to Yvonne DeVaughn sing “Healing Waters for the Soul”
https://www.youtube.com/watch?v=sgfBByJjfh8

Yvonne Devaughn is a director with AVA (Advocacy for victims of Abuse). And, AVA is an important component of the community outreach of Evangelical Covenant Church.

You can read more here – http://www.npcwm.org/what-we-do/ava/

 

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s