MS SQL Server DTS - running DTS package in .Net-- no errors, but nothing happens?

Author

running DTS package in .Net-- no errors, but nothing happens?

Jim Bancroft

2005-07-13, 8:24 pm

I'm stumped. I've ported the DTS library to my C# application (using the
tips listed at http://SQLDev.Net/DTS/DotNETCookBook.htm) and rewrote my VB6
DTS package code in C#. However, when I execute it nothing happens.

I turned on event log publishing with the " WriteCompletionStatu
sToNTEventLog
" flag, but all I got was this informational message in the event log:

-------------------
The execution of the following DTS Package succeeded:

<<snipped for brevity>>

Total Execution Time: 0 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPump
Task_1' was not executed
Step 'DTSStep_DTSExecuteS
QLTask_1' was not executed
------------------

I've cut out the boilerplate stuff at top, but rest assured no errors were
reported.

My DTS code is a straight port from what I had in VB6 (which was generated
by the SQL 2000 wizard), with the same flags and everything else. For
instance, here's the C# definition of one of the two steps referred to
above:

DTS.Step2 oStep;
oStep = (DTS.Step2) goPackage.Steps.New();
oStep.Name = " DTSStep_DTSDataPumpT
ask_1";
oStep.Description = "Transform Data Task: undefined";
oStep.ExecutionStatus = DTS.DTSStepExecStatus. DTSStepExecStat_Wait
ing;
oStep.TaskName = " DTSTask_DTSDataPumpT
ask_1";
oStep.CommitSuccess = false;
oStep.RollbackFailure = true;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.RelativePriority =
DTS. DTSStepRelativePrior
ity. DTSStepRelativePrior
ity_Normal;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = false;
oStep.IsPackageDSORowset = false;
oStep. JoinTransactionIfPre
sent = true;
oStep.RollbackFailure = true;
goPackage.Steps.Add(oStep);

When it comes time to run the package's Execute() method, it races through,
like it's not even trying.

My DTS code is surrounded in a try-catch statement, and no exceptions are
raised. As an aside, I'm a little concerned with the "ScriptLanguage"
attribute above (not using VBScript in .Net after all), but there's no
"VB.Net" or "C#" attribute in DTS.

I know it's difficult to diagnose what's happening given the incomplete
package I've shown you above....what I was hoping for was general guidance,
or maybe a pointer in the right direction with regard to getting all this to
work in .Net. Has anyone managed to do what I'm trying, and are there
additional debugging steps I can attempt? Thanks for the assistance!

Jim Bancroft

2005-07-20, 1:23 pm

Ok, I found the answer on my own. In the hopes that this helps someone in
the future, I accidentally set the "IsPackageDSORowset" flag to true when I
ported the code, whereas it had been false earlier. That's all it took...

"Jim Bancroft" <asdfsklw@nowhere.com> wrote in message
news:OWgshwAiFHA.2424@TK2MSFTNGP09.phx.gbl...
> I'm stumped. I've ported the DTS library to my C# application (using the
> tips listed at http://SQLDev.Net/DTS/DotNETCookBook.htm) and rewrote my
> VB6 DTS package code in C#. However, when I execute it nothing happens.
>
> I turned on event log publishing with the
> " WriteCompletionStatu
sToNTEventLog " flag, but all I got was this
> informational message in the event log:
>
> -------------------
> The execution of the following DTS Package succeeded:
>
> <<snipped for brevity>>
>
> Total Execution Time: 0 seconds
> Package Steps execution information:
> Step 'DTSStep_DTSDataPump
Task_1' was not executed
> Step 'DTSStep_DTSExecuteS
QLTask_1' was not executed
> ------------------
>
> I've cut out the boilerplate stuff at top, but rest assured no errors were
> reported.
>
> My DTS code is a straight port from what I had in VB6 (which was generated
> by the SQL 2000 wizard), with the same flags and everything else. For
> instance, here's the C# definition of one of the two steps referred to
> above:
>
> DTS.Step2 oStep;
> oStep = (DTS.Step2) goPackage.Steps.New();
> oStep.Name = " DTSStep_DTSDataPumpT
ask_1";
> oStep.Description = "Transform Data Task: undefined";
> oStep.ExecutionStatus = DTS.DTSStepExecStatus. DTSStepExecStat_Wait
ing;
> oStep.TaskName = " DTSTask_DTSDataPumpT
ask_1";
> oStep.CommitSuccess = false;
> oStep.RollbackFailure = true;
> oStep.ScriptLanguage = "VBScript";
> oStep.AddGlobalVariables = true;
> oStep.RelativePriority =
> DTS. DTSStepRelativePrior
ity. DTSStepRelativePrior
ity_Normal;
> oStep.CloseConnection = false;
> oStep.ExecuteInMainThread = false;
> oStep.IsPackageDSORowset = false;
> oStep. JoinTransactionIfPre
sent = true;
> oStep.RollbackFailure = true;
> goPackage.Steps.Add(oStep);
>
>
> When it comes time to run the package's Execute() method, it races
> through, like it's not even trying.
>
> My DTS code is surrounded in a try-catch statement, and no exceptions are
> raised. As an aside, I'm a little concerned with the "ScriptLanguage"
> attribute above (not using VBScript in .Net after all), but there's no
> "VB.Net" or "C#" attribute in DTS.
>
> I know it's difficult to diagnose what's happening given the incomplete
> package I've shown you above....what I was hoping for was general
> guidance, or maybe a pointer in the right direction with regard to getting
> all this to work in .Net. Has anyone managed to do what I'm trying, and
> are there additional debugging steps I can attempt? Thanks for the
> assistance!
>
>
>

Sponsored Links