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


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 )





Design Table – dbo.custListDest ( Destination Table )





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)



Here is what our BIDS Task Flowchart looks like:





Task – Execute SQL Task

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





Task – Data Flow Task


Data Flow Task

Here is our Data Flow Task



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





OLE DB Destination – Mappings





OLE DB Destination – Error Output




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
 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.







Flat File Destination

Flat File Destination Editor – Connection Manager




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.



Flat File Destination Editor – Mappings

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





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









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.






Listening To

Listening to Yvonne DeVaughn sing “Healing Waters for the Soul”

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 –





Leave a Reply

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

You are commenting using your 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