Gert,

I am currently using the DTSPackage library. I am
instantiating it inside of Microsoft Navision using
C/SIDE code. I seem to have some problems with
datatyping, etc. and I seem unable to use SQL-NS
anyway. I wanted to use SQL-NS because the distribution
of this app is limited so licensing is not an issue. I
wanted to give the user the same interface as though they
were running DTS from SQLEM, as it is more informative
and the large footprint is a small price to pay. I dont
know if you are familiar with C/SIDE, Navision, and SQL-
NS, but I know SQL-NS requires strong typing. Any help
you could give would be welcomed.

Right now I am using DTSPackage Step properties to return
a completion status and report it to the user. This
works if the package succeeds (by the way the package is
an Analysis Services Processing Task), but it blows up
when it errors with a modal message, instead of writing a
Step Failed event.

I have also seen instances where I have executed the
package from this environment, it shows success, but
there was an error that was not returned to the client.
I need to make this stable. Please see the attached
code, if you could review it I would be grateful.


LoadPackage(ServerName : Text[50];PackageName : Text[100])

CLEARALL;
CREATE(DTSPackage);
DTSPackage.LoadFromSQLServer
(ServerName,'','',256,'','','',PackageName);
DTSPackage.LogServerFlags := 256;
DTSPackage.LogToSQLServer := TRUE;
DTSPackage.LogServerName := ServerName;
ExecuteSteps(DTSPackage);

DTSPackage.UnInitialize;

ExecuteSteps(Package : Automation "'Microsoft DTSPackage
Object Library'.Package2")
TempRec.DELETEALL;
PrimaryKey := FORMAT(TODAY()) + FORMAT(TIME());
CompanyInfo.GET();
IF CompanyInfo."Enable Logging" THEN BEGIN
strConn :='Provider=sqloledb;Data Source=' +
CompanyInfo."Reporting Server" + ';Initial Catalog=';
strConn := strConn + 'msdb' +';Integrated Security=SSPI';
strSQL := 'tblNavisionSQLLog';
CREATE(rs);
CREATE(cn);
cn.Open (strConn);
cn.CommandTimeout := 120;
rs.Open (strSQL,cn,2,2);
END;


FOR i := 1 TO Package.Steps.Count DO BEGIN
Step := Package.Steps.Item(i);
Step.Execute;
IF (Step.ExecutionResult = 0) THEN BEGIN
TempRec.INIT;
TempRec."Primary Key" := FORMAT(i);
TempRec.Text1 := FORMAT(Step.Description);
TempRec.Text4 := 'Step Successful';
TempRec.Text2 := FORMAT(Step.TaskName);
CASE Step.ExecutionStatus OF
4:
TempRec.Text3 := 'Completed';
3:
TempRec.Text3 := 'Inactive';
2:
TempRec.Text3 := 'In Progress';
1:
TempRec.Text3 := 'Waiting';
END;
CompanyInfo.GET;
IF CompanyInfo."Enable Logging" THEN BEGIN
rs.AddNew;
rs.Fields.Item('LogEntry').Value := FORMAT
(PrimaryKey);
rs.Fields.Item('ObjName').Value := FORMAT
(Package.Name);
rs.Fields.Item('Type').Value := 'DTS';
rs.Fields.Item('ExecDate').Value := TODAY();
rs.Fields.Item('Operator').Value := USERID();
rs.Fields.Item('ErrorCode').Value :=
Step.ExecutionResult;
rs.Fields.Item('ErrorDescription').Value :=
TempRec.Text4 ;
rs.Fields.Item('Status').Value := TempRec.Text3;
DescString := TempRec.Text1 + '-' + TempRec.Text4;
rs.Fields.Item(2).Value := FORMAT(DescString);
rs.Update;
END;

TempRec.INSERT;
END ELSE BEGIN
TempRec.INIT;
TempRec."Primary Key" := FORMAT(i);
TempRec.Text1 := FORMAT(Step.Description);
TempRec.Text4 := 'Step Failed';
TempRec.Text2 := FORMAT(Step.TaskName);
CASE Step.ExecutionStatus OF
4:
TempRec.Text3 := 'Completed';
3:
TempRec.Text3 := 'Inactive';
2:
TempRec.Text3 := 'In Progress';
1:
TempRec.Text3 := 'Waiting';
END;
CompanyInfo.GET;
IF CompanyInfo."Enable Logging" THEN BEGIN
rs.AddNew;
rs.Fields.Item('LogEntry').Value := FORMAT
(PrimaryKey);
rs.Fields.Item('ObjName').Value := FORMAT
(Package.Name);
rs.Fields.Item('Type').Value := 'DTS';
rs.Fields.Item('ExecDate').Value := TODAY();
rs.Fields.Item('Operator').Value := USERID();
rs.Fields.Item('ErrorCode').Value :=
Step.ExecutionResult;
rs.Fields.Item('ErrorDescription').Value :=
TempRec.Text4;
rs.Fields.Item('Status').Value := TempRec.Text3;
DescString := TempRec.Text1 + '-' + TempRec.Text4;
rs.Fields.Item(2).Value := FORMAT(DescString);
rs.Update;

END;
TempRec.INSERT;
END;
END;
TempRec.INIT;
TempRec."Primary Key" := 'Seq';
TempRec.Text1 := 'Description';
TempRec.Text2 := 'Name';
TempRec.Text3 := 'Message';
TempRec.Text4 := 'Result';
TempRec.Text5 := 'Elapsed Time';
TempRec.HeaderRec := TRUE;
TempRec.INSERT;

FORM.RUN(50190,TempRec);

I created my own logging system because when I execute
this package from this environment, even if logging is
permanently enabled on the package it does not write to
the sysdtspackagelog table

Well I do not know anything about Navision or the C/SIDE language.

I am confused by the way you want to execute your packages, it seems off to
me to execute step by step. Logging will write to the tables for each step,
if that does not work you have more problems. I would never use SQLNS do
accomplish this.

Since you are part of Navision, contact me directly on my Microsoft email
and we can look in to the details.

GertD (AT) SQLDev (DOT) Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.

"Devin McMahon" <dmcmaho1 (AT) twcny (DOT) rr.com> wrote