|
|
You're in the
sql server dts
group:Action based execution.
sql server dts:
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 [quoted text, click to view] "Sachy" <anonymous@discussions.microsoft.com> wrote in message news:03a401c3c9a0$adddf220$a101280a@phx.gbl... > 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. >
In article <OjHghJeyDHA.2308@TK2MSFTNGP11.phx.gbl>, Allan Mitchell
<allan@no-spam.sqldts.com> writes <snip> [quoted text, click to view] > >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. > 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/fileapi/watchedfolder.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@reply-to-newsgroup-only.uk.com> wrote in message news:XvBdKBBXMX6$Ewk2@sqldts.com... [quoted text, click to view] > In article <OjHghJeyDHA.2308@TK2MSFTNGP11.phx.gbl>, Allan Mitchell > <allan@no-spam.sqldts.com> writes > <snip> > > > >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. > > > > 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/fileapi/watchedfolder.htm > > > -- > Darren Green (SQL Server MVP) > DTS - http://www.sqldts.com > > PASS - the definitive, global community for SQL Server professionals > http://www.sqlpass.org >
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 [quoted text, click to view] >-----Original Message-----
>In article <OjHghJeyDHA.2308@TK2MSFTNGP11.phx.gbl>, Allan Mitchell ><allan@no-spam.sqldts.com> writes ><snip> >> >>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. >> > >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/fileapi/watchedfolder.htm > > >-- >Darren Green (SQL Server MVP) >DTS - http://www.sqldts.com > >PASS - the definitive, global community for SQL Server professionals >http://www.sqlpass.org > >.
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. [quoted text, click to view] "Sachy" <anonymous@discussions.microsoft.com> wrote in message news:00ec01c3ca5b$23abc440$a401280a@phx.gbl... > 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 > > >-----Original Message----- > >In article <OjHghJeyDHA.2308@TK2MSFTNGP11.phx.gbl>, Allan > Mitchell > ><allan@no-spam.sqldts.com> writes > ><snip> > >> > >>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. > >> > > > >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/fileapi/watchedfolder.htm > > > > > >-- > >Darren Green (SQL Server MVP) > >DTS - http://www.sqldts.com > > > >PASS - the definitive, global community for SQL Server > professionals > >http://www.sqlpass.org > > > >. > >
In message <00ec01c3ca5b$23abc440$a401280a@phx.gbl>, Sachy
<anonymous@discussions.microsoft.com> writes [quoted text, click to view] >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 > 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |