[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DTS phases filter - informational question

Posted on 2005-04-29
16
Medium Priority
?
605 Views
Last Modified: 2013-11-30
I've got a dts job which functions just fine.  I'd like, however, to improve the error handling  -- like how many rows were inserted, what row failed if there was a failure, etc.  I've enabled the 'show multi-pahse pump in dts designer' property.  and now i am within the package itself, the transformations tab.  i notice there are several new phases filters in addition to 'Row Transform' that I'd had already.  The new ones, of course, are not mapped source/destination - I assume that I'd need to do that.  All I'm wondering is whether or not by doing this I am going to alter/impact in any way the existing Row Transform?  Can anybody give me a little more information on the right way to improve the error handling as I've mentioned above?
0
Comment
Question by:RLLewis
  • 7
  • 7
  • 2
16 Comments
 
LVL 13

Accepted Solution

by:
ispaleny earned 375 total points
ID: 13895206
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13895226
And for error handling of rejected data and rowcount, you can use built-in logging.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13895256
See Transform Data Task - Options - Exception file. But it doesn't work for fast-load(bulk-insert).
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:RLLewis
ID: 13895274
I do the exception file on everything by default.  Right now I am reading the sqlservercentral piece --- very good thus far.  My worries are the same, though -- if i do this, am I going to screw my presently-functioning pkg?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13895344
and let me elaborate --- the package that is 'working fine'  -- it loads tons of files on a scheduled basis.  like yesterday i walked through 877 files fine.  fifteen of them failed, though, logging that it can't find col079.  that is the only information i have.  i'm just trying to get far better detail regarding 'why' it's failing every so often on certain files.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13895802
Just a personal opinion here, unless you start writing quite a bit of ActiveX script in DTS, it's really tough to get a "nice picture" of the errors.  I've found it often easier to stage the flat files first and load them from table to table with DTS or simply using SQL Statements.  The table-to-table option is nice, because you can flag errors on your input table.

Brett
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13895867
RLLewis,
in log you have an error row number, it is written into Log.Source file also. If it fails without logging, then isn't the file in expected format.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13895870
i'm not sure what you mean by that, arbert --- staging them --- i am loading them into a working table first, and then into the 'real' table.  it's the load into the working table that fails.  can you explain what you mean by that?
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13895884
here's my failure in the log - where is this error row number:

The execution of the following DTS Package failed:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description:Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.
Error code: 80040428
\Error Help File:sqldts80.hlp
Error Help Context ID:700

Package Name: CAX Load Historical
Package Description: Looping, Importing and Archiving
Package ID: {8461F39E-197F-41F7-9A20-31F3724F1162}
Package Version: {3636872D-1B59-48FD-9CA6-EE47285E4CFD}
Package Execution Lineage: {F020A3B3-7CC0-414A-BD3D-146F9DC8D42F}
Executed On: 20070RLEWIS
Executed By: rlewis
Execution Started: 4/29/2005 8:10:30 AM
Execution Completed: 4/29/2005 8:10:30 AM
Total Execution Time: 0.422 seconds

Package Steps execution information:

Step 'DTSStep_DTSExecuteSQLTask_1' succeeded
Step Execution Started: 4/29/2005 8:10:30 AM
Step Execution Completed: 4/29/2005 8:10:30 AM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0

Step 'DTSStep_DTSActiveScriptTask_1' succeeded
Step Execution Started: 4/29/2005 8:10:30 AM
Step Execution Completed: 4/29/2005 8:10:30 AM
Total Step Execution Time: 0.016 seconds
Progress count in Step: 0

Step 'DTSStep_DTSActiveScriptTask_2' was not executed

Step 'DTSStep_DTSActiveScriptTask_3' succeeded
Step Execution Started: 4/29/2005 8:10:30 AM
Step Execution Completed: 4/29/2005 8:10:30 AM
Total Step Execution Time: 0.015 seconds
Progress count in Step: 0

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:Column name 'Col079' was not found.
Step Error code: 80042008
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Step Execution Started: 4/29/2005 8:10:30 AM
Step Execution Completed: 4/29/2005 8:10:30 AM
Total Step Execution Time: 0.359 seconds
Progress count in Step: 0

Step 'DTSStep_DTSActiveScriptTask_4' was not executed
Step 'DTSStep_DTSActiveScriptTask_5' was not executed
0
 
LVL 34

Expert Comment

by:arbert
ID: 13895895
Oh ok, from above, I figured you were going from a file directly into your table.  I'm just saying there are probably easier ways to go about error checking than with dts.  You could run cursory checks of the data before you attempt to load them (make sure numbers are numbers, lengths are appropriate, etc), set error flags before you actually do your load.

The current DTS handles logging well, but it doesn't really give you too many options on what to do with the records if something happens (all is better in 2005).
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13895967
But this log is not a detailed Transform Data Task - Options - Exception file log. It is an overall DTS package log.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13896086
i think i figured it out --- these files that fail on col079 actually only have 78 columns.  huge problem there -- i have no idea how to load the data into the same table in the event that the column 79 is not also found.  do you guys know?
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13896252
You can create new On error branch with different DTS transform task.
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13896337
I'm not sure how to do that.
0
 
LVL 13

Expert Comment

by:ispaleny
ID: 13896411
In DTS, there are 4 dependency types:
On success
On failure
On completion
ActiveX

You create this DTS schema:
Text file connection
|
|Normal transformation
|
Database connection
|
|On failure dependency
|->
Text file connection
|
|Normal transformation with one column less
|
Database connection
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13896431
oh hell, i knew that.  excuse my blonde moment.  and yes, you're right, that is the right way to work around this.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question