I'm trying to create a local package that will back a set of SQL Server tables up to a local Access file on a regular basis. The goal is to have the DTS job create a new access file each day, then backup the tables from SQL Server to Access.
I used Enterprise Manager to Export the data from SQL to Access, and had it save it as a local DTS Package. That produces something like what you'll see in the attached graphic.
I also know how to make an ActiveX (VBScript) component that will make the Access file for me, and set each of the destination data sources to point to the access file.
The question is, how do I put them together?
How do I get that ActiveX task to execute before any of the others? I could set each of the table copy tasks to be dependent on the ActiveX task, but there are hundreds of tables, and that will take forever.
I don't get exactly what you are asking. Are you trying to make those 3 steps execute one after another? What about putting a workflow--> on success between them.
yes you want to click on the first task, then hold ctrl and click on the 2nd task.. then go up to workflow and hit "On Success" (can't remember the name of the toolbar link)
__________________
<< Insert exceedingly large and overly verbose message of how 1337 you are here including full specs of every vehicle you've ever driven and PC you've owned >>
Thanks. I understand the Workflow -> On Success approach. My issue is that there are well over 100 tables, and it would take me several hours to set up all of those relationships.
As it stands now, there are no success or failure dependencies on the job, and yet it does seem to execute in a predictable order. I'm just not clear on what that order is.
Do job execute from Left to Right? Right to Left? Top to Bottom?
I hope not. Since Enterprise Manager created the initial set of commands, and I tacked on the ActiveX step, that would mean the things I want to run first, would actually run last!
You can export the DTS package into a VBS file. Maybe export it, put it in the order YOU want it in, put in some IF statements
(Package --> Save As.. for filetype change it from SQL Server to VB File)
__________________
<< Insert exceedingly large and overly verbose message of how 1337 you are here including full specs of every vehicle you've ever driven and PC you've owned >>
Linear Mode
