June 1, 2011 at 1:16 pm
We have an ETL process (SSIS) that loads data from a csv file on a ftp site. Before executing the SSIS package we check via a stored procedure if the file exists yet. We do so using the following command:
SET @path = 'dir \\' + 'UNCPath'+ ' /A-D /B'
INSERT @fileList
EXEC MASTER.dbo.xp_cmdshell @path
Then if the file we're looking for is in the list we execute the SSIS package, else we do nothing. This works most of the time but in some cases it seems that the file has not completed upload but the above code finds it. As a result some FTP tasks in the SSIS package fail saying file not found.
I'm looking for a variation of the above code to make sure the file is completely uploaded before executing SSIS. For example, would xp_fileExists work better?
June 2, 2011 at 9:15 am
IMHO it seems odd to call into a database to check if a file exists when you're in a .NET environment (SSIS) that is much better suited for doing file system tasks.
Why not use the File System Watcher Task for SSIS -> http://www.sqlis.com/23.aspx.
It will wait until it can gain exclusive access to a file before proceeding so, unless the FTP process is not maintaining a consistent write-handle to the file while it is being uploaded, it will solve the problem you're having with picking up a file that is still being written to.
An ancillary benefit of moving everything into SSIS is that you can do away with one use of xp_CmdShell to check for a file which is a step in the right direction when you have SSIS in place.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 2, 2011 at 9:43 am
Thanks. Our SSIS package does check to see that the file exists. We check in SQL first because we don't even want to run SSIS if it does not exist. Running the SSIS kicks off a lot of queueing and logging we don't want. Also, the SSIS package does have a task to the check for the file. It see's the file, so does the ftp task to archive the file. It's when it trys to delete the file from the root it fails. I think I'll try the task you suggested.
June 2, 2011 at 10:24 am
Just a thought...if your main pkg is too noisy to kick off straight away then you could create a parent pkg to contain a File Watcher and an Execute Package Task that calls your existing package when the file is there.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply