I read the artical "Looping, Importing and Archiving" by Allan Mitchell
and "Import all files in a directory (Multi Step) By Darren Green". It
is very helpful! I can even import directory of text files with DTS
package successfully.
But when i I try to import the directory of excel files to sql server.
Only the first file can be imported successfully. It failed at the 2nd
file import. I got the error message "'Statement2$' is not a valid
name. Make sure that it does not include invalid characters or
punctuation and that it is not too long." Statement2.xls is the 2nd
file under the directory.
Can someone help with this issue?

Statement2.xls is not statement2$

One if the name of a file and one is a named range in Excel.

Do all of your Excel spreadsheets contain a tab/table/named range of
statement2$

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<megankiew (AT) gmail (DOT) com> wrote

Allan,
Sorry! I did not totally understand what you've suggested. The excel
name is statement2.xls. There is only one workbook with statement2.xls
and its name is statement2.
what do you mean
Do all of your Excel spreadsheets contain a tab/table/named range of
statement2$ ?

Thanks!

I understand that statment2$ is the worksheet, not the file. In the
sample package i downloaded. After the loop task, the text source
points to the 2nd file in the directory, but the source of transform
data task still points to the first one which is statement2$. Should i
change the source of transform data task in activex task.

I understand that statment2$ is the worksheet, not the file. In the
sample package i downloaded. After the loop task, the text source
points to the 2nd file in the directory, but the source of transform
data task still points to the first one which is statement2$. Should i
change the source of transform data task in activex task.

You should change the DataSource property of the Source connection object.

It will be looking for a statement2$ in the new DataSource.

Si there one?

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


<megankiew (AT) gmail (DOT) com> wrote