copying excel source data with SSIS

Question

Good Day,

I do have 4 Excel Data Sources, named as "E1","E2","E3","E4" respectively.Now,  i need to copy all the Excel source data into one Excel sheet(Destination).condition is, in  the Destination Excel Sheet, the data must be in " Source Order " like...First "E1" after "E1" then "E2",....

Example:-

       Destination table must be like this.

                    E1
                       ------------
                       ----------
                      ---------------
                    E2
                        --------------
                        --------------
                        -------------
                    E3
                        -------------
                        -------------
                        ---------------
                     E4
                          ---------------
                          --------------
                          ---------------

 Thank you.


Regards
Raghu

Answer 1

Hi Raghu,
if the metadata for all 4 excel file are same then use the for each loop container for the same and DFT inside the FEL.

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 2

Good Day,

I do have 4 Excel Data Sources, named as "E1","E2","E3","E4" respectively.Now,  i need to copy all the Excel source data into one Excel sheet(Destination).condition is, in  the Destination Excel Sheet, the data must be in " Source Order " like...First "E1" after "E1" then "E2",....

Example:-

       Destination table must be like this.

                    E1
                       ------------
                       ----------
                      ---------------
                    E2
                        --------------
                        --------------
                        -------------
                    E3
                        -------------
                        -------------
                        ---------------
                     E4
                          ---------------
                          --------------
                          ---------------

 Thank you.


Regards
Raghu

Hi Raghu,
You could use ETL's suggestion or, if the columns are the same you can use a union all transform to the 4 XL source and then send to the Excel destination.

---

Hope this helps !!
Sudeep   |    My Blog

Answer 3

Good Day,

Could you please explain me bit clear.

Regards
Raghu

Answer 4

Good Day,

Could you please explain me bit clear.

Regards
Raghu

This question is for whom?

---

Hope this helps !!
Sudeep   |    My Blog

Answer 5

Good Day Sudeep,

Yes, the columns are same.

Regards
Raghu

Answer 6

Good Day Sudeep,

I tried by using "UnionAll" transformation, ofcourse it is copied all the excel source data into Destination excel sheet.
i need , at the destination level, data must be in " Source Order " ( Like , i showed in my post).

Thank you.

Regards
Raghu

Answer 7

Hi Raghu,
Do you know what is the order of the source (excel workbooks)?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 8

Hi.

please check the following link 

http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx

this may help you. you just need to simulate with your case.

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 9

Good Day Nitesh,

In the Destination Excel Sheet the data must be like this,


                    E1
                       ------------
                       ----------
                      ---------------
                    E2
                        --------------
                        --------------
                        -------------
                    E3
                        -------------
                        -------------
                        ---------------
                     E4
                          ---------------
                          --------------
                          ---------------


Regards
Raghu

Answer 10

Good Day Nitesh,

"Do you know what is the order of the source (excel workbooks)?"

Yes, source is Excel Sheets

Answer 11

Good Day Sudeep,

I tried by using "UnionAll" transformation, ofcourse it is copied all the excel source data into Destination excel sheet.
i need , at the destination level, data must be in " Source Order " ( Like , i showed in my post).

Thank you.

Regards
Raghu

Hi Raghu,
If that is the case you should Try ETL approach that he suggested in his first post

Hi Raghu,
if the metadata for all 4 excel file are same then use the for each loop container for the same and DFT inside the FEL.

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Set up a foreach loop for the 4 files.
Put a DFT in the for each loop.
In the DFT have one excel source whose connectionmanagers' conn string string should be dynamic.(use expressions)
Use the excel destination to dump the data destination

Check my blog which uses dynamic flat file you could modify it as per ur need.

---

Hope this helps !!
Sudeep   |    My Blog

Answer 12

Hi Raghu,
if the metadata for all 4 excel file are same then use the for each loop container for the same and DFT inside the FEL.

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Please check the following blog for the flat file "http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx"

Thanks-

---

Let us TRY this | Don’t forget to mark the post(s) that answered your question

Answer 13

Good Day,

Thank you.

Regards
Raghu

Answer 14

Good Day Sudeep,

You explained very clearly in your blog.Many Thanks.

Regards
Raghu

Answer 15

Good Day,

Can anybody provide me "Data Profiling Task" sample package, and links.

Thank you.

Regards
Raghu

Answer 16

Hi Raghu,
Please check these links for SSIS 2008 Data Profiling Task
http://www.simple-talk.com/sql/ssis/sql-server-2008--ssis-data-profiling-task/
http://consultingblogs.emc.com/jamiethomson/archive/2008/03/02/ssis-data-profiling-task-part-1-introduction.aspx

---

Nitesh Rai- Please mark the post as answered if it answers your question