Transformation Task Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Transformation Task Error

Author

Topic

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2005-10-14 : 05:06:25

Hi All,

I have a DTS package that export the a huge records from SQL 2000 to Microsoft Access 2000. Inside the DTS package, I have 7 Transformation Tasks to transform each individual table records into the Access. When I run the package manually, I encountered an error in some of the Transformation Task. The error that I encountered is as follow

Step 'DTSStep_DTSDataPumpTask_2' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. (Microsoft JET Database Engine (80004005): Invalid argument.)
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Step Execution Started: 10/13/2005 5:31:16 PM
Step Execution Completed: 10/13/2005 5:56:17 PM
Total Step Execution Time: 1501.156 seconds
Progress count in Step: 0

When I check my Access file size, it is about 2,097,152KB. But, the process is still not success. I check all my tasks and can't find any problem.

Any idea what wrong over here?

Thank you in advance for the help.

Regards,
William

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-14 : 05:50:56

check the 'DTSStep_DTSDataPumpTask_2', Invalid argument

--------------------
keeping it simple...

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2005-10-17 : 00:16:06

Hi Jen,

Thank you for your reply.

DTSStep_DTSDataPumpTask_2 is a simple SQL query. I try to run that query in the SQL Query Analyzer, no problem. It can successfully generate the result for me.

Any idea why is it fail?

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-17 : 01:08:13

From my experience, Try to execute the package from the DTS Designer window, When the task fails the error message displayed there is usually much more clear.

Karunakaran

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-17 : 03:13:35

can you post the query?

--------------------
keeping it simple...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-17 : 03:27:13

Since he gets the results in QA, I assume there might be invalid data coming in between during the transformation.

Karunakaran

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-17 : 03:40:11

yep

--------------------
keeping it simple...

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2005-10-17 : 23:11:37

Hi,

Here is my SQL query:

SELECT DocID, DocDesc, DocDate, CustNo, TransDate, ReceivedBy, ReceivedDateTime, VersionNo, Status, DocApprovedDateTime, ApprovedBy
FROM [dbo].[CUSTMERDOC]
WHERE DATEDIFF (Week, DocApprovedDateTime, GetDate()) > 26

It is just a simple query that shows the customers document records that are move than 26 weeks.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-17 : 23:38:24

Did you tried executing the package in the DTS Designer?
Check the sql results and see if there is any record which might have an invalid data for Access.

Karunakaran

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-17 : 23:49:54

and you run this directly as a dts step?

there might be some datatypes that are not compatible with Access...

quote:
Originally posted by william_lee78

Hi,

Here is my SQL query:

SELECT DocID, DocDesc, DocDate, CustNo, TransDate, ReceivedBy, ReceivedDateTime, VersionNo, Status, DocApprovedDateTime, ApprovedBy
FROM [dbo].[CUSTMERDOC]
WHERE DATEDIFF (Week, DocApprovedDateTime, GetDate()) > 26

It is just a simple query that shows the customers document records that are move than 26 weeks.

--------------------
keeping it simple...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-10-18 : 01:48:59

quote:
Originally posted by jen

and you run this directly as a dts step?

there might be some datatypes that are not compatible with Access...

--------------------
keeping it simple...

I believe the DTS package is executed from the command using DTSRun, thats when the error message are displayed like this. When its run from DTS Designer and if you have invalid data during transformationususally, you get error message with specific column name.

Well, thats my experience.

Karunakaran

william_lee78
Yak Posting Veteran

53 Posts

Posted - 2005-10-18 : 23:23:33

Hi All,

Finally I know the cause of the problem. Basically it is due to the .mdb file that exceed 2GB file size limitation. If I reduce the number of records to transfer, I can successfully transfer the records to my .mdb file.

Anyway, thank you so much for the help and the information provided.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources