x

WMI Event task to load data from xml files to database tables

I need to accomplish the following tasks:

1) Detect a new zip file in a designated folder
2) Unzip files which are xml files
3) Loop through list of files, read data, update/replace data in database tables.

Do you think that I can use WMI Event watcher task and WMI Data read task to do these?

Thank you.

more ▼

asked Apr 23, 2010 at 03:53 PM in Default

Lydia gravatar image

Lydia
1 1 1 1

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

2 answers: sort oldest

You should be able to use the WMI Event Watcher Task - but the main purpose of this task is to "wait" until a file is detected, and "then" do something. If you're just running your package manually or on a schedule, and wanting it to do something with files in a folder that weren't there last time you ran the package, you don't want the WMI Event Watcher Task.

In order to "detect" files, you can use the Foreach Loop Container to iterate over all the files in the folder, then inside the loop, determine if the file is "new" (however you determine that). If you want to know file modification dates, you can use a Script with the System.IO.FileInfo namespace, or use the File Properties Task.

To unzip files, you can use the Script Task and public code like SharpZip, or you can use one of the compression tasks found on the SSIS Community Tasks and Components site.

Once you have your files located/detected, and extracted, you'd use a Data Flow Task to read them in to update/replace data in a database.

more ▼

answered Apr 26, 2010 at 06:09 PM

Todd McDermid gravatar image

Todd McDermid
391 2

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

Thank you very much for your reply. I have a WMI Event Watcher Task to watch the folder, and then a script task to do unzip. It works fine when it starts from Visual Studio project. I start with the wmi event watcher task , it turns yellow, kind of waiting, after I drop the new zip file in the designated folder a few seconds later, it turns to green and proceed to unzip task, and it unzip the zip file. But, when I set up this as a job in SQL Server Agent, run it every minute, it ran through steps with no error, but did not unzip the file. How can I set up WMI task, so it watches the folder wait until a new zip file coming, then proceed to other steps?

more ▼

answered Apr 27, 2010 at 11:12 AM

Lydia gravatar image

Lydia
1 1 1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x937

asked: Apr 23, 2010 at 03:53 PM

Seen: 1056 times

Last Updated: Apr 24, 2010 at 06:25 PM