|
|
You're in the
sql server dts
group:Import multiple mdb files to SQL Server issue: Please Help...
sql server dts:
I have about 100+ mdb files with the same table structure design; need to be imported into SQL Server. They are in the same directory but separated sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb files....d:\MTS\A...n\*mdb files) I've created the DTS package to import but it handles only one file for each package. It's impossible for me to create 100+ DTS packages with the actual source filename. Please help, I'm greatly appreciated for your help. Sincerely --
Hello NT,
You can easily convert this package to do what you want http://www.sqldts.com/246.aspx -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > Hi All, > I have about 100+ mdb files with the same table structure design; need > to be > imported into SQL Server. They are in the same directory but > separated > sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > files....d:\MTS\A...n\*mdb files) > I've created the DTS package to import but it handles only one file > for each > package. It's impossible for me to create 100+ DTS packages with the > actual > source filename. Please help, I'm greatly appreciated for your help. > Sincerely >
Hi Allan,
I've used the sample package from http://www.sqldts.com/246.aspx as you suggested. This package works well for the source data in flat text format. Unfortunately, in my case that I'm dealing with the source data in MS Access format. Perhaps, I don't know to convert this package to work for MS Access files. Thanks very much if you would get me more and/or better hints on this issue. -- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > You can easily convert this package to do what you want > > http://www.sqldts.com/246.aspx > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Hi All, > > I have about 100+ mdb files with the same table structure design; need > > to be > > imported into SQL Server. They are in the same directory but > > separated > > sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > > files....d:\MTS\A...n\*mdb files) > > I've created the DTS package to import but it handles only one file > > for each > > package. It's impossible for me to create 100+ DTS packages with the > > actual > > source filename. Please help, I'm greatly appreciated for your help. > > Sincerely > > > >
Hi Allan,
I'm really bad...I still could not make it work even with a big help from you. Here is my modification if you would help...Thanks buch! I sent my .dts to your email Thanks, -- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > > So your loop simply changes to looping over MDBs > Your assignment of found MDB changes to be a different type of connection. > You remove the FF --> SQL Server and replace with Access --> SQL Server > > > Hopefully this should get you started > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Hi Allan, > > > > I've used the sample package from http://www.sqldts.com/246.aspx as > > you suggested. This package works well for the source data in flat > > text format. Unfortunately, in my case that I'm dealing with the > > source data in MS Access format. Perhaps, I don't know to convert > > this package to work for MS Access files. > > > > Thanks very much if you would get me more and/or better hints on this > > issue. > > > > "Allan Mitchell" wrote: > > > >> Hello NT, > >> > >> You can easily convert this package to do what you want > >> > >> http://www.sqldts.com/246.aspx > >> > >> -- > >> > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> Hi All, > >>> I have about 100+ mdb files with the same table structure design; > >>> need > >>> to be > >>> imported into SQL Server. They are in the same directory but > >>> separated > >>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > >>> files....d:\MTS\A...n\*mdb files) > >>> I've created the DTS package to import but it handles only one file > >>> for each > >>> package. It's impossible for me to create 100+ DTS packages with the > >>> actual > >>> source filename. Please help, I'm greatly appreciated for your help. > >>> Sincerely > >
I have sent to this address allan@no-spam.sqldts.com. Is it right?
-- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > You will need to modify my eMail slightly to get it to work. > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Hi Allan, > > > > I'm really bad...I still could not make it work even with a big help > > from you. Here is my modification if you would help...Thanks buch! I > > sent my .dts to your email > > > > Thanks, > > > > "Allan Mitchell" wrote: > > > >> Hello NT, > >> > >> So your loop simply changes to looping over MDBs > >> Your assignment of found MDB changes to be a different type of > >> connection. > >> You remove the FF --> SQL Server and replace with Access --> SQL > >> Server > >> Hopefully this should get you started > >> > >> -- > >> > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> Hi Allan, > >>> > >>> I've used the sample package from http://www.sqldts.com/246.aspx as > >>> you suggested. This package works well for the source data in flat > >>> text format. Unfortunately, in my case that I'm dealing with the > >>> source data in MS Access format. Perhaps, I don't know to convert > >>> this package to work for MS Access files. > >>> > >>> Thanks very much if you would get me more and/or better hints on > >>> this issue. > >>> > >>> "Allan Mitchell" wrote: > >>> > >>>> Hello NT, > >>>> > >>>> You can easily convert this package to do what you want > >>>> > >>>> http://www.sqldts.com/246.aspx > >>>> > >>>> -- > >>>> > >>>> Allan Mitchell > >>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>> http://www.sqldts.com > >>>> | > >>>> http://www.konesans.com > >>>>> Hi All, > >>>>> I have about 100+ mdb files with the same table structure design; > >>>>> need > >>>>> to be > >>>>> imported into SQL Server. They are in the same directory but > >>>>> separated > >>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > >>>>> files....d:\MTS\A...n\*mdb files) > >>>>> I've created the DTS package to import but it handles only one > >>>>> file > >>>>> for each > >>>>> package. It's impossible for me to create 100+ DTS packages with > >>>>> the > >>>>> actual > >>>>> source filename. Please help, I'm greatly appreciated for your > >>>>> help. > >>>>> Sincerely > >
Please check your email again...
-- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > remove no-spam > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > I have sent to this address allan@no-spam.sqldts.com. Is it right? > > > > "Allan Mitchell" wrote: > > > >> Hello NT, > >> > >> You will need to modify my eMail slightly to get it to work. > >> > >> -- > >> > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> Hi Allan, > >>> > >>> I'm really bad...I still could not make it work even with a big help > >>> from you. Here is my modification if you would help...Thanks buch! > >>> I sent my .dts to your email > >>> > >>> Thanks, > >>> > >>> "Allan Mitchell" wrote: > >>> > >>>> Hello NT, > >>>> > >>>> So your loop simply changes to looping over MDBs > >>>> Your assignment of found MDB changes to be a different type of > >>>> connection. > >>>> You remove the FF --> SQL Server and replace with Access --> SQL > >>>> Server > >>>> Hopefully this should get you started > >>>> -- > >>>> > >>>> Allan Mitchell > >>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>> http://www.sqldts.com > >>>> | > >>>> http://www.konesans.com > >>>>> Hi Allan, > >>>>> > >>>>> I've used the sample package from http://www.sqldts.com/246.aspx > >>>>> as you suggested. This package works well for the source data in > >>>>> flat text format. Unfortunately, in my case that I'm dealing with > >>>>> the source data in MS Access format. Perhaps, I don't know to > >>>>> convert this package to work for MS Access files. > >>>>> > >>>>> Thanks very much if you would get me more and/or better hints on > >>>>> this issue. > >>>>> > >>>>> "Allan Mitchell" wrote: > >>>>> > >>>>>> Hello NT, > >>>>>> > >>>>>> You can easily convert this package to do what you want > >>>>>> > >>>>>> http://www.sqldts.com/246.aspx > >>>>>> > >>>>>> -- > >>>>>> > >>>>>> Allan Mitchell > >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>> http://www.sqldts.com > >>>>>> | > >>>>>> http://www.konesans.com > >>>>>>> Hi All, > >>>>>>> I have about 100+ mdb files with the same table structure > >>>>>>> design; > >>>>>>> need > >>>>>>> to be > >>>>>>> imported into SQL Server. They are in the same directory but > >>>>>>> separated > >>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > >>>>>>> files....d:\MTS\A...n\*mdb files) > >>>>>>> I've created the DTS package to import but it handles only one > >>>>>>> file > >>>>>>> for each > >>>>>>> package. It's impossible for me to create 100+ DTS packages with > >>>>>>> the > >>>>>>> actual > >>>>>>> source filename. Please help, I'm greatly appreciated for your > >>>>>>> help. > >>>>>>> Sincerely > >
Hello NT,
So your loop simply changes to looping over MDBs Your assignment of found MDB changes to be a different type of connection. You remove the FF --> SQL Server and replace with Access --> SQL Server Hopefully this should get you started -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > Hi Allan, > > I've used the sample package from http://www.sqldts.com/246.aspx as > you suggested. This package works well for the source data in flat > text format. Unfortunately, in my case that I'm dealing with the > source data in MS Access format. Perhaps, I don't know to convert > this package to work for MS Access files. > > Thanks very much if you would get me more and/or better hints on this > issue. > > "Allan Mitchell" wrote: > >> Hello NT, >> >> You can easily convert this package to do what you want >> >> http://www.sqldts.com/246.aspx >> >> -- >> >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> Hi All, >>> I have about 100+ mdb files with the same table structure design; >>> need >>> to be >>> imported into SQL Server. They are in the same directory but >>> separated >>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb >>> files....d:\MTS\A...n\*mdb files) >>> I've created the DTS package to import but it handles only one file >>> for each >>> package. It's impossible for me to create 100+ DTS packages with the >>> actual >>> source filename. Please help, I'm greatly appreciated for your help. >>> Sincerely
Hello NT,
You will need to modify my eMail slightly to get it to work. -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > Hi Allan, > > I'm really bad...I still could not make it work even with a big help > from you. Here is my modification if you would help...Thanks buch! I > sent my .dts to your email > > Thanks, > > "Allan Mitchell" wrote: > >> Hello NT, >> >> So your loop simply changes to looping over MDBs >> Your assignment of found MDB changes to be a different type of >> connection. >> You remove the FF --> SQL Server and replace with Access --> SQL >> Server >> Hopefully this should get you started >> >> -- >> >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> Hi Allan, >>> >>> I've used the sample package from http://www.sqldts.com/246.aspx as >>> you suggested. This package works well for the source data in flat >>> text format. Unfortunately, in my case that I'm dealing with the >>> source data in MS Access format. Perhaps, I don't know to convert >>> this package to work for MS Access files. >>> >>> Thanks very much if you would get me more and/or better hints on >>> this issue. >>> >>> "Allan Mitchell" wrote: >>> >>>> Hello NT, >>>> >>>> You can easily convert this package to do what you want >>>> >>>> http://www.sqldts.com/246.aspx >>>> >>>> -- >>>> >>>> Allan Mitchell >>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>> http://www.sqldts.com >>>> | >>>> http://www.konesans.com >>>>> Hi All, >>>>> I have about 100+ mdb files with the same table structure design; >>>>> need >>>>> to be >>>>> imported into SQL Server. They are in the same directory but >>>>> separated >>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb >>>>> files....d:\MTS\A...n\*mdb files) >>>>> I've created the DTS package to import but it handles only one >>>>> file >>>>> for each >>>>> package. It's impossible for me to create 100+ DTS packages with >>>>> the >>>>> actual >>>>> source filename. Please help, I'm greatly appreciated for your >>>>> help. >>>>> Sincerely
Hello NT,
remove no-spam -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > I have sent to this address allan@no-spam.sqldts.com. Is it right? > > "Allan Mitchell" wrote: > >> Hello NT, >> >> You will need to modify my eMail slightly to get it to work. >> >> -- >> >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> Hi Allan, >>> >>> I'm really bad...I still could not make it work even with a big help >>> from you. Here is my modification if you would help...Thanks buch! >>> I sent my .dts to your email >>> >>> Thanks, >>> >>> "Allan Mitchell" wrote: >>> >>>> Hello NT, >>>> >>>> So your loop simply changes to looping over MDBs >>>> Your assignment of found MDB changes to be a different type of >>>> connection. >>>> You remove the FF --> SQL Server and replace with Access --> SQL >>>> Server >>>> Hopefully this should get you started >>>> -- >>>> >>>> Allan Mitchell >>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>> http://www.sqldts.com >>>> | >>>> http://www.konesans.com >>>>> Hi Allan, >>>>> >>>>> I've used the sample package from http://www.sqldts.com/246.aspx >>>>> as you suggested. This package works well for the source data in >>>>> flat text format. Unfortunately, in my case that I'm dealing with >>>>> the source data in MS Access format. Perhaps, I don't know to >>>>> convert this package to work for MS Access files. >>>>> >>>>> Thanks very much if you would get me more and/or better hints on >>>>> this issue. >>>>> >>>>> "Allan Mitchell" wrote: >>>>> >>>>>> Hello NT, >>>>>> >>>>>> You can easily convert this package to do what you want >>>>>> >>>>>> http://www.sqldts.com/246.aspx >>>>>> >>>>>> -- >>>>>> >>>>>> Allan Mitchell >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>> http://www.sqldts.com >>>>>> | >>>>>> http://www.konesans.com >>>>>>> Hi All, >>>>>>> I have about 100+ mdb files with the same table structure >>>>>>> design; >>>>>>> need >>>>>>> to be >>>>>>> imported into SQL Server. They are in the same directory but >>>>>>> separated >>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb >>>>>>> files....d:\MTS\A...n\*mdb files) >>>>>>> I've created the DTS package to import but it handles only one >>>>>>> file >>>>>>> for each >>>>>>> package. It's impossible for me to create 100+ DTS packages with >>>>>>> the >>>>>>> actual >>>>>>> source filename. Please help, I'm greatly appreciated for your >>>>>>> help. >>>>>>> Sincerely
Hello NT,
Got it thanks. Will look tonight -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > Please check your email again... > > "Allan Mitchell" wrote: > >> Hello NT, >> >> remove no-spam >> >> -- >> >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> I have sent to this address allan@no-spam.sqldts.com. Is it right? >>> >>> "Allan Mitchell" wrote: >>> >>>> Hello NT, >>>> >>>> You will need to modify my eMail slightly to get it to work. >>>> >>>> -- >>>> >>>> Allan Mitchell >>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>> http://www.sqldts.com >>>> | >>>> http://www.konesans.com >>>>> Hi Allan, >>>>> >>>>> I'm really bad...I still could not make it work even with a big >>>>> help from you. Here is my modification if you would help...Thanks >>>>> buch! I sent my .dts to your email >>>>> >>>>> Thanks, >>>>> >>>>> "Allan Mitchell" wrote: >>>>> >>>>>> Hello NT, >>>>>> >>>>>> So your loop simply changes to looping over MDBs >>>>>> Your assignment of found MDB changes to be a different type of >>>>>> connection. >>>>>> You remove the FF --> SQL Server and replace with Access --> SQL >>>>>> Server >>>>>> Hopefully this should get you started >>>>>> -- >>>>>> Allan Mitchell >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>> http://www.sqldts.com >>>>>> | >>>>>> http://www.konesans.com >>>>>>> Hi Allan, >>>>>>> >>>>>>> I've used the sample package from http://www.sqldts.com/246.aspx >>>>>>> as you suggested. This package works well for the source data >>>>>>> in flat text format. Unfortunately, in my case that I'm dealing >>>>>>> with the source data in MS Access format. Perhaps, I don't know >>>>>>> to convert this package to work for MS Access files. >>>>>>> >>>>>>> Thanks very much if you would get me more and/or better hints on >>>>>>> this issue. >>>>>>> >>>>>>> "Allan Mitchell" wrote: >>>>>>> >>>>>>>> Hello NT, >>>>>>>> >>>>>>>> You can easily convert this package to do what you want >>>>>>>> >>>>>>>> http://www.sqldts.com/246.aspx >>>>>>>> >>>>>>>> -- >>>>>>>> >>>>>>>> Allan Mitchell >>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>>> http://www.sqldts.com >>>>>>>> | >>>>>>>> http://www.konesans.com >>>>>>>>> Hi All, >>>>>>>>> I have about 100+ mdb files with the same table structure >>>>>>>>> design; >>>>>>>>> need >>>>>>>>> to be >>>>>>>>> imported into SQL Server. They are in the same directory but >>>>>>>>> separated >>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb >>>>>>>>> files....d:\MTS\A...n\*mdb files) >>>>>>>>> I've created the DTS package to import but it handles only one >>>>>>>>> file >>>>>>>>> for each >>>>>>>>> package. It's impossible for me to create 100+ DTS packages >>>>>>>>> with >>>>>>>>> the >>>>>>>>> actual >>>>>>>>> source filename. Please help, I'm greatly appreciated for your >>>>>>>>> help. >>>>>>>>> Sincerely
Hi Allan,
I got your email, thanks for your response... I know the file extension .res doesn't look like MDB but it is. We have to use MS Access 2K or 2003 version to open. I currently have about 200+ .res files need to be imported onto SQL Server database. These *.res files have been generated from the 3rd party vendor system. There are 10 tables in each ..res file and all .res files have exactly the same table structure design, except the data diff. I'm very beginner for this task, so I need a lot of help...It's greatly appreciated for any help. Best regards, -- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > > Got it thanks. Will look tonight > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Please check your email again... > > > > "Allan Mitchell" wrote: > > > >> Hello NT, > >> > >> remove no-spam > >> > >> -- > >> > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> I have sent to this address allan@no-spam.sqldts.com. Is it right? > >>> > >>> "Allan Mitchell" wrote: > >>> > >>>> Hello NT, > >>>> > >>>> You will need to modify my eMail slightly to get it to work. > >>>> > >>>> -- > >>>> > >>>> Allan Mitchell > >>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>> http://www.sqldts.com > >>>> | > >>>> http://www.konesans.com > >>>>> Hi Allan, > >>>>> > >>>>> I'm really bad...I still could not make it work even with a big > >>>>> help from you. Here is my modification if you would help...Thanks > >>>>> buch! I sent my .dts to your email > >>>>> > >>>>> Thanks, > >>>>> > >>>>> "Allan Mitchell" wrote: > >>>>> > >>>>>> Hello NT, > >>>>>> > >>>>>> So your loop simply changes to looping over MDBs > >>>>>> Your assignment of found MDB changes to be a different type of > >>>>>> connection. > >>>>>> You remove the FF --> SQL Server and replace with Access --> SQL > >>>>>> Server > >>>>>> Hopefully this should get you started > >>>>>> -- > >>>>>> Allan Mitchell > >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>> http://www.sqldts.com > >>>>>> | > >>>>>> http://www.konesans.com > >>>>>>> Hi Allan, > >>>>>>> > >>>>>>> I've used the sample package from http://www.sqldts.com/246.aspx > >>>>>>> as you suggested. This package works well for the source data > >>>>>>> in flat text format. Unfortunately, in my case that I'm dealing > >>>>>>> with the source data in MS Access format. Perhaps, I don't know > >>>>>>> to convert this package to work for MS Access files. > >>>>>>> > >>>>>>> Thanks very much if you would get me more and/or better hints on > >>>>>>> this issue. > >>>>>>> > >>>>>>> "Allan Mitchell" wrote: > >>>>>>> > >>>>>>>> Hello NT, > >>>>>>>> > >>>>>>>> You can easily convert this package to do what you want > >>>>>>>> > >>>>>>>> http://www.sqldts.com/246.aspx > >>>>>>>> > >>>>>>>> -- > >>>>>>>> > >>>>>>>> Allan Mitchell > >>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>> http://www.sqldts.com > >>>>>>>> | > >>>>>>>> http://www.konesans.com > >>>>>>>>> Hi All, > >>>>>>>>> I have about 100+ mdb files with the same table structure > >>>>>>>>> design; > >>>>>>>>> need > >>>>>>>>> to be > >>>>>>>>> imported into SQL Server. They are in the same directory but > >>>>>>>>> separated > >>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > >>>>>>>>> files....d:\MTS\A...n\*mdb files) > >>>>>>>>> I've created the DTS package to import but it handles only one > >>>>>>>>> file > >>>>>>>>> for each > >>>>>>>>> package. It's impossible for me to create 100+ DTS packages > >>>>>>>>> with > >>>>>>>>> the > >>>>>>>>> actual > >>>>>>>>> source filename. Please help, I'm greatly appreciated for your > >>>>>>>>> help. > >>>>>>>>> Sincerely > >
Hello NT,
So you have the package looping correctly You have the Transform Data task set up. What is the error you get? -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > Hi Allan, > > I got your email, thanks for your response... > I know the file extension .res doesn't look like MDB but it is. We > have to > use MS Access 2K or 2003 version to open. I currently have about 200+ > .res > files need to be imported onto SQL Server database. These *.res files > have > been generated from the 3rd party vendor system. There are 10 tables > in each > .res file and all .res files have exactly the same table structure > design, > except the data diff. > I'm very beginner for this task, so I need a lot of help...It's > greatly appreciated for any help. > > Best regards, > > "Allan Mitchell" wrote: > >> Hello NT, >> >> Got it thanks. Will look tonight >> -- >> Allan Mitchell >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >> | >> http://www.konesans.com >>> Please check your email again... >>> >>> "Allan Mitchell" wrote: >>> >>>> Hello NT, >>>> >>>> remove no-spam >>>> >>>> -- >>>> >>>> Allan Mitchell >>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>> http://www.sqldts.com >>>> | >>>> http://www.konesans.com >>>>> I have sent to this address allan@no-spam.sqldts.com. Is it >>>>> right? >>>>> >>>>> "Allan Mitchell" wrote: >>>>> >>>>>> Hello NT, >>>>>> >>>>>> You will need to modify my eMail slightly to get it to work. >>>>>> >>>>>> -- >>>>>> >>>>>> Allan Mitchell >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>> http://www.sqldts.com >>>>>> | >>>>>> http://www.konesans.com >>>>>>> Hi Allan, >>>>>>> >>>>>>> I'm really bad...I still could not make it work even with a big >>>>>>> help from you. Here is my modification if you would >>>>>>> help...Thanks buch! I sent my .dts to your email >>>>>>> >>>>>>> Thanks, >>>>>>> >>>>>>> "Allan Mitchell" wrote: >>>>>>> >>>>>>>> Hello NT, >>>>>>>> >>>>>>>> So your loop simply changes to looping over MDBs >>>>>>>> Your assignment of found MDB changes to be a different type of >>>>>>>> connection. >>>>>>>> You remove the FF --> SQL Server and replace with Access --> >>>>>>>> SQL >>>>>>>> Server >>>>>>>> Hopefully this should get you started >>>>>>>> -- >>>>>>>> Allan Mitchell >>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>>> http://www.sqldts.com >>>>>>>> | >>>>>>>> http://www.konesans.com >>>>>>>>> Hi Allan, >>>>>>>>> >>>>>>>>> I've used the sample package from >>>>>>>>> http://www.sqldts.com/246.aspx as you suggested. This package >>>>>>>>> works well for the source data in flat text format. >>>>>>>>> Unfortunately, in my case that I'm dealing with the source >>>>>>>>> data in MS Access format. Perhaps, I don't know to convert >>>>>>>>> this package to work for MS Access files. >>>>>>>>> >>>>>>>>> Thanks very much if you would get me more and/or better hints >>>>>>>>> on this issue. >>>>>>>>> >>>>>>>>> "Allan Mitchell" wrote: >>>>>>>>> >>>>>>>>>> Hello NT, >>>>>>>>>> >>>>>>>>>> You can easily convert this package to do what you want >>>>>>>>>> >>>>>>>>>> http://www.sqldts.com/246.aspx >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> >>>>>>>>>> Allan Mitchell >>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>>>>> http://www.sqldts.com >>>>>>>>>> | >>>>>>>>>> http://www.konesans.com >>>>>>>>>>> Hi All, >>>>>>>>>>> I have about 100+ mdb files with the same table structure >>>>>>>>>>> design; >>>>>>>>>>> need >>>>>>>>>>> to be >>>>>>>>>>> imported into SQL Server. They are in the same directory >>>>>>>>>>> but >>>>>>>>>>> separated >>>>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb >>>>>>>>>>> files....d:\MTS\A...n\*mdb files) >>>>>>>>>>> I've created the DTS package to import but it handles only >>>>>>>>>>> one >>>>>>>>>>> file >>>>>>>>>>> for each >>>>>>>>>>> package. It's impossible for me to create 100+ DTS packages >>>>>>>>>>> with >>>>>>>>>>> the >>>>>>>>>>> actual >>>>>>>>>>> source filename. Please help, I'm greatly appreciated for >>>>>>>>>>> your >>>>>>>>>>> help. >>>>>>>>>>> Sincerely
Hi Allan,
Sorry for the late response, I just got back from my trip. Anyway, It have not worked yet but I will try again and keep you posted if you're still interested. Thanks for your help, -- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > > So you have the package looping correctly > You have the Transform Data task set up. > What is the error you get? > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Hi Allan, > > > > I got your email, thanks for your response... > > I know the file extension .res doesn't look like MDB but it is. We > > have to > > use MS Access 2K or 2003 version to open. I currently have about 200+ > > .res > > files need to be imported onto SQL Server database. These *.res files > > have > > been generated from the 3rd party vendor system. There are 10 tables > > in each > > .res file and all .res files have exactly the same table structure > > design, > > except the data diff. > > I'm very beginner for this task, so I need a lot of help...It's > > greatly appreciated for any help. > > > > Best regards, > > > > "Allan Mitchell" wrote: > > > >> Hello NT, > >> > >> Got it thanks. Will look tonight > >> -- > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> Please check your email again... > >>> > >>> "Allan Mitchell" wrote: > >>> > >>>> Hello NT, > >>>> > >>>> remove no-spam > >>>> > >>>> -- > >>>> > >>>> Allan Mitchell > >>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>> http://www.sqldts.com > >>>> | > >>>> http://www.konesans.com > >>>>> I have sent to this address allan@no-spam.sqldts.com. Is it > >>>>> right? > >>>>> > >>>>> "Allan Mitchell" wrote: > >>>>> > >>>>>> Hello NT, > >>>>>> > >>>>>> You will need to modify my eMail slightly to get it to work. > >>>>>> > >>>>>> -- > >>>>>> > >>>>>> Allan Mitchell > >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>> http://www.sqldts.com > >>>>>> | > >>>>>> http://www.konesans.com > >>>>>>> Hi Allan, > >>>>>>> > >>>>>>> I'm really bad...I still could not make it work even with a big > >>>>>>> help from you. Here is my modification if you would > >>>>>>> help...Thanks buch! I sent my .dts to your email > >>>>>>> > >>>>>>> Thanks, > >>>>>>> > >>>>>>> "Allan Mitchell" wrote: > >>>>>>> > >>>>>>>> Hello NT, > >>>>>>>> > >>>>>>>> So your loop simply changes to looping over MDBs > >>>>>>>> Your assignment of found MDB changes to be a different type of > >>>>>>>> connection. > >>>>>>>> You remove the FF --> SQL Server and replace with Access --> > >>>>>>>> SQL > >>>>>>>> Server > >>>>>>>> Hopefully this should get you started > >>>>>>>> -- > >>>>>>>> Allan Mitchell > >>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>> http://www.sqldts.com > >>>>>>>> | > >>>>>>>> http://www.konesans.com > >>>>>>>>> Hi Allan, > >>>>>>>>> > >>>>>>>>> I've used the sample package from > >>>>>>>>> http://www.sqldts.com/246.aspx as you suggested. This package > >>>>>>>>> works well for the source data in flat text format. > >>>>>>>>> Unfortunately, in my case that I'm dealing with the source > >>>>>>>>> data in MS Access format. Perhaps, I don't know to convert > >>>>>>>>> this package to work for MS Access files. > >>>>>>>>> > >>>>>>>>> Thanks very much if you would get me more and/or better hints > >>>>>>>>> on this issue. > >>>>>>>>> > >>>>>>>>> "Allan Mitchell" wrote: > >>>>>>>>> > >>>>>>>>>> Hello NT, > >>>>>>>>>> > >>>>>>>>>> You can easily convert this package to do what you want > >>>>>>>>>> > >>>>>>>>>> http://www.sqldts.com/246.aspx > >>>>>>>>>> > >>>>>>>>>> -- > >>>>>>>>>> > >>>>>>>>>> Allan Mitchell > >>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>>>> http://www.sqldts.com > >>>>>>>>>> | > >>>>>>>>>> http://www.konesans.com > >>>>>>>>>>> Hi All, > >>>>>>>>>>> I have about 100+ mdb files with the same table structure > >>>>>>>>>>> design; > >>>>>>>>>>> need > >>>>>>>>>>> to be > >>>>>>>>>>> imported into SQL Server. They are in the same directory > >>>>>>>>>>> but > >>>>>>>>>>> separated > >>>>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > >>>>>>>>>>> files....d:\MTS\A...n\*mdb files) > >>>>>>>>>>> I've created the DTS package to import but it handles only > >>>>>>>>>>> one > >>>>>>>>>>> file > >>>>>>>>>>> for each > >>>>>>>>>>> package. It's impossible for me to create 100+ DTS packages > >>>>>>>>>>> with > >>>>>>>>>>> the > >>>>>>>>>>> actual > >>>>>>>>>>> source filename. Please help, I'm greatly appreciated for > >>>>>>>>>>> your > >>>>>>>>>>> help. > >>>>>>>>>>> Sincerely > >
Hi Alan,
Thanks for your help...! I finally made it works with the way I wanted for the single table selected; however, if I selected to import multiple tables in the mdb file then the following statement from Loop Around seemed to fail. set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3") 'The trick to looping in DTS is to set the step at the start of the loop to an execution status of waiting stpbegin.ExecutionStatus = DTSStepExecStat_Waiting -- NT [quoted text, click to view] "NT" wrote:
> Hi Allan, > > Sorry for the late response, I just got back from my trip. Anyway, It have > not worked yet but I will try again and keep you posted if you're still > interested. > > Thanks for your help, > -- > NT > > > "Allan Mitchell" wrote: > > > Hello NT, > > > > > > So you have the package looping correctly > > You have the Transform Data task set up. > > What is the error you get? > > > > -- > > > > Allan Mitchell > > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > > http://www.konesans.com > > > > > Hi Allan, > > > > > > I got your email, thanks for your response... > > > I know the file extension .res doesn't look like MDB but it is. We > > > have to > > > use MS Access 2K or 2003 version to open. I currently have about 200+ > > > .res > > > files need to be imported onto SQL Server database. These *.res files > > > have > > > been generated from the 3rd party vendor system. There are 10 tables > > > in each > > > .res file and all .res files have exactly the same table structure > > > design, > > > except the data diff. > > > I'm very beginner for this task, so I need a lot of help...It's > > > greatly appreciated for any help. > > > > > > Best regards, > > > > > > "Allan Mitchell" wrote: > > > > > >> Hello NT, > > >> > > >> Got it thanks. Will look tonight > > >> -- > > >> Allan Mitchell > > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > > >> | > > >> http://www.konesans.com > > >>> Please check your email again... > > >>> > > >>> "Allan Mitchell" wrote: > > >>> > > >>>> Hello NT, > > >>>> > > >>>> remove no-spam > > >>>> > > >>>> -- > > >>>> > > >>>> Allan Mitchell > > >>>> http://wiki.sqlis.com | http://www.sqlis.com | > > >>>> http://www.sqldts.com > > >>>> | > > >>>> http://www.konesans.com > > >>>>> I have sent to this address allan@no-spam.sqldts.com. Is it > > >>>>> right? > > >>>>> > > >>>>> "Allan Mitchell" wrote: > > >>>>> > > >>>>>> Hello NT, > > >>>>>> > > >>>>>> You will need to modify my eMail slightly to get it to work. > > >>>>>> > > >>>>>> -- > > >>>>>> > > >>>>>> Allan Mitchell > > >>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > > >>>>>> http://www.sqldts.com > > >>>>>> | > > >>>>>> http://www.konesans.com > > >>>>>>> Hi Allan, > > >>>>>>> > > >>>>>>> I'm really bad...I still could not make it work even with a big > > >>>>>>> help from you. Here is my modification if you would > > >>>>>>> help...Thanks buch! I sent my .dts to your email > > >>>>>>> > > >>>>>>> Thanks, > > >>>>>>> > > >>>>>>> "Allan Mitchell" wrote: > > >>>>>>> > > >>>>>>>> Hello NT, > > >>>>>>>> > > >>>>>>>> So your loop simply changes to looping over MDBs > > >>>>>>>> Your assignment of found MDB changes to be a different type of > > >>>>>>>> connection. > > >>>>>>>> You remove the FF --> SQL Server and replace with Access --> > > >>>>>>>> SQL > > >>>>>>>> Server > > >>>>>>>> Hopefully this should get you started > > >>>>>>>> -- > > >>>>>>>> Allan Mitchell > > >>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > > >>>>>>>> http://www.sqldts.com > > >>>>>>>> | > > >>>>>>>> http://www.konesans.com > > >>>>>>>>> Hi Allan, > > >>>>>>>>> > > >>>>>>>>> I've used the sample package from > > >>>>>>>>> http://www.sqldts.com/246.aspx as you suggested. This package > > >>>>>>>>> works well for the source data in flat text format. > > >>>>>>>>> Unfortunately, in my case that I'm dealing with the source > > >>>>>>>>> data in MS Access format. Perhaps, I don't know to convert > > >>>>>>>>> this package to work for MS Access files. > > >>>>>>>>> > > >>>>>>>>> Thanks very much if you would get me more and/or better hints > > >>>>>>>>> on this issue. > > >>>>>>>>> > > >>>>>>>>> "Allan Mitchell" wrote: > > >>>>>>>>> > > >>>>>>>>>> Hello NT, > > >>>>>>>>>> > > >>>>>>>>>> You can easily convert this package to do what you want > > >>>>>>>>>> > > >>>>>>>>>> http://www.sqldts.com/246.aspx > > >>>>>>>>>> > > >>>>>>>>>> -- > > >>>>>>>>>> > > >>>>>>>>>> Allan Mitchell > > >>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > > >>>>>>>>>> http://www.sqldts.com > > >>>>>>>>>> | > > >>>>>>>>>> http://www.konesans.com > > >>>>>>>>>>> Hi All, > > >>>>>>>>>>> I have about 100+ mdb files with the same table structure > > >>>>>>>>>>> design; > > >>>>>>>>>>> need > > >>>>>>>>>>> to be > > >>>>>>>>>>> imported into SQL Server. They are in the same directory > > >>>>>>>>>>> but > > >>>>>>>>>>> separated > > >>>>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and d:\MTS\A2\*.mdb > > >>>>>>>>>>> files....d:\MTS\A...n\*mdb files) > > >>>>>>>>>>> I've created the DTS package to import but it handles only > > >>>>>>>>>>> one > > >>>>>>>>>>> file > > >>>>>>>>>>> for each > > >>>>>>>>>>> package. It's impossible for me to create 100+ DTS packages > > >>>>>>>>>>> with > > >>>>>>>>>>> the > > >>>>>>>>>>> actual > > >>>>>>>>>>> source filename. Please help, I'm greatly appreciated for > > >>>>>>>>>>> your > > >>>>>>>>>>> help. > > >>>>>>>>>>> Sincerely > > > >
Hi Allan,
I have checked and assured that the name DTSStep_DTSActiveScriptTask_3 is correct since I kept the same original name from your example. Moreover, it actually worked if I select to import only one table in mdb file. So, the loop around did work; it went back to begin loop = DTSStep_DTSActiveScriptTask_3 to take the next mdb file then do the data import task, continue looping until the last mbd file in the folder then execute the finished loop. However, it failed and the Finished module never ran if I select to import more than one table. I hope it would not confuse you. Thanks, -- NT [quoted text, click to view] "Allan Mitchell" wrote:
> Hello NT, > > What is probably happening is that the step I mention DTSStep_DTSActiveScriptTask_3 > is not the same in your package. Go to the Workflow properties and find > out the step name of your equivalent step > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Hi Alan, > > > > Thanks for your help...! I finally made it works with the way I wanted > > for the single table selected; however, if I selected to import > > multiple tables in the mdb file then the following statement from Loop > > Around seemed to fail. > > > > set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3") > > 'The trick to looping in DTS is to set the step at the start of the > > loop to > > an execution status of waiting > > stpbegin.ExecutionStatus = DTSStepExecStat_Waiting > > > > "NT" wrote: > > > >> Hi Allan, > >> > >> Sorry for the late response, I just got back from my trip. Anyway, It > >> have not worked yet but I will try again and keep you posted if > >> you're still interested. > >> > >> Thanks for your help, > >> -- > >> NT > >> "Allan Mitchell" wrote: > >> > >>> Hello NT, > >>> > >>> So you have the package looping correctly > >>> You have the Transform Data task set up. > >>> What is the error you get? > >>> -- > >>> > >>> Allan Mitchell > >>> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >>> | > >>> http://www.konesans.com > >>>> Hi Allan, > >>>> > >>>> I got your email, thanks for your response... > >>>> I know the file extension .res doesn't look like MDB but it is. We > >>>> have to > >>>> use MS Access 2K or 2003 version to open. I currently have about > >>>> 200+ > >>>> .res > >>>> files need to be imported onto SQL Server database. These *.res > >>>> files > >>>> have > >>>> been generated from the 3rd party vendor system. There are 10 > >>>> tables > >>>> in each > >>>> .res file and all .res files have exactly the same table structure > >>>> design, > >>>> except the data diff. > >>>> I'm very beginner for this task, so I need a lot of help...It's > >>>> greatly appreciated for any help. > >>>> Best regards, > >>>> > >>>> "Allan Mitchell" wrote: > >>>> > >>>>> Hello NT, > >>>>> > >>>>> Got it thanks. Will look tonight > >>>>> -- > >>>>> Allan Mitchell > >>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>> http://www.sqldts.com > >>>>> | > >>>>> http://www.konesans.com > >>>>>> Please check your email again... > >>>>>> > >>>>>> "Allan Mitchell" wrote: > >>>>>> > >>>>>>> Hello NT, > >>>>>>> > >>>>>>> remove no-spam > >>>>>>> > >>>>>>> -- > >>>>>>> > >>>>>>> Allan Mitchell > >>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>> http://www.sqldts.com > >>>>>>> | > >>>>>>> http://www.konesans.com > >>>>>>>> I have sent to this address allan@no-spam.sqldts.com. Is it > >>>>>>>> right? > >>>>>>>> > >>>>>>>> "Allan Mitchell" wrote: > >>>>>>>> > >>>>>>>>> Hello NT, > >>>>>>>>> > >>>>>>>>> You will need to modify my eMail slightly to get it to work. > >>>>>>>>> > >>>>>>>>> -- > >>>>>>>>> > >>>>>>>>> Allan Mitchell > >>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>>> http://www.sqldts.com > >>>>>>>>> | > >>>>>>>>> http://www.konesans.com > >>>>>>>>>> Hi Allan, > >>>>>>>>>> > >>>>>>>>>> I'm really bad...I still could not make it work even with a > >>>>>>>>>> big help from you. Here is my modification if you would > >>>>>>>>>> help...Thanks buch! I sent my .dts to your email > >>>>>>>>>> > >>>>>>>>>> Thanks, > >>>>>>>>>> > >>>>>>>>>> "Allan Mitchell" wrote: > >>>>>>>>>> > >>>>>>>>>>> Hello NT, > >>>>>>>>>>> > >>>>>>>>>>> So your loop simply changes to looping over MDBs > >>>>>>>>>>> Your assignment of found MDB changes to be a different type > >>>>>>>>>>> of > >>>>>>>>>>> connection. > >>>>>>>>>>> You remove the FF --> SQL Server and replace with Access --> > >>>>>>>>>>> SQL > >>>>>>>>>>> Server > >>>>>>>>>>> Hopefully this should get you started > >>>>>>>>>>> -- > >>>>>>>>>>> Allan Mitchell > >>>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>>>>> http://www.sqldts.com > >>>>>>>>>>> | > >>>>>>>>>>> http://www.konesans.com > >>>>>>>>>>>> Hi Allan, > >>>>>>>>>>>> > >>>>>>>>>>>> I've used the sample package from > >>>>>>>>>>>> http://www.sqldts.com/246.aspx as you suggested. This > >>>>>>>>>>>> package works well for the source data in flat text format. > >>>>>>>>>>>> Unfortunately, in my case that I'm dealing with the source > >>>>>>>>>>>> data in MS Access format. Perhaps, I don't know to convert > >>>>>>>>>>>> this package to work for MS Access files. > >>>>>>>>>>>> > >>>>>>>>>>>> Thanks very much if you would get me more and/or better > >>>>>>>>>>>> hints on this issue. > >>>>>>>>>>>> > >>>>>>>>>>>> "Allan Mitchell" wrote: > >>>>>>>>>>>> > >>>>>>>>>>>>> Hello NT, > >>>>>>>>>>>>> > >>>>>>>>>>>>> You can easily convert this package to do what you want > >>>>>>>>>>>>> > >>>>>>>>>>>>> http://www.sqldts.com/246.aspx > >>>>>>>>>>>>> > >>>>>>>>>>>>> -- > >>>>>>>>>>>>> > >>>>>>>>>>>>> Allan Mitchell > >>>>>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>>>>>>> http://www.sqldts.com > >>>>>>>>>>>>> | > >>>>>>>>>>>>> http://www.konesans.com > >>>>>>>>>>>>>> Hi All, > >>>>>>>>>>>>>> I have about 100+ mdb files with the same table structure > >>>>>>>>>>>>>> design; > >>>>>>>>>>>>>> need > >>>>>>>>>>>>>> to be > >>>>>>>>>>>>>> imported into SQL Server. They are in the same directory > >>>>>>>>>>>>>> but > >>>>>>>>>>>>>> separated > >>>>>>>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and > >>>>>>>>>>>>>> d:\MTS\A2\*.mdb > >>>>>>>>>>>>>> files....d:\MTS\A...n\*mdb files) > >>>>>>>>>>>>>> I've created the DTS package to import but it handles > >>>>>>>>>>>>>> only > >>>>>>>>>>>>>> one > >>>>>>>>>>>>>> file > >>>>>>>>>>>>>> for each > >>>>>>>>>>>>>> package. It's impossible for me to create 100+ DTS > >>>>>>>>>>>>>> packages > >>>>>>>>>>>>>> with > >>>>>>>>>>>>>> the > >>>>>>>>>>>>>> actual > >>>>>>>>>>>>>> source filename. Please help, I'm greatly appreciated for > >>>>>>>>>>>>>> your > >>>>>>>>>>>>>> help.
Hello NT,
What is probably happening is that the step I mention DTSStep_DTSActiveScriptTask_3 is not the same in your package. Go to the Workflow properties and find out the step name of your equivalent step -- Allan Mitchell http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | http://www.konesans.com [quoted text, click to view] > Hi Alan, > > Thanks for your help...! I finally made it works with the way I wanted > for the single table selected; however, if I selected to import > multiple tables in the mdb file then the following statement from Loop > Around seemed to fail. > > set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3") > 'The trick to looping in DTS is to set the step at the start of the > loop to > an execution status of waiting > stpbegin.ExecutionStatus = DTSStepExecStat_Waiting > > "NT" wrote: > >> Hi Allan, >> >> Sorry for the late response, I just got back from my trip. Anyway, It >> have not worked yet but I will try again and keep you posted if >> you're still interested. >> >> Thanks for your help, >> -- >> NT >> "Allan Mitchell" wrote: >> >>> Hello NT, >>> >>> So you have the package looping correctly >>> You have the Transform Data task set up. >>> What is the error you get? >>> -- >>> >>> Allan Mitchell >>> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com >>> | >>> http://www.konesans.com >>>> Hi Allan, >>>> >>>> I got your email, thanks for your response... >>>> I know the file extension .res doesn't look like MDB but it is. We >>>> have to >>>> use MS Access 2K or 2003 version to open. I currently have about >>>> 200+ >>>> .res >>>> files need to be imported onto SQL Server database. These *.res >>>> files >>>> have >>>> been generated from the 3rd party vendor system. There are 10 >>>> tables >>>> in each >>>> .res file and all .res files have exactly the same table structure >>>> design, >>>> except the data diff. >>>> I'm very beginner for this task, so I need a lot of help...It's >>>> greatly appreciated for any help. >>>> Best regards, >>>> >>>> "Allan Mitchell" wrote: >>>> >>>>> Hello NT, >>>>> >>>>> Got it thanks. Will look tonight >>>>> -- >>>>> Allan Mitchell >>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>> http://www.sqldts.com >>>>> | >>>>> http://www.konesans.com >>>>>> Please check your email again... >>>>>> >>>>>> "Allan Mitchell" wrote: >>>>>> >>>>>>> Hello NT, >>>>>>> >>>>>>> remove no-spam >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> Allan Mitchell >>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>> http://www.sqldts.com >>>>>>> | >>>>>>> http://www.konesans.com >>>>>>>> I have sent to this address allan@no-spam.sqldts.com. Is it >>>>>>>> right? >>>>>>>> >>>>>>>> "Allan Mitchell" wrote: >>>>>>>> >>>>>>>>> Hello NT, >>>>>>>>> >>>>>>>>> You will need to modify my eMail slightly to get it to work. >>>>>>>>> >>>>>>>>> -- >>>>>>>>> >>>>>>>>> Allan Mitchell >>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>>>> http://www.sqldts.com >>>>>>>>> | >>>>>>>>> http://www.konesans.com >>>>>>>>>> Hi Allan, >>>>>>>>>> >>>>>>>>>> I'm really bad...I still could not make it work even with a >>>>>>>>>> big help from you. Here is my modification if you would >>>>>>>>>> help...Thanks buch! I sent my .dts to your email >>>>>>>>>> >>>>>>>>>> Thanks, >>>>>>>>>> >>>>>>>>>> "Allan Mitchell" wrote: >>>>>>>>>> >>>>>>>>>>> Hello NT, >>>>>>>>>>> >>>>>>>>>>> So your loop simply changes to looping over MDBs >>>>>>>>>>> Your assignment of found MDB changes to be a different type >>>>>>>>>>> of >>>>>>>>>>> connection. >>>>>>>>>>> You remove the FF --> SQL Server and replace with Access --> >>>>>>>>>>> SQL >>>>>>>>>>> Server >>>>>>>>>>> Hopefully this should get you started >>>>>>>>>>> -- >>>>>>>>>>> Allan Mitchell >>>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>>>>>> http://www.sqldts.com >>>>>>>>>>> | >>>>>>>>>>> http://www.konesans.com >>>>>>>>>>>> Hi Allan, >>>>>>>>>>>> >>>>>>>>>>>> I've used the sample package from >>>>>>>>>>>> http://www.sqldts.com/246.aspx as you suggested. This >>>>>>>>>>>> package works well for the source data in flat text format. >>>>>>>>>>>> Unfortunately, in my case that I'm dealing with the source >>>>>>>>>>>> data in MS Access format. Perhaps, I don't know to convert >>>>>>>>>>>> this package to work for MS Access files. >>>>>>>>>>>> >>>>>>>>>>>> Thanks very much if you would get me more and/or better >>>>>>>>>>>> hints on this issue. >>>>>>>>>>>> >>>>>>>>>>>> "Allan Mitchell" wrote: >>>>>>>>>>>> >>>>>>>>>>>>> Hello NT, >>>>>>>>>>>>> >>>>>>>>>>>>> You can easily convert this package to do what you want >>>>>>>>>>>>> >>>>>>>>>>>>> http://www.sqldts.com/246.aspx >>>>>>>>>>>>> >>>>>>>>>>>>> -- >>>>>>>>>>>>> >>>>>>>>>>>>> Allan Mitchell >>>>>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | >>>>>>>>>>>>> http://www.sqldts.com >>>>>>>>>>>>> | >>>>>>>>>>>>> http://www.konesans.com >>>>>>>>>>>>>> Hi All, >>>>>>>>>>>>>> I have about 100+ mdb files with the same table structure >>>>>>>>>>>>>> design; >>>>>>>>>>>>>> need >>>>>>>>>>>>>> to be >>>>>>>>>>>>>> imported into SQL Server. They are in the same directory >>>>>>>>>>>>>> but >>>>>>>>>>>>>> separated >>>>>>>>>>>>>> sub-folders (ex: d:\MTS\A1\*.mdb files and >>>>>>>>>>>>>> d:\MTS\A2\*.mdb >>>>>>>>>>>>>> files....d:\MTS\A...n\*mdb files) >>>>>>>>>>>>>> I've created the DTS package to import but it handles >>>>>>>>>>>>>> only >>>>>>>>>>>>>> one >>>>>>>>>>>>>> file >>>>>>>>>>>>>> for each >>>>>>>>>>>>>> package. It's impossible for me to create 100+ DTS >>>>>>>>>>>>>> packages >>>>>>>>>>>>>> with >>>>>>>>>>>>>> the >>>>>>>>>>>>>> actual >>>>>>>>>>>>>> source filename. Please help, I'm greatly appreciated for >>>>>>>>>>>>>> your >>>>>>>>>>>>>> help. >>>>>>>>>>>>>> Sincerely
I truly didn't get an error. Instead, I got msg "Successfully completed execution of package". Which means all six tables in file1.mdb have imported over to SQL DB. I clicked OK button then view the Step and Status, so two steps showed not run: "Bad Directories" and "Finished". Careless about step task "Bad Directories"; it was good when that task didn't run. The issue with the task "Finished", it didn't run because there were still more files in the source folder. Task "Finished" = true with only condition when the source folder is empty. According to your example counter = fold.files.count 'So long as there is more than 1 file carry on if counter >= 1 then for each fil in fold.Files DTSGlobalVariables("gv_FileFullName").Value = fil.path ShouldILoop = CBool(True) Next else ShouldILoop = CBool(False) End if Thank you, -- NT [quoted text, click to view] "Allan Mitchell" wrote: > Hello NT, > > So not only do you want to import more than one access DB but in the DB you > want to import more than one table. > > What is the error you get? > > Doing the loop inside the loop is fine but will involve a lot of coding if > you want the table you import/export to be dynamic. > > -- > > Allan Mitchell > http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com | > http://www.konesans.com > > > Hi Allan, > > > > I have checked and assured that the name DTSStep_DTSActiveScriptTask_3 > > is correct since I kept the same original name from your example. > > Moreover, it actually worked if I select to import only one table in > > mdb file. So, the loop around did work; it went back to begin loop = > > DTSStep_DTSActiveScriptTask_3 to take the next mdb file then do the > > data import task, continue looping until the last mbd file in the > > folder then execute the finished loop. However, it failed and the > > Finished module never ran if I select to import more than one table. > > > > I hope it would not confuse you. > > > > Thanks, > > > > "Allan Mitchell" wrote: > > > >> Hello NT, > >> > >> What is probably happening is that the step I mention > >> DTSStep_DTSActiveScriptTask_3 is not the same in your package. Go to > >> the Workflow properties and find out the step name of your equivalent > >> step > >> > >> -- > >> > >> Allan Mitchell > >> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com > >> | > >> http://www.konesans.com > >>> Hi Alan, > >>> > >>> Thanks for your help...! I finally made it works with the way I > >>> wanted for the single table selected; however, if I selected to > >>> import multiple tables in the mdb file then the following statement > >>> from Loop Around seemed to fail. > >>> > >>> set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3") > >>> 'The trick to looping in DTS is to set the step at the start of the > >>> loop to > >>> an execution status of waiting > >>> stpbegin.ExecutionStatus = DTSStepExecStat_Waiting > >>> "NT" wrote: > >>> > >>>> Hi Allan, > >>>> > >>>> Sorry for the late response, I just got back from my trip. Anyway, > >>>> It have not worked yet but I will try again and keep you posted if > >>>> you're still interested. > >>>> > >>>> Thanks for your help, > >>>> -- > >>>> NT > >>>> "Allan Mitchell" wrote: > >>>>> Hello NT, > >>>>> > >>>>> So you have the package looping correctly > >>>>> You have the Transform Data task set up. > >>>>> What is the error you get? > >>>>> -- > >>>>> Allan Mitchell > >>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>> http://www.sqldts.com > >>>>> | > >>>>> http://www.konesans.com > >>>>>> Hi Allan, > >>>>>> > >>>>>> I got your email, thanks for your response... > >>>>>> I know the file extension .res doesn't look like MDB but it is. > >>>>>> We > >>>>>> have to > >>>>>> use MS Access 2K or 2003 version to open. I currently have about > >>>>>> 200+ > >>>>>> .res > >>>>>> files need to be imported onto SQL Server database. These *.res > >>>>>> files > >>>>>> have > >>>>>> been generated from the 3rd party vendor system. There are 10 > >>>>>> tables > >>>>>> in each > >>>>>> .res file and all .res files have exactly the same table > >>>>>> structure > >>>>>> design, > >>>>>> except the data diff. > >>>>>> I'm very beginner for this task, so I need a lot of help...It's > >>>>>> greatly appreciated for any help. > >>>>>> Best regards, > >>>>>> "Allan Mitchell" wrote: > >>>>>> > >>>>>>> Hello NT, > >>>>>>> > >>>>>>> Got it thanks. Will look tonight > >>>>>>> -- > >>>>>>> Allan Mitchell > >>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>> http://www.sqldts.com > >>>>>>> | > >>>>>>> http://www.konesans.com > >>>>>>>> Please check your email again... > >>>>>>>> > >>>>>>>> "Allan Mitchell" wrote: > >>>>>>>> > >>>>>>>>> Hello NT, > >>>>>>>>> > >>>>>>>>> remove no-spam > >>>>>>>>> > >>>>>>>>> -- > >>>>>>>>> > >>>>>>>>> Allan Mitchell > >>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>>> http://www.sqldts.com > >>>>>>>>> | > >>>>>>>>> http://www.konesans.com > >>>>>>>>>> I have sent to this address allan@no-spam.sqldts.com. Is it > >>>>>>>>>> right? > >>>>>>>>>> > >>>>>>>>>> "Allan Mitchell" wrote: > >>>>>>>>>> > >>>>>>>>>>> Hello NT, > >>>>>>>>>>> > >>>>>>>>>>> You will need to modify my eMail slightly to get it to work. > >>>>>>>>>>> > >>>>>>>>>>> -- > >>>>>>>>>>> > >>>>>>>>>>> Allan Mitchell > >>>>>>>>>>> http://wiki.sqlis.com | http://www.sqlis.com | > >>>>>>>>>>> http://www.sqldts.com > >>>>>>>>>>> | > >>>>>>>>>>> http://www.konesans.com > >>>>>>>>>>>> Hi Allan, > >>>>>>>>>>>> > >>>>>>>>>>>> I'm really bad...I still could not make it work even with a > >>>>>>>>>>>> big help from you. Here is my modification if you would > >>>>>>>>>>>> help...Thanks buch! I sent my .dts to your email > >>>>>>>>>>>> > >>>>>>>>>>>> Thanks, > >>>>>>>>>>>> > >>>>>>>>>>>> "Allan Mitchell" wrote: > >>>>>>>>>>>> > >>>>>>>>>>>>> Hello NT, > >>>>>>>>>>>>> > >>>>>>>>>>>>> So your loop simply changes to looping over MDBs > >>>>>>>>>>>>> Your assignment of found MDB changes to be a different |