MS SQL Server DTS - Importing An Excel File With Unknown Columns . . .

Author

Importing An Excel File With Unknown Columns . . .

DeniseMe

2005-09-21, 11:24 am

Hello,

I need to be able to import an excel file that has a dynamic column set. I
want to import it to a sql table that will be created new each time at the
time of import with the name of the excel file as the table name. Since the
excel file will always have a different number of columns in it to import, I
can't just set up a table or delete and recreate a table before import (since
I don't know the column names or how many there are). How do I go about
doing this in DTS? I am already using the dynamic properties and global
variable to grab the dynamic excel file name and use it in the excel
connection, but just don't know how to get dynamic transforms/create a table
each time.

Thanks,
Denise

Allan Mitchell

2005-09-21, 8:24 pm

You are going to need to figure out how to get the column headings from
the Excel Spreadsheet. You may be better asking that question on an
Excel Newsgroup unless Douglas or the like comes in here. After that
and once you know the columns headings then you can dynamically build a
datapump task from this information (along with the name of the sheet)

I have an example package illustrating the latter if you ping offline

Allan

"DeniseMe" < DeniseMe@discussions
.microsoft.com> wrote in message
news:AFFAE34B-663F-4227-8380- 374E750B9AE0@microso
ft.com:

> Hello,
>
> I need to be able to import an excel file that has a dynamic column set.
> I
> want to import it to a sql table that will be created new each time at
> the
> time of import with the name of the excel file as the table name. Since
> the
> excel file will always have a different number of columns in it to
> import, I
> can't just set up a table or delete and recreate a table before import
> (since
> I don't know the column names or how many there are). How do I go about
> doing this in DTS? I am already using the dynamic properties and global
> variable to grab the dynamic excel file name and use it in the excel
> connection, but just don't know how to get dynamic transforms/create a
> table
> each time.
>
> Thanks,
> Denise

DeniseMe

2005-09-30, 11:23 am

I tried to send the following to your e-mail address and it was returned to me:

Hello Allan,

I had asked a question a couple of weeks ago (9/21) about import dynamic
excel file columns. You had said you have an example package illustrating
how to build a dynamic datapump task. Is there any way I could get my hands
on that example package?? Thanks!! I haven't quite figured out the excel
side of things because this project as a priority has slipped for me, but I
am not back on it and will hopefully figure it out or have the input file
converted to a different format :o)

Thanks so much for your help!!

Thanks,

Denise
denise.meyer@firstlogic.com

Can you send me the package example to the above e-mail address?
Thanks so much!!

"Allan Mitchell" wrote:

> You are going to need to figure out how to get the column headings from
> the Excel Spreadsheet. You may be better asking that question on an
> Excel Newsgroup unless Douglas or the like comes in here. After that
> and once you know the columns headings then you can dynamically build a
> datapump task from this information (along with the name of the sheet)
>
> I have an example package illustrating the latter if you ping offline
>
>
> Allan
>
> "DeniseMe" < DeniseMe@discussions
.microsoft.com> wrote in message
> news:AFFAE34B-663F-4227-8380- 374E750B9AE0@microso
ft.com:
>
>
>

Allan Mitchell

2005-09-30, 1:23 pm

Remove the no-spam decoy.

--

----------------------------------------
Allan Mitchell (Microsoft SQL Server MVP)
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

"DeniseMe" < DeniseMe@discussions
.microsoft.com> wrote in message
news:EBB9F464-B2F0-4AC5-ADF2- D1BAAB7D8CAF@microso
ft.com:
[color=darkred]
> I tried to send the following to your e-mail address and it was returned to me:
>
> Hello Allan,
>
> I had asked a question a couple of weeks ago (9/21) about import dynamic
> excel file columns. You had said you have an example package illustrating
> how to build a dynamic datapump task. Is there any way I could get my hands
> on that example package?? Thanks!! I haven't quite figured out the excel
> side of things because this project as a priority has slipped for me, but I
> am not back on it and will hopefully figure it out or have the input file
> converted to a different format :o)
>
> Thanks so much for your help!!
>
> Thanks,
>
> Denise
> denise.meyer@firstlogic.com
>
> Can you send me the package example to the above e-mail address?
> Thanks so much!!
>
>
>
>
> "Allan Mitchell" wrote:
>

Sponsored Links