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.

Terry Perkins

Old Hand

Points: 355
More actions
March 23, 2008 at 12:48 pm

#120596

I have an issue where I would like an end user to drop a formatted text file into a directory. I would like to have a polling service pick up the file, import it into to a SQL table and then move/rename the original file into a archive directory. Can this all be done in SSIS? Thanks in advance.

Viewing 15 posts - 1 through 15 (of 20 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I don't know SSIS that well, but it can certainly be done in plain old Transact SQL (i.e., the SQL Server Data Engine). I do it all the time.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Terry Perkins Old Hand Points: 355 More actions · Answer 2

I got it work by creating a SQL job that is triggered by a WMI alert from within the job and thus fires my SSIS package.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Glad it worked out.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

pritesh2205 Ten Centuries Points: 1355 More actions · Answer 4

You can use WMI task with the following WQL query to watch a folder or a particular file .....

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"'

Robert.concepcion SSC Enthusiast Points: 194 More actions · Answer 5

Yes it can, but you have to be a programmer.

You have a SSIS package that an SQL agent job calls every hour.

Does File exist, NO, exit.

Else yes.

Is file Processed? if yes the archive it

Else

Process it

Then Archive it.

You need a batch processing table and a file table with your staging table.

Terry Perkins Old Hand Points: 355 More actions · Answer 6

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.

Ananth-431739 Ten Centuries Points: 1185 More actions · Answer 7

Hi,

Could you send me the steps and code that how you solved this task thr' WMI?

Help is highly appreciated.....

darkman2020 SSChasing Mays Points: 602 More actions · Answer 8

This can easily be done in ssis you can use the file watcher task to watch and see if any change has happened in the directory and get the file name and then you can use a script task and a file system task to move the file to an archive file. this is very simple.

john.arnott SSChampion Points: 11882 More actions · Answer 9

Although setting up a foreach loop with a WMI event watcher works algorithmically, there's an "issue" with SSIS not releasing memory at the end of the loop. Jamie Thompson in his "SSIS Junkie" blog passed along this nugget from "skreekc". See http://blogs.conchango.com/jamiethomson/archive/2006/10/18/SSIS_3A00_-Random-information-for-beginners.aspx

Using a WMI alert to trigger the package sounds good. What we did for a similar situation was schedule the package to run every ten minutes and let the foreach loop decide if a file was there.

One more caveat: Both WMI and the foreach enumerator will see a file as present as soon as it starts to arrive in the target drive. If it's a large file, your SSIS routine may fail trying to read a file that's not yet fully available. To avoid this, you may want to try the third-party developed "FileWatcher" task from Konesans, Ltd., although I'm not sure that would take care of the memory leak aspect of an infinite loop. Or, as we did, use a parallel directory to receive an "EOF" file with the same name but different extension after sending the big "real data" file, and run your foreach loop using that for the enumerator.

S. Kusen SSChampion Points: 10936 More actions · Answer 10

The way I have done this was the following:

1. set up a package variable, something like "fileFound" as an int

2. set up a script task to look for the file you are looking for (vb.net coding libraries), if the file is found, set dts.variables("fileFound").value = 1 (if found, 1, else 0)

3. set up the data flow task to be the second task in the control flow

4. on the green success line from the script task to the data flow task, change the properties of the green line to "expression and constraint" and make the expression "@fileFound == 1", and the constraint "Success" so the data flow task only fires if the script task runs successfully and when the file was found.

That is as close to using all SSIS components as I could find.

RonKyle SSC-Dedicated Points: 31554 More actions · Answer 11

I'd like to add the warning about grabbing files instantly--sometimes the complete file is not available. I've seen that happen. Setting the check for 15 minute intervals still carries some small risk that it could happen, but I've never seen it happen with that setting. Normally a 15 minute wait is more than acceptable.

bishals Grasshopper Points: 19 More actions · Answer 12

hey dudes i have got the concept of WMI task and when using it can we read the name of the file which are variable and put that name in aany variable using WMI task so that we can use that file name later .

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 13

At http://www.sqlis.com/23.aspx there is a file watcher task that will do the same thing with a bit less confusion.

Watch out for this task and the use of the WMI event on a job agent (they are both doing the same thing). WMI events do not queue. So, if you are processing a file when the event happens rather than waiting for the event, you will miss the event. It is good practice at the end of processing a file to do the work of checking for any files that arrived while you were processing.

nupz.scorpi Newbie Points: 9 More actions · Answer 14

"I have an issue where I would like an end user to drop a formatted text file into a directory. I would like to have a polling service pick up the file, import it into to a SQL table and then move/rename the original file into a archive directory. Can this all be done in SSIS? Thanks in advance. "

Hi guys,

i hv to build the same application using java and sql...can u guide me how to aproach the problem..

Thanks a lot ...

wud be highly obliged..

Thanks

Nupur