Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: errors from a child dts package

    I am executing a child dts package from an activex script. While I get no errors when executing the child package independently, I get errors when executing it thru an activex script. Can anyone help?

    Also, can anyone give me a sample code to display error messages from each step in a log file ?
    Thanks

    Here is the activex script I use:

    '************************************************* *********************
    ' Visual Basic ActiveX Script
    '************************************************* ***********************
    Function Main()

    Main = DTSTaskExecResult_Success
    Dim SQLConn
    Dim DB2Conn
    SQLStr = "DRIVER=SQL Server;SERVER=GPDSQLIRV01;DATABASE=DailySales"
    DB2Str = "DSN=SYSHDB2P"

    Set SQLConn = CreateObject("ADODB.Connection")
    Set DB2Conn = CreateObject("ADODB.Connection")

    SQLConn.Open SQLStr, "script", "script"
    DB2Conn.Open DB2Str, "hgrprd1", "ddcsgr1"

    db2sql = "SELECT MAX(INSERT_TIME) AS INSERT_TIME FROM H.SA48 WHERE REC_TYPE = 'D' "
    set db2sqltxt48 = db2conn.execute(db2sql)

    SSsql = "SELECT INSERT_TIME FROM SA48_date_tbl"
    set SSsqltxt48 = sqlconn.execute(SSsql)

    db2sql49 = "SELECT AS_OF_DT FROM H.SA49 "
    set db2sqltxt49 = db2conn.execute(db2sql49)

    SSsql49 = "SELECT AS_OF_DT FROM SA49_date_tbl"
    set SSsqltxt49 = sqlconn.execute(SSsql49)



    If db2sqltxt48("insert_time") <> SSsqltxt48("insert_time") And db2sqltxt49("as_of_dt") <> SSsqltxt49("as_of_dt") then

    Dim oPkg, oStep
    Dim sServer, sUID, sPWD, iSecurity , sPkgPWD, sPkgName, sErrMsg
    Set oPkg = CreateObject("DTS.Package")

    ' Assign parameters
    sServer = "GPDSQLIRV01"
    sUID = "script"
    sPWD = "script"
    iSecurity = DTSSQLStgFlag_UseTrustedConnection
    sPkgPWD = ""
    sPkgName = "DTS_Load_ProductTables"

    ' Load Child Package
    oPkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity , sPkgPWD, "", "", sPkgName


    oPkg.Execute

    For Each oStep In oPkg.Steps
    If oStep.ExecutionResult = DTSStepExecResult_Failure Then
    Main = DTSTaskExecResult_Failure
    MsgBox "hello"
    End If
    Next

    oPkg.Uninitialize
    Set oStep = Nothing
    Set oPkg = Nothing


    End If

    db2sqltxt48.close
    SSsqltxt48.close
    db2sqltxt49.close
    SSsqltxt49.close

    db2conn.close
    sqlconn.close

    Set db2sqltxt48 = Nothing
    Set SSsqltxt48 = Nothing
    Set db2sqltxt49 = Nothing
    Set SSsqltxt49 = Nothing

    Set db2conn = Nothing
    Set sqlconn = Nothing


    End Function

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: errors from a child dts package

    .......and the reason most of this isn't a stored procedure is?

    What's the p[ackage suppose to do?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Re: errors from a child dts package

    Originally posted by Brett Kaiser
    .......and the reason most of this isn't a stored procedure is?

    What's the p[ackage suppose to do?
    Brett

    The child package has several steps. Each step is downloading data from a DB2 table on the mainframe to a corresponding table on the SQLServer

    Thanks

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: errors from a child dts package

    Are you doing a lot of manipulation?

    Is the whole process built already?

    I usually transfer everything to a staging enviroment and then manipulate the data after it migrated.

    Just seems easier..

    As fa as logging errors, can you echo them out to a text file...do inserts in to a table might not work in the evnt you have to roll back..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Posts
    3

    Re: errors from a child dts package

    Originally posted by Brett Kaiser
    Are you doing a lot of manipulation?

    Is the whole process built already?

    I usually transfer everything to a staging enviroment and then manipulate the data after it migrated.

    Just seems easier..

    As fa as logging errors, can you echo them out to a text file...do inserts in to a table might not work in the evnt you have to roll back..
    Brett
    No, no processing at all. Just select from the table on the mainframe and insert into the one on the SQLSERVER.
    Regarding error messages coming from each step of the child package, can you give some sample code to capture the error code and message, and echo it to a text file in the parent package?

    Thanks
    Anil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •