x

using wmi event task in ssis for loading files into database tables

Hi team,

I have problem as follows: files comes from FTP and stored in share folder from share folder based on their types they are loaded into database. I am trying to use WMI event watcher task to know the information whether new file is placed is in shared folder or not, how to get output filenames from that wmi event watcher task.So that i can use file system task to put the files into other folder and later use data flow task for loading into database.

please help me how to get output filenames which are newly placed in the folder through WMI

Thanks, Starterm.

more ▼

asked Apr 19, 2010 at 09:53 AM in Default

avatar image

starterm
31 2 2 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Hi Starterm,

I have done a project similar to this where I was 'watching' an FTP folder location for new files.

Most FTP sites I've used, if not all of them, are not windows boxes or if they are windows, wouldnt allow WMI access.

Two questions, are the filenames unique? And do you remove the downloaded files from the FTP site?

If each file is unique (you won't have to remove them), I created an ssis package which downloaded the files, then looped through the files in the folder, and loaded them into a table. Then I checked another table that holds the already uploaded files. If they haven’t been processed before, I load them using a dataflow task.

If the files are not unique, then you should download the file and remove it from the FTP site (remove it so whenever there is a file there, you know it is new) and rename it with a time stamp, then upload it.

Then you can schedule your package to run however often you like.

There are stacks of good SFTP addins here http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/00043b62-d243-427a-8118-3b40e9281050.

If you need any more help with this, then let me know and I’ll make steps for you.

Cheers and happy SSISing

Dan.

more ▼

answered Apr 19, 2010 at 09:00 PM

avatar image

Daniel Ross
2.9k 11 15 18

Thanks for the quick reply. Yes files are unique and should be deleted from shared folder once they are loaded, but problem is whenever their is new file loaded from ftp to share folder that specific file should be loaded to database. By using wmi task i can know their is new file placed in the shared folder but how to get that file name from wmi task which has been placed newly and later put that file into database.

Please share the steps

Thanks

Apr 20, 2010 at 05:00 AM starterm

please help me out regarding this

Apr 30, 2010 at 02:04 AM starterm
(comments are locked)
10|1200 characters needed characters left

Was this resolved? I'd like ot see the solution as well.

thanks,

more ▼

answered Apr 14, 2011 at 08:43 AM

avatar image

bhassel
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1198

asked: Apr 19, 2010 at 09:53 AM

Seen: 2211 times

Last Updated: May 03, 2010 at 07:44 AM

Copyright 2017 Redgate Software. Privacy Policy