Workflow confused

#1

Hi,
Thank you for the quick response earlier, but I'm still having a little trouble. My DTS Package has a Transform Data Task (TDT) between two connections, i then have an Execute SQL Task (EST) to delete the records in the source table if the TDT is successful, but i can't figure out how to stop the TDT if the EST doesn't manage to delete the contents of the table, the TDT still sends the records and then sends them again in 10 mins when it executes again, but the EST isn;t always successful, the table is sometimes locked for the input of new records.
The package is executed from VB code.
Tearing my hair out.
Much obliged

#2

OK

Let me get this straight.

You move some data from A --> B. On success you then delete this data from
A

If the delete fails (for what reason?) then you want to what?

1. Rollback the rows that were moved
2. Stop the task executing next time

You are executing this from VB right? Why can VB not maintain the state of
what happend after checking for any errors in the package?

What are you going to do if the delete fails?

Manually fix things?

You can build this into a transaction and have the Data movement rolled
back. You will still need to sort out why the delete did not happen though.

--

----------------------------

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

"Gary Spence" <GarySpence (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,
Thank you for the quick response earlier, but I'm still having a little
trouble. My DTS Package has a Transform Data Task (TDT) between two

connections, i then have an Execute SQL Task (EST) to delete the records in
the source table if the TDT is successful, but i can't figure out how to
stop the TDT if the EST doesn't manage to delete the contents of the table,
the TDT still sends the records and then sends them again in 10 mins when it
executes again, but the EST isn;t always successful, the table is sometimes
locked for the input of new records.

Quote:

The package is executed from VB code.
Tearing my hair out.
Much obliged

#3

In article <94299408-76C2-4D5B-BB9B-DD30855FC788 (AT) microsoft (DOT) com>, Gary Spence wrote:

Quote:

Thanks for the previous replies, do these properties for my package object look ok for failing the transaction if the deletion task fails.

'Set the properties for the package.
With goPackage
.Name = "DataTransferTo400"
.Description = "Data Transfer To AS 400"
.WriteCompletionStatusToNTEventLog = False
.FailOnError = True
.PackagePriorityClass = 2
.MaxConcurrentSteps = 4
.LineageOptions = 0
.UseTransaction = True
.TransactionIsolationLevel = 4096
.AutoCommitTransaction = True
.RepositoryMetadataOptions = 0
.UseOLEDBServiceComponents = True
.LogToSQLServer = False
.LogServerFlags = 0
.FailPackageOnLogFailure = False
.ExplicitGlobalVariables = False
.PackageType = 0
'If there's an error then log it to this file.
.LogFileName = "C:\Development Projects\DTS Error Logs\DTSErrorLog.txt"
End With

and these for my step object :

Set oStep = goPackage.Steps.New

'Set the properties
With oStep
.Name = "DTSStep_DTSExecuteSQLTask_1"
.Description = "Delete the buffer"
.ExecutionStatus = 1
.TaskName = "DTSTask_DTSExecuteSQLTask_1"
.CommitSuccess = False
.RollbackFailure = True
.ScriptLanguage = "VBScript"
.AddGlobalVariables = True
.RelativePriority = 3
.CloseConnection = False
.ExecuteInMainThread = False
.IsPackageDSORowset = False
.JoinTransactionIfPresent = False
.DisableStep = False
.FailPackageOnError = True
End With

Much obliged Allen

I'll tell you the easiest way to get to know the object model in VB is to save the package to a VB .bas file. So I would create the package
as you would like it to be and then see what the DTS package generates.

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

#4

Please note that i am using MSDE on Microsoft XP machine.
--
Regards
Bharat Jariwala

"Gary Spence" wrote:

Quote:

Hi,
Thank you for the quick response earlier, but I'm still having a little trouble. My DTS Package has a Transform Data Task (TDT) between two connections, i then have an Execute SQL Task (EST) to delete the records in the source table if the TDT is successful, but i can't figure out how to stop the TDT if the EST doesn't manage to delete the contents of the table, the TDT still sends the records and then sends them again in 10 mins when it executes again, but the EST isn;t always successful, the table is sometimes locked for the input of new records.
The package is executed from VB code.
Tearing my hair out.
Much obliged

#5

Hello Bharat,

So you pump rows from A --> B

If that is successful you delete the source rows.

Your package then starts again?

1. Why? You could have an external process that makes the decision to run
the package or not.
2. if the delete does not happen then the source will have the rows and
the destination also. i presume you only want to take "New" rows? if you
do then perform a check based on the keys for the rows and this will tell
you if the row is already in the destination.
3. You could have an ExecuteSQL task that counted the rows in the source
not in the destination. An active script task would then check this number.
If it was greater than 0 then the datapump would run and export new rows.
If not then it would disable the data pump task

Basically you need to perform a check up front of the DataPump task to decide
if you want to precede

Allan

Quote:

Please note that i am using MSDE on Microsoft XP machine.

"Gary Spence" wrote:

Hi,

Thank you for the quick response earlier, but I'm still having a
little trouble. My DTS Package has a Transform Data Task (TDT)
between two connections, i then have an Execute SQL Task (EST) to
delete the records in the source table if the TDT is successful, but
i can't figure out how to stop the TDT if the EST doesn't manage to
delete the contents of the table, the TDT still sends the records and
then sends them again in 10 mins when it executes again, but the EST
isn;t always successful, the table is sometimes locked for the input
of new records.

The package is executed from VB code.

Tearing my hair out.

Much obliged

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Workflow confused

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Workflow confused - 07-08-2004 , 07:02 AM

Re: Workflow confused - 07-08-2004 , 07:24 AM

Re: Workflow confused - 07-08-2004 , 10:36 AM

RE: Workflow confused - 01-11-2006 , 06:21 AM

RE: Workflow confused - 01-11-2006 , 01:24 PM