DTS Error Handling

Hi folks,

I would like to know -

-) Is there a way to continue the DTS Package execution even if there are some errors? for example if there is a primary key violation error, I would like to continue with my transformation.

Note:
I found a KB (#240221) in MSDN "HOW TO: Handle Errors in Data Transformation Services "Package" and "Step" Objects". At the bottom of this article MS suggests to use DTSErrorMode object. But I could not find that object.

DB version : SQL Server 2000You can handle exceptions in DTS tasks and continue processing...

The example you gave: PRIMARY KEY Violation would most likely occur in an Execute SQL Task or possibly an ActiveX Script.

When an error occurs in a task you can add an On Failure Workflow to an ActiveX Script that restarts the process (loops back to) where it left off, i.e. performing the next INSERT etc...

See the sample below:

Function Main()

dim pkg
dim stp
'Rerun the ActiveXScriptstep
set pkg = DTSGlobalVariables.Parent 'Get a reference to the package
set stp = pkg.Steps("DTSStep_DTSActiveScriptTask_1")
stp.ExecutionStatus = DTSStepExecStat_Waiting

'Release resources
set stp = nothing
set pkg = nothing

main = DTSTaskExecResult_Success
End Function

Regards,
CPN

DTS Error handling

Hi,

I'm trying to decide on the best method for dealing with errors in a DTS package. It is sufficient to retrieve Step failure information after package execution but I have tried both methods. Specifically, these methods are detailed in http://support.microsoft.com/kb/240221. Another article - http://support.microsoft.com/default.aspx?scid=kb;en-us;321525 - details the need to establish a single event sink to avoid "unexpected behaviour". I have used the code as described but noticed very little difference between post execution checking using GetExecutionErrorInfo and the PackageEventSink Interface. Using the Event Sink does retrieve one extra type of error, ie.

"Error at Destination for Row number 2. Errors encountered so far in this task: 1". This error will always give the row number as the last row in the file, in this case an excel file source. This is not really useful, but I don't want to detach the Event Sink because of the possibility of "unexpected behaviour". Could somebody advise? First is it possible to retrieve the line number, and secondly can anyone detail what an example of this unexpected behaviour might be? Finally, is there a way to retrieve a unique key constraint violation from a package if it occurs? It is only caught in the generalised way and produced as "Error at Destination...".

Thanks in advance

Please see the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg
|||This forum is specific to SSIS, the replacement for DTS in 2005. You might have better success posting here: http://groups.google.com/groups?as_q=Html+mail&as_ugroup=microsoft.public.sqlserver.dts

DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:

>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'***************************************
*******************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'***************************************
*********************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:[vbcol=seagreen]
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
[vbcol=seagreen]
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
> '***************************************
*******************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
> '***************************************
*********************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:|||Thanks!
Sue Hoegemeier wrote:[vbcol=seagreen]
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
>

DTS and handling dates

I am trying to DTS some data from delimited source and some of the
dates have the value 99999999. This naturally makes the DTS job fail.
Is there a method to make it ignore these values and append only those
with acceptable date formats?: 20060810
Thanks for any suggestions.
RBollingerThere are probably quite a few different ways...it all
depends as to what best meets the overall needs for the
package.
One option would be to use a query against the text source
to select the rows you want to import using whatever
criteria against the date column.
Another would be to use an ActiveX Transformation script
with the Transform Data task, check the value of the column
with the date value and if it's not a date or in whatever
format, use DTSTransformStat_SkipInsert
to skip the row.
And another option would be to import the data into a
staging table, use a transform data task and query the
staging table for rows with the appropriate values for the
date column.
-Sue
On 10 Aug 2006 14:09:57 -0700, "robboll"
<robboll@.hotmail.com> wrote:
>I am trying to DTS some data from delimited source and some of the
>dates have the value 99999999. This naturally makes the DTS job fail.
>Is there a method to make it ignore these values and append only those
>with acceptable date formats?: 20060810
>Thanks for any suggestions.
>RBollinger|||Making a staging table isn't an option for me so I'll use the
Transformation script:
The source is a text file and I am having difficulty with the date.
The script is as follows (it may wrap):
'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
Function Main()
DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
if DTSSource("Col002") = "99999999" then
Main = DTSTransforStat_SkipRow
else
DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
DTSSource("Col002"))
end if
DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
Main = DTSTransformStat_OK
End Function
This is bombing with a type mismatch 'CONVERT' error. Any suggestions
appreicated.
RBollinger
Sue Hoegemeier wrote:
> There are probably quite a few different ways...it all
> depends as to what best meets the overall needs for the
> package.
> One option would be to use a query against the text source
> to select the rows you want to import using whatever
> criteria against the date column.
> Another would be to use an ActiveX Transformation script
> with the Transform Data task, check the value of the column
> with the date value and if it's not a date or in whatever
> format, use DTSTransformStat_SkipInsert
> to skip the row.
> And another option would be to import the data into a
> staging table, use a transform data task and query the
> staging table for rows with the appropriate values for the
> date column.
> -Sue
> On 10 Aug 2006 14:09:57 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
> >I am trying to DTS some data from delimited source and some of the
> >dates have the value 99999999. This naturally makes the DTS job fail.
> >Is there a method to make it ignore these values and append only those
> >with acceptable date formats?: 20060810
> >
> >Thanks for any suggestions.
> >
> >RBollinger|||You get the error because you are in an ActiveX script using
VBScript and you are trying to use T-SQL syntax in the
VBScript. So convert won't work. VBScript conversion
functions start with C followed by the abbreviated data
type. CInt for Integer, CDate for Date. Try CDate.
-Sue
On 12 Aug 2006 14:51:37 -0700, "robboll"
<robboll@.hotmail.com> wrote:
>Making a staging table isn't an option for me so I'll use the
>Transformation script:
>The source is a text file and I am having difficulty with the date.
>The script is as follows (it may wrap):
>'**********************************************************************
>' Visual Basic Transformation Script
>' Copy each source column to the
>' destination column
>'************************************************************************
>Function Main()
> DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> if DTSSource("Col002") = "99999999" then
> Main = DTSTransforStat_SkipRow
> else
> DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
>DTSSource("Col002"))
> end if
> DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
>Main = DTSTransformStat_OK
>End Function
>This is bombing with a type mismatch 'CONVERT' error. Any suggestions
>appreicated.
>RBollinger
>
>
>
>Sue Hoegemeier wrote:
>> There are probably quite a few different ways...it all
>> depends as to what best meets the overall needs for the
>> package.
>> One option would be to use a query against the text source
>> to select the rows you want to import using whatever
>> criteria against the date column.
>> Another would be to use an ActiveX Transformation script
>> with the Transform Data task, check the value of the column
>> with the date value and if it's not a date or in whatever
>> format, use DTSTransformStat_SkipInsert
>> to skip the row.
>> And another option would be to import the data into a
>> staging table, use a transform data task and query the
>> staging table for rows with the appropriate values for the
>> date column.
>> -Sue
>> On 10 Aug 2006 14:09:57 -0700, "robboll"
>> <robboll@.hotmail.com> wrote:
>> >I am trying to DTS some data from delimited source and some of the
>> >dates have the value 99999999. This naturally makes the DTS job fail.
>> >Is there a method to make it ignore these values and append only those
>> >with acceptable date formats?: 20060810
>> >
>> >Thanks for any suggestions.
>> >
>> >RBollinger|||Thanks!
Sue Hoegemeier wrote:
> You get the error because you are in an ActiveX script using
> VBScript and you are trying to use T-SQL syntax in the
> VBScript. So convert won't work. VBScript conversion
> functions start with C followed by the abbreviated data
> type. CInt for Integer, CDate for Date. Try CDate.
> -Sue
> On 12 Aug 2006 14:51:37 -0700, "robboll"
> <robboll@.hotmail.com> wrote:
> >Making a staging table isn't an option for me so I'll use the
> >Transformation script:
> >
> >The source is a text file and I am having difficulty with the date.
> >The script is as follows (it may wrap):
> >
> >'**********************************************************************
> >' Visual Basic Transformation Script
> >' Copy each source column to the
> >' destination column
> >'************************************************************************
> >
> >Function Main()
> > DTSDestination("ACTIVITY-STAT") = DTSSource("Col001")
> >
> > if DTSSource("Col002") = "99999999" then
> > Main = DTSTransforStat_SkipRow
> > else
> > DTSDestination("DATE-ACT-END") = CONVERT(smalldatetime,
> >DTSSource("Col002"))
> > end if
> >
> > DTSDestination("ACTIVITY-TYPE") = DTSSource("Col003")
> > DTSDestination("ACTIVITY-CODE") = DTSSource("Col004")
> > DTSDestination("ACTIVITY-UNIT") = DTSSource("Col005")
> > DTSDestination("ACTIVITY-DESC") = DTSSource("Col006")
> >Main = DTSTransformStat_OK
> >End Function
> >
> >This is bombing with a type mismatch 'CONVERT' error. Any suggestions
> >appreicated.
> >
> >RBollinger
> >
> >
> >
> >
> >
> >
> >
> >Sue Hoegemeier wrote:
> >> There are probably quite a few different ways...it all
> >> depends as to what best meets the overall needs for the
> >> package.
> >> One option would be to use a query against the text source
> >> to select the rows you want to import using whatever
> >> criteria against the date column.
> >> Another would be to use an ActiveX Transformation script
> >> with the Transform Data task, check the value of the column
> >> with the date value and if it's not a date or in whatever
> >> format, use DTSTransformStat_SkipInsert
> >> to skip the row.
> >> And another option would be to import the data into a
> >> staging table, use a transform data task and query the
> >> staging table for rows with the appropriate values for the
> >> date column.
> >>
> >> -Sue
> >>
> >> On 10 Aug 2006 14:09:57 -0700, "robboll"
> >> <robboll@.hotmail.com> wrote:
> >>
> >> >I am trying to DTS some data from delimited source and some of the
> >> >dates have the value 99999999. This naturally makes the DTS job fail.
> >> >Is there a method to make it ignore these values and append only those
> >> >with acceptable date formats?: 20060810
> >> >
> >> >Thanks for any suggestions.
> >> >
> >> >RBollinger

DTA package handling multiple file formats

Hi,

Currently we get data from more then 200 different sources and all of
our vendors provide data in different file formats. The problem is we
have more then 100 DTS packages now and the maintainance is very
diffucult.
Every time our vendor changes the format we have to change in multiple
DTS packages.
Is anybody know what would be the right way of reducing the no. of DTS
packages.
The type of file formats we get are .xls .txt .dat .csv etc. and .txt
& .dat files comes with different delimitters. The # of columns also
varies from file to file. Is it possible to have a DTS package which
can handle diff file formats and loads data into a staging table and
from there based of the source of the file we can move data into
respective tables & columns.

We are using SQL SERVER 2000

Thanks in advance.

Subodh"Subodh" <sgoyal@.agline.on.ca> wrote in message
news:90104bf0.0501240846.58b2b293@.posting.google.c om...
> Hi,
> Currently we get data from more then 200 different sources and all of
> our vendors provide data in different file formats. The problem is we
> have more then 100 DTS packages now and the maintainance is very
> diffucult.
> Every time our vendor changes the format we have to change in multiple
> DTS packages.
> Is anybody know what would be the right way of reducing the no. of DTS
> packages.
> The type of file formats we get are .xls .txt .dat .csv etc. and .txt
> & .dat files comes with different delimitters. The # of columns also
> varies from file to file. Is it possible to have a DTS package which
> can handle diff file formats and loads data into a staging table and
> from there based of the source of the file we can move data into
> respective tables & columns.
> We are using SQL SERVER 2000
> Thanks in advance.
> Subodh

Personally, I would look at writing an external script or program in C#,
Python, Perl or whatever to manipulate the files and load the staging table.
The script could load the data directly to the staging table by dynamically
generating INSERTs, or it might transform the source files to your own
standard file format to be used with bcp.exe, BULK INSERT or the DTS Bulk
Insert task.

Your maintenance efforts would be then directed at the program, not at the
packages, which is probably a good thing - it's likely easier to modify one
module/class/object than 10 packages, and most languages have good library
support for parsing, tokenizing, regexes and so on. Or perhaps a hybrid
solution might work - an external program for proprietary file formats, and
standard DTS connections/tasks for the rest. You might also want to ask in
microsoft.public.sqlserver.dts to see if someone else has experienced a
similar situation.

Finally, since your basic issue (as I understood it) is that you have too
many file formats, you should consider agreeing a standard file format - at
least with your larger clients/vendors - rather than looking at it just as a
technical problem. I have no idea how easy that would be in your company's
situation, of course.

Simon|||
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!