exporting data from excel sheets to one table

[Kingkumar]

Hi,
I'm new to DTS packages and what i need to do is to migrate data from 10 excel sheets of the same workbook into a single table .
Now the question i have is that whether i need to create 10 different transform data task to achieve this or there's a simple way
Also if i have to do same sheets in 10 different tables then do i need 10 different transform data task or there's some other way to do it.
Thanks
--king

 

[Idokyoku2]

King,

Be sure to create your destination table first before attempting to import multiple tabs/sheets in Excel.

For example, after selecting Excel 97-2000, locating file and all that, you'll be prompted to select a destination table. At that time DTS will display all sheets from a single Excel file; select each sheet into sample SQL table and all will be well.

David

 

[Kingkumar]

so u mean to say that i dont need to create 10 different transform data task to migrate data from 10 sheets from same workbook?
--King

 

[Idokyoku2]

That is correct. I would like to mention both tabs I used during this test had the exact same table structure. Don't
whether yours is the same. You can always use query builder to spec columns needed.
D

 

[Idokyoku2]

Also, DTS will mention how many tables have been imported, or sheets/tabs in this case.
D

 

[yksvaan]

Idokyoku2, you are saying : That is correct i dont need to create 10 different transform data.

How would you import 10 worksheet with one Transform Data task to a single destination table WITHOUT manually changing the source sheet setting after every step execute, I think Kingreen is obviously not anxious to do so?

Well, it is possible, using Dynamic Properties Task, but you didn't mention that.

Kingreen, transfering each sheet to separate tables or all sheets to a single one, it's the same. You'd still need 10 tasks, but using Dynamic Properties Task it is possible to automate changing the source and destinations and have only one task. But there's some learning to do to get grip of Dynamic Properties Task. So 10 transforms is not that hard, if it's just copy column data transforms without anything special. It would have been done twice in this time I've been writing this post.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[Idokyoku2]

Try it. It will prompt you for all sheets within an Excel file for import to the same table. I've not done this before today, but it did work. I can only assume 10 would act as two did? It reads them as separate tables.

D

 

[yksvaan]

Idokyoku2, I think you are referring to using the Import Wizard, which let's you select all sheets. But the outcome of this, what I meant, is a package with 10 Transform Data Tasks, and 10 source and 10 destination connections!

Building the package yourself you need only one connection for source and one for destination, but still the 10 transformations.

As far as I know the way to do it with only one Trn task is to use Dynamic Properties Task to chage the source sheet and/or destination table.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]