DTS Designer/DTSrun problem

  • Ok, if I run this package in the DTS Designer, it runs fine. If I run this package from the DTSrun prompt, it fails and gives the following output.

    This has been running fine for a long time, so I know it's nothing to do with security at the dos level on my end. I am pulling from another ODBC datasource (non SQL Server) and dumping to a text file.

    DTSRun: Loading...

    DTSRun: Executing...

    DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1

    DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1

    DTSRun OnStart: DTSStep_DTSDataPumpTask_1

    DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000

    DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000

    . . . (omitted lines here)

    DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 19000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 19000

    DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147221499 (80040005)

    Error string: Need to run the object to perform this operation

    Error source: Microsoft Data Transformation Services (DTS) Package

    Help file: sqldts80.hlp

    Help context: 1100

    Error Detail Records:

    Error: -2147221499 (80040005); Provider Error: 0 (0)

    Error string: Need to run the object to perform this operation

    Error source: Microsoft Data Transformation Services (DTS) Package

    Help file: sqldts80.hlp

    Help context: 1100

    Error: -2147221499 (80040005); Provider Error: 0 (0)

    Error string: Need to run the object to perform this operation

    Error source: Microsoft Data Transformation Services (DTS) Package

    Help file: sqldts80.hlp

    Help context: 4700

    Error: -2147221499 (80040005); Provider Error: 0 (0)

    Error string: Code execution exception: EXCEPTION_ACCESS_VIOLATION

    Error source: Microsoft Data Transformation Services (DTS) Data Pump

    Help file: sqldts80.hlp

    Help context: 0

    DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

    Error: -2147220440 (80040428); Provider Error: 0 (0)

    Error string: Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.

    Error source: Microsoft Data Transformation Services (DTS) Package

    Help file: sqldts80.hlp

    Help context: 700

    The log file of SQL Agent has the following error row :

    2008-09-23 11:37:38 - ! [298] SQLServer Error: 446, Cannot resolve collation conflict for equal to operation. [SQLSTATE 42000]

    I am not doing any kind of join, just a simple, single table select statement, with no joins or even a where clause. I'm just pulling all the rows with no calculations on the columns. I am also dumping to a txt file, so I know there's no problem with an table on my end.

    There are multiple char columns in the table I'm pulling from, but I've got it isolated down to one column. If I include that column, the job fails. If I don't include that column, the job succeeds.


    Live to Throw
    Throw to Live
    Will Summers

  • Sounds like there could be some kind of funny character in that column somewhere...

    I ran into a similar problem using an RMS database as an ODBC datasource. There were a number of non printable Ascii characters that caused me issues that I had to remove before I could load it up into my database.

    Could be perhaps it's a character that the ODBC driver just can't handle. any chance you can query that column for records that contain things other than alpha numeric values and then exclude those rows to see if it works? If you said this is something that's just begun to happen you'd think you'd be able to narrow it down to rows recently added /updated.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • I just remembered the bit about it working fine in DTS Designer but not DTSRUn. any chance they are using different versions of the same driver? when I haad issues liek this it was when getting data from RMS and pushing it to a PostgreSQL db. I upgraded my Postgres driver and it started having issues I'd never seen before. I rolled the driver back and all was well...

    According to MS it was something with ODBC conformance levels or some such and they basically said they couldn't support it.

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • First off, thanks for the quick replies.

    I have looked at the column after pulling it down into a table, a text file and into an access database. The field is a char(1) and there is just a 'Y' in the field. It doesn't look any different than the row before or after.

    I have only installed one version of the driver on that server. I don't think I have ever even done a uninstall of the driver either. Only one installation.

    If I go through the DTS designer, I can pull the whole table no problem into a text or sql server table. If I exclude the column, then everything runs fine in the designer and through the DTSrun prompt.


    Live to Throw
    Throw to Live
    Will Summers

  • Luke L (9/23/2008)


    Sounds like there could be some kind of funny character in that column somewhere...

    This was my first guess, but why would it work in the designer and not the DTSrun?


    Live to Throw
    Throw to Live
    Will Summers

  • I can pull the whole table no problem into a text or sql server table.

    Have you verified that you get every row and that it doesn't just toss a bad record out?

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • I have pulled the whole table to a text file and a sql server table through the DTS designer and the data looks fine.


    Live to Throw
    Throw to Live
    Will Summers

  • Here's a suggestion:

    The problem may arise if you have different versions of SQLServer installed on the same box, or even if you have different versions of DTSRun in different directories from installing a backwards compatibility patch.

    So, when you run the package from Designer, it executes with the correct DTSRun version, but when you call it from SQLServer it executes with a different version.

    You can try changing the order of the directories in the PATH system parameter or you can include the full path to the DTSRun.exe in the job step. I find it easier to include the full path in the job step.

    Kurt Soutendijk
    Sr SQL Server DBA/Developer

Viewing 8 posts - 1 through 7 (of 7 total)

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