CODEWITHSUNDEEP
excelssis
Craig List
Craig List

Reputation: 81

How do I load an Excel file data into multiple tables using SSIS package?

I am getting order information in an Excel file in the following format and order. I need to load these into the following tables.

OrderHeader
-----------
OrderNumber
CustomerID

Customer
--------
CustomerID
CustomerFirstName
CustomerLastName

OrderDetail
-----------
Product Information

In C#, I used to load this by reading the data one row at a time and checking for ordernumber and loop through rest of the info if that remained the same as prvious row. Now I am trying to move to SSIS and was wondering if there's a better approach that i can use or should I be using script task.

OrderNumber CustomerID CustomerFirstName CustomerLastName Product ID Model No Serial No Date of Purchase
----------- ---------- ----------------- ---------------- ---------- -------- --------- ----------------
000001          A1           John              Doe           111      1x1x1x   1s1s1s     11/1/2012
000001          A1           John              Doe           112      1x1x2x   1s1s2s     11/1/2012
000001          A1           John              Doe           113      1x1x3x   1s1s3s     11/1/2012
000001          A1           John              Doe           114      1x1x4x   1s1s4s     11/1/2012
000001          A1           John              Doe           115      1x1x5x   1s1s5s     11/1/2012
000001          A1           John              Doe           116      1x1x6x   1s1s6s     11/1/2012
000002          A2           John1             Doe1          111      1x1x1x   1s1s1s     11/1/2012
000002          A2           John1             Doe1          112      1x1x2x   1s1s2s     11/1/2012
000002          A2           John1             Doe1          113      1x1x3x   1s1s3s     11/1/2012
000002          A2           John1             Doe1          114      1x1x4x   1s1s4s     11/1/2012
000002          A2           John1             Doe1          115      1x1x5x   1s1s5s     11/1/2012
000002          A2           John1             Doe1          116      1x1x6x   1s1s6s     11/1/2012

Upvotes: 0

Views: 451

Answers (1)

Data Masseur
Data Masseur

Reputation: 1193

Just use three data pumps with a SQL SELECT statement using DISTINCT and returning only the fields you need for each destination. You may also need some logic to handle data conflicts, but you weren't asking specifically about that, so I'm assuming you don't have that issue.

Hopefully your OrderDetail table includes the OrderNumber field, or you won't be getting very far.

for orderHeader feed:

Select DISTINCT OrderNumber, CustomerID from XLSsource

Upvotes: 1

Related Questions