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.
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
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.
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 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 – http://www.npcwm.org/what-we-do/ava/