Post reply

DTS package transformation to commandline

  • November 3, 2006 at 10:35 pm

    #61567

    Hi,

    I have a standard request of copying tables from oracle and sql server.

    What I did is,

    1. created an ODBC connection from the target server to the initial server.

    2. Created a DTS job from the initial server to the target server

    3. backup current production, cleanup production and othe restore

        from backup of DTS fails.

     

    I have traced the error back to dts.

     

    The DTS package works perfectly if I run from enterprise manager.

    If I run it through a job, it says it succeeds but no data is copied over;

    I ran dtsrunui to convert to get the command it uses.

    Copied all these tasks to the job and tried running it together.

    Can anybody help?

     

    Thanks

    Murali

     

     

     

  • November 6, 2006 at 4:45 am

    #670007

    at a rough guess I'd say permissions.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • November 6, 2006 at 7:36 am

    #670038

    Thanks to all.

    I do write the job output onto the dts log file and there was always the DRIVER error.

    It turned out that I have defined the ODBC connectivity as  a User DSN and DTS did not like it eventhough the logged in user was the same, the job was defined using the same user and the DTS owner was the same user.

    I redefined the same ODBC connectivity as a SYSTEM DSN and voila, it works without any issues!!!!

    Problem solved. Thanks all for your inputs and lessons learned.

     

    Murali

     

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply