Dear NGroup,

I want my package to wait for certain files to show up in
the folder before it could start executing the data
transfer??

I have large number of sources (more than 150 or so)
sending their data to me. My job is to absorb all the
data from these sources into my DB. The transfer from
these sources is not guaranteed until all the text files
show up in the folder assigned to that source. While this
is happening I want to kick-off my DTS and go home. I
want this package to keep waiting for all the files show
up in any one of the folders scheduled for that day.
Start the data import just for that folder and wait for
another source to finish the transfer and import that
data later and so on. I cannot control the way each
source is transferring their data. They want to send at
their own time during the day. And we cannot handle more
15 transfers in a day. But it is virtually impossible for
me wait for them to finish 1 by 1 to kick-off my job for
each one of them separately.
Hope all this makes sense to anyone?
I am pretty close to it but not getting something to work.

Thanks
Sachy.

We used to do the exact same thing at a previous lient.

Our requirement was not much different to yours.

A full data load = 40 files.
Groups of files made up a part of the load and once all files for that part
were found in the location we could load that part.

We had a VB program on a loop during the day that asked the question of the
operating system "What files do you have?" The file names were stored in a
DB table. If all files for a load were there it fired a package.

You can equally do this with workflow.

You could

1.
Kick off the package in the morning.
Have it check for files every 10 mins.
If all there then import then go to sleep for 10 mins. If not all there go
to sleep for 10 mins.

2.

Have SQL Server Agent kick off the package every 10 mins and you simply
check for the files. and if there then you import them.

Multiple Paths in Workflow
(http://www.sqldts.com/default.aspx?218)


My VB example was before the days of .Net. In .Net i would use the
FileSystemWatcher class to do the waiting and fire the events to call my
package.

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Sachy" <anonymous (AT) discussions (DOT) microsoft.com> wrote

In article <OjHghJeyDHA.2308 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Allan Mitchell
<allan (AT) no-spam (DOT) sqldts.com> writes
<snip>
Even before .net this was possible, using the
FindFirstChangeNotification and related API calls. Here is a VB
implementation sample-

http://www.mvps.org/vbnet/code/filea...chedfolder.htm


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Good catch. I am lazy and waited for .Net <grin>

--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Darren Green" <darren.green (AT) reply-to-newsgroup-only (DOT) uk.com> wrote in
message news:XvBdKBBXMX6$Ewk2 (AT) sqldts (DOT) com...

Thanks Darren,

I didnt know about this. I will try this one somewhere.
But in the mean time here is an alternative option.
Well I got something to work using VBscript WScript.sleep.
This way I stay in my DTS ActiveXScript. Here is what I
did. It is working but needs some more final touch up and
testing.

1. A Job is schedule to kick-off DTS.
2. DTS package starts with checking Today's schedule from
SQL Table. Reads all the respective directory path from
the table.
3. Upon knowing all the directories, it checks 1 by 1 all
of them for existence of all the files.
4. If all files not found in any of the directories,
CALL WscShell.Run("Sleep.vbs", True)
where Sleep.vbs has only one line, WScript.sleep 10000.
5. After 10 seconds, it loops through again.
6. Finds all the files for any one folder, continues with
importing data.
7. Imports the data from folder which has all the files in
it.
8. Marks the status and continue to work on remaining.

Does this makes sense or you find any flaw in here.
I didnt have much time to finish this but if i get
opportunity, i will try your way.

Thanks to both of you,
Sachy

You may want to introduce MSMQ in the mix as the files can be picked up near
realtime and processed by the DTS package waiting for the messages arrive
using the MSMQ task.

"Sachy" <anonymous (AT) discussions (DOT) microsoft.com> wrote

In message <00ec01c3ca5b$23abc440$a401280a (AT) phx (DOT) gbl>, Sachy
<anonymous (AT) discussions (DOT) microsoft.com> writes
Sachy, if it works for you then it works, so use it!

Cheers


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org