Few Months back, we were working on a task with respect to OLEDB Source and Destination. We extracted data from Heterogeneous Source and dumped the data into SQL Server Database tables. Everything was going fine. Few days later, we added a Composite Key for the table columns, which was then deployed in the production environment.

After a while, we noticed that few rows [Almost 20 Million Rows :-) ] were missing from the box and so we decided to analyze the reasons behind it. We were too curious as to what was happening in the database after applying the Composite Keys. So we took a sample data as shown below and went on doing an RND as follows.

Create a table with few columns called as Source Customer — sCustomer with 3 Columns with the data types as Varchar (10)

Create table sCustomer
(
CustomerID Varchar (10),
CustomerName Varchar (10),
CustomerAddress Varchar (10)
)

Then insert data with some duplicate data into sCustomer

Insert sCustomer
SELECT   1 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   1 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   1 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   2 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   3 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   4 AS CustomerID, 'B' AS CustomerName, 'Chennai’ AS CustomerAddress
UNION ALL
SELECT   5 AS CustomerID, 'B' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   6 AS CustomerID, 'F' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   1 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   2 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   3 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   4 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   5 AS CustomerID, 'E' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   6 AS CustomerID, 'E' AS CustomerName, 'Chennai' AS CustomerAddress
UNION ALL
SELECT   7 AS CustomerID, 'A' AS CustomerName, 'Chennai' AS CustomerAddress

After Inserting Data, the sCustomer table shows it has duplicate data

 

 

Using SSIS, Create the package with a simple Dataflow task containing OLEDB Source and OLEDB Destination

Open the OLEDB Source and add the respective table

Select the required Columns and Click OK. Create the required Destination table with the Composite Key as shown below

Double click the Destination and Set the Commit Size of data to 15 since there are only 15 rows in my Source Customer table. It depends on the Number of rows.

Connect the Error Output to Redirect Rows on Error as Shown

Run the Package

While running the package there are only 4 to 5 rows as duplicate, but all the 15 rows are moved into Destination Error as shown above, Stop the package and again set the Commit size to 1 as shown below.

Run the Package again. Before running the package, truncate both the dCustomer and Error Log table.

After setting the Commit size to 1, the data ran into the table properly. Since the Transaction occurs as a batch, when one record fails, all the records are moved into Error table.

Solution 1: Use the LOOK UP table, check for the conditions with AND Cases

E.g. CustomerID != CustomerID AND CustomerName != CustomerName then do the Insert based on NO MATCH

Solution 2: What happens if it Truncate Load

Instead of using the “FastLoad” Option present in the OLEDB Destination, we can simply use the “Table Or View” Option to load the Data while you apply Composite Key or Constraint.

After using the “Table or View” Option in OLEDB Destination, the Process ran properly.

We hope you find this article useful. We are a Microsoft gold certified partner providing offshore software development on Microsoft technologies (.NET, SQL), SharePoint, Microsoft Business Intelligence, Dynamics ERP & CRM and Mobile application development on iOS, Windows and Android platforms.

Please do contact us if you have any questions pertaining to this article or any other queries that you might have relating to our services.

Tags: Microsoft BI | Comments Off

previous post: Role Center in Dynamics SL 2011 next post: Maps for Application

Comments are closed.

Archives

2016
2015
2014
2013
2012
Congruent Facebook Twitter Slideshare