October 13, 2003 at 10:27 am
I have been tasked with creating a DTS package or set of packages which copies a subset of tables and a subset of the data in tables which exists in multiple databases to another server with the has the same databases. The new server.databases represent a test platform so they needs to be rebuild after each test.
I have 4 main steps which I need executed. Each main step is identified by its own package which in turn executes other packages.
The first step drops the old databases and created a new ones. It also sets up linked servers. This step actually executes other DTS Packages each of which build one of the databases.
The second step copies all required table objects to the new database. Only table structures are copied. No data or indexes. This step actually executes other packages which create the tables required for each database.
The third step copies the data from specified tables, where the entire table is to be copied, to the test tables. This step actually executes other tasks that represent the databases in which the tables exist. Further, these database level tasks call other tasks which copy the actual table.
In the database level tasks is where I am having my problem.
I have created a package for each Table transfer (Copy SQL Server Object) with only the “Copy Data” check box and the “Replace Existing Data” check box selected. Further I have deselected the “Copy All Objects” check box and used the “Select Objects” button to allow me to select a single table. Additionally I have deselected the “Use default options” check box and selected a set of my own options.
In the Database level package I used Execute Package tasks to execute each of the above mentioned packages. If I connect each package with an “On Completion” workflow indicator everything works just fine, although a bit slower than I’d wish. When I remove the workflow objects DTS should attempt to execute as many steps as it can (up to 4 at once) asynchronously. Here is where I have the problem. Some of the steps fail because one of the Scripting files in the Copy Table Data packages “are being used by another user”. The Scripting files are named Server.database.TAB, server.database.DB1, etc and the location is specified in the Scripting File Directory of the Copy Table Data Package using the copy tab.
I’ve made sure each Copy Table Data points to its own scripting file directory so there should be no file contention, yet there is.
Can anyone help? If you need to ask more questions please do and I will answer as best I can.
Thanks,
October 16, 2003 at 8:00 am
This was removed by the editor as SPAM
October 17, 2003 at 4:04 am
Not sure I follow, but seems like simple locking. An earlier step is creating the file and hasnt released it when the later step goes to use it. If slightly slower package execution is all it takes to fix it, I think I'd be ok with that.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply