Using SSIS to poll a directory for a text file and then insert it into SQL table.

Question

Using SSIS to poll a directory for a text file and then insert it into SQL table.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

pritesh2205 Ten Centuries Points: 1355 More actions · Answer 1

Yes, it can be done is SSIS. Depending upon what you want to do , you need to use the various components present in control flow of SSIS.

You can keep a watch on the folder where the file will be dropped using a WMI task. You can use the following query by modifying the path

FOR INDIVIDUAL FILE :

SELECT * FROM __InstanceCreationEvent WITHIN 60 WHERE TargetInstance ISA 'CIM_DataFile' AND TargetInstance.Name = 'c:\\ImportData\\test.txt'

FOR WATCHING A FOLDER :

SELECT * FROM __instancecreationevent WITHIN 60 WHERE TargetInstance ISA 'Cim_DirectoryContainsFile' AND TargetInstance.GroupComponent='Win32_Directory.Name="c:\\\\ImportData"'

Here 60 is the time duration , for which you want to watch the folder or file.

You should then connect the WMI task to a dataflow task , where you will load the file to the table.

After that use a file system task to move the file to an archive folder, you can change the name of the file by creating an expression for the file path in the file system task.

- Hope this helps.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 2

Just wanted to add a work around for those people who talked about using a waitfor or a time delay to prevent processing of a file not yet ready. I used that method a few years back to trigger a filewatcher service and it just wasn't reliable enough.

Rather than using a delay, rename the file after it has been extracted to the triggering name (If your set up allows it).

A delay may work for a while, but if file is large and growing or network performance variable you can never be certain that your time delay is sufficient (this resulted in too many load failures for us). On the other hand you may be wasting valuable batch time waiting for a file that was there 10 minutes ago.

Renaming a file is fairly simple to do in SSIS using the file system task.

Kindest Regards,

Frank Bazan

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 3

Just a quick thought...

Another solution, possibly more stable than a time delay, even if you cannot rename the file, is a time delay with a file-size check. If the file size has not changes in 2 (or 5) minutes, the file is no longer begin transferred. So, in a watcher loop, when new files are found, the names and sizes are loaded into a variable, a delay of x minutes occurs, then the files are compared with the sizes in the variable. Those ready to load are added to another variables, and the previous variable is rewritten with the new names and sizes of those still pending.

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 4

Indeed, someone offer this man a M&M (or is that a M - singular). The Scripting.FileSystemObject object will allow you to check the size property: http://msdn.microsoft.com/en-us/library/2d66skaf(VS.85).aspx

Max

haathikb SSC Journeyman Points: 75 More actions · Answer 5

Terry Perkins (3/24/2008)
I discovered that I don't need to have a schedule for my SQL agent job that runs my SSIS. The WMI Alert that is attached to the job triggers it and fires it off.

Terry,

I am trying to select the WMI Event Alert (like you have in your screenshot) in my SQL Agent Job step on SQL 2005, but cannot see that option in the dropdown.

How do you get the WMI option in the SQL Agent Step setup ?

-Shiva

Madhuchhanda Kundu Old Hand Points: 360 More actions · Answer 6

Hi,

can you please provide a sample code for that ?

thanks

madhu