March 23, 2008 at 12:48 pm
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.
March 23, 2008 at 2:05 pm
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]
March 23, 2008 at 7:29 pm
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.
March 23, 2008 at 8:49 pm
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]
March 24, 2008 at 4:53 am
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"'
March 24, 2008 at 9:33 am
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.
March 24, 2008 at 10:30 am
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.
March 24, 2008 at 11:16 pm
Hi,
Could you send me the steps and code that how you solved this task thr' WMI?
Help is highly appreciated.....
March 26, 2008 at 1:01 am
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.
March 26, 2008 at 10:32 am
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.
March 30, 2008 at 8:00 pm
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.
April 14, 2008 at 9:31 am
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.
May 27, 2008 at 6:52 am
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 .
May 27, 2008 at 8:30 am
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.
July 9, 2008 at 12:48 am
"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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply