?
Solved

DTS - Transform Data Task Properties - Transformation - Email errors in source data.

Posted on 2006-04-18
13
Medium Priority
?
1,488 Views
Last Modified: 2012-08-14
Hi, I am working in Microsoft SQL server 2000. I am currently working in DTS. I need to email an user if the source data is not valid.
Is this possible using ActiveX script in Transformation under Transform Data Task Properties ?  
If this is not possible, is there a way that I can write the error info to a file and later have a ActiveX task to email the file. Any help will be greatly appreciated.
Thanks
KK
0
Comment
Question by:norgrenit
  • 7
  • 6
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16483664
It is very simple if you have access to an SMTP server:
1. Download and install this free component (if you have not already done so):
XPSMTP.DLL - SQL Server SMTP Mail XP
http://www.sqldev.net/xp/xpsmtp.htm

2. Create an Execute SQL Task in your package called "Send Error Message"

3. Add the following to this task:
exec master.dbo.xp_smtp_sendmail @From = 'From email address', @To = 'To email address', @Subject='Error', @Message='Houston we have a problem!', @Server='IP address or name of the SMTP server'

4. On failure branch to the "Send Error Message" task.
0
 

Author Comment

by:norgrenit
ID: 16489120
Thanks acperkins, it looks like I need to explain my problem little bit more.
I have a input file and a DB connection. When I create a Transform Data Task, inside the properties, I define the source file and the destination table. In the transformation tab, I choose the source column and the destination field, and then I create a new transformation with ActiveX script. I need to check the value of the source column. If it is invalid I need to send an email, change the value to a default value and proceed with the load. I need to do this for each row. This does not happen often. But if it does, some one needs to be notified.
For example : I am looking for 8 character date in column 5, if for some reason I get a date less than 8 characters I need to send an email.  I have given the basic code below.
Function Main()
      If IsNull(DTSSource("Col005")) then
      else
            if Len( DTSSource("Col005")) < 8 then
                                              ' this is where I need to send an email
            end if
      end if
      Main = DTSTransformStat_OK
End Function
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16490140
>>it looks like I need to explain my problem little bit more.<<
I know exactly what you are talking about, we do that here all the time.  You need to change your ActiveX Script Task as follows:

Function Main()
     If IsNull(DTSSource("Col005")) Then
          Main = DTSTransformStat_OK
     ElseIf Len( DTSSource("Col005")) < 8 Then      ' this is where I need to send an email
          Main = DTSTransformStat_Error
     End If
End Function

When this task fails it will branch to the "Send Error Message" Execute SQL Task which sends the message.

I trust this is clear.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:norgrenit
ID: 16503061
Thanks acperkins, Sorry for the delay. I downloaded the XPSMTP.dll from the site and installed it as per instructions. I decided to test this with 2 records. So, the transformation of the ActiveX script is as follows.
'  Copy each source column to the destination column
Function Main()
      DTSDestination("sls045_Delivery_Date") = DTSSource("Col009")
      Main = DTSTransformStat_Error
End Function

Then I created a ActiveX task and typed the following
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
      exec  master.dbo.xp_smtp_sendmail
      @From = 'norgrenit@usa.norgren.com',
         @To = norgrenit@usa.norgren.com',
      @Subject='Error',
      @Message='Houston we have a problem!',
      @Server='dotnetdev.usa.norgren.com'
      
      Main = DTSTaskExecResult_Success
End Function

My understanding is for each record, during the transformation, an email will be sent.

But when I try to execute the DTS get the following error.
"The number of failing rows exceeds the maximum specified". This might be because of
Main = DTSTransformStat_Error, in the transformation section.

I also got the following error in the Active X task.
Error Code : 0
Error Source = Microsoft VBScript Compilation Error
Error Description : Invalid Character
Error on Line 5

Any idea ?  Thanks for all your help - KK
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16503628
>>Any idea ?  Thanks for all your help - KK<<
Simply put you are mixing languages:  You cannot use that code in an ActiveX Script Task.  You need to use an Execute SQL Task to use xp_smtp_sendmail.  So:

Create an Execute SQL Task in your package called "Send Error Message" and add the code:
   exec  master.dbo.xp_smtp_sendmail
     @From = 'norgrenit@usa.norgren.com',
        @To = norgrenit@usa.norgren.com',
     @Subject='Error',
     @Message='Houston we have a problem!',
     @Server='dotnetdev.usa.norgren.com'

Now make the Send Error Message task the failure desitination for you Data Transformation task that contains this function:

Function Main()
     DTSDestination("sls045_Delivery_Date") = DTSSource("Col009")
     Main = DTSTransformStat_Error
End Function
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16503636
P.S. Make sure you first test and successfully receive a message using the following code in SQL Query Analyzer:

exec  master.dbo.xp_smtp_sendmail
     @From = 'norgrenit@usa.norgren.com',
        @To = norgrenit@usa.norgren.com',
     @Subject='Error',
     @Message='Houston we have a problem!',
     @Server='dotnetdev.usa.norgren.com'
0
 

Author Comment

by:norgrenit
ID: 16508156
Thanks acperkins. Sending email part worked beautifully. But, it did not continue to load the data. The 2 rows of data I had, did not get loaded. It looks like, when the first record got processed, it set  the DTSTransformStat_Error,
 sent the email and stopped. When I ran the package the transformation task gave a status as "Error Occured", but the Send Error Message task ran successfully. I got one email instead of 2, for each row that failed.

I decided to change the Max Error count in the option tab of the transformation task properties. Just to see whether this will help. When I changed the value to 4 to allow 4 errors to occur before the task stops. The "Send Error Message" task did not run and the transformation task gave a different error "Error duting transformation DTS_Trans.... for row number 2".  The same thing happened with the Insert Buffer Count also. I set them back to default value.  

Am I missing something in the DTS ?  Thanks again - KK
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16512746
My mistake.  I thought you wanted to fail if any errors occurred.  It sounds like you want to validate and then report the errors.

Let me tell you how we do it.  We always import into a staging table, this allows us to validate prior to copying to the final production tables.  So in your case, I would import everything in and only fail if the source data is corrupt.  You can then run some tests on the staging table using T-SQL (it sould be in a stored procedure) to verify that all the columns are valid and if not email the results using XPSMTP.

I trust this is clear.
0
 

Author Comment

by:norgrenit
ID: 16539532
Sorry for the delay.

First and foremost, I do not want my task to fail. I have a transformation which is an ActiveX VBscript. It checks for the date value. If the date value is prior to a certain date, make the date as a default value and email this info to a user. That is all. I want the process to continue doing this for each row that comes in. Right now I have everything except the emailing part. The current code works fine. The only thing I need is to have email capability with this.

I have done what you have mention in the last email in a different situation. I have different ways to do that.  But I was looking for an email option to work.

Thanks for your help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16540151
>>First and foremost, I do not want my task to fail.<<
Right that is what I said.
"My mistake.  I thought you wanted to fail if any errors occurred."  The key word being "thought" as in past tense.

>>If the date value is prior to a certain date, make the date as a default value and email this info to a user. <<
So if there is one row that fails you want to report what exactly?
What happens if 100 rows fail do you want to report it 100 times or what exactly do you want to display?
0
 

Author Comment

by:norgrenit
ID: 16544894
When the date is invalid one of our managers need to be notified, so that he/she can take action. This is a financial data table and the management is little bit sensitive about wrong dates getting entered accidentally. This does not happen quite often. Once in 6 months. 2 or 3 records occassionally. This is what I have been told. The email will have the invalid date and the record details. If for all the weird reasons there are 100 records with invalid date, Yes, the manager wants to get 100 emails. I am dealing with some interesting people here. :)

If nothing else works, I even thought about writing the error info to a file and then email the file using a task at the end of the DTS package.

Thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1200 total points
ID: 16549459
If you can just send one email if there is an invalid date then you can just set a global variable and then in the next task an Execute SQL Task you can send the message if that global variable is set.

If on the other hand you have to send multiple emails for every time there is an invalid date than consider, using a global variable with a delimiter for each time there is an invalid date or as you suggest create a temporary table with all the invalid dates and then email from there using the Execute SQL Task

But to be honest, I have never had the need to do anything like this, nor would I even consider it.  As far as I am concerned if the date is invalid I do not import the date.  It never gets beyond the staging table.
0
 

Author Comment

by:norgrenit
ID: 16588954
After a brief discussion/argument with the concern manager I am going to adopt the temporary table idea with invalid dates. I am going to have a task at the end to email the content of the table in one stretch if any record exist.

Thanks for all your help.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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