Excel file to be imported to database daily but hte file name changes 1

[Jeet2004]

Hi,
I need to access an excel file which is on shared drive , but the problem is the file is generated by a reporting tool and the file name changes daily.
Is there a way to get a file from a folder ( the folder will always have one file) and use that file(whatever name it has) to import to the database server ?
If yes than how can it be done ?
Any help would be greatly appreicated.
Thanks

 

[mrdenny]

I assume that the file will be some format of the date.

You can use a dynamic properites task to reset the name of the file you are looking for.

You can also issue a dos move command to rename all files to a single file name. If there is only one file this should work fine. The problem becomes if there are ever two files.

I'd recomend changing the name of the file you are looking for.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Jeet2004]

we are using a third party tool and it generates teh file name on its own in some hexadecimal format (fffx8.xls or ffff85.xls etc)( it generates three files one sytem file, one report file and one excel file of the report, every time the report is run)

and the problem is that the folder where the file is dumped has history files too.
is there a way in DTS where in the scripts , using fso, we can find the latest file in the folder ?
how do we issue a dos command in the package itself to rename or move files.
Thanks

 

[DrSql]

The way I approch is as Denny suggested add a DOS step to your DTS package.

Your first step should be Copy the file to a backup folder and rename the file to your DTS linked excel file. At the end of the process delete the file.

For example Your excel file is getting generated into folder called EXCEL, create a subfolder called backup.

Copy C:\EXCEL\*.XLS C:\EXCEL\BACKUP
REN or RENAME *.XLS DTSFILE.XLS
After your Import
DEL C:\EXCEL\*.XLS

You can add this as a SQL Statement also by
exec master.dbo.xp_cmdshell


Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[Jeet2004]

Thanks DrSql for your help.
I am new to dts so would appreciate if you could tell me where& how to add the above script in the dts pacakge
The way i do it is in the dTS is i add excel and sql server connection and there is a transform data task in b/w the two , so where does the above script goes.
Should i be putting them as Execute sql task (copy will have on success go to import work flow and delete will have on success workflow)and have a workflow of on success after and before the tasks

 

[DrSql]

Here are the steps.
STEP1
SQL EXECUTE
exec master.dbo.xp_cmdshell 'copy C:\EXCEL\*.XLS C:\EXCEL\BAK\'
go
exec master.dbo.xp_cmdshell 'RENAME C:\EXCEL\*.XLS DTSFILE.XLS'

STEP2
TRANSFER FROM EXCEL to SQL SERVER

STEP3
SQL EXECUTE
exec master.dbo.xp_cmdshell 'DEL C:\EXCEL\*.XLS'

I have created a sample DTS package, if a public FTP site or email availbale I can send to you.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[Jeet2004]

Thanks a lot Dr.Sql i appreciate your help.
My email address is

gojeet2004@yahoo.com

if you could email me the sample package it would be great thanks.

 

[DrSql]

Its on its way.. Good luck

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[Jeet2004]

thanks