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

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
norgrenitAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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.
norgrenitAuthor Commented:
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
Anthony PerkinsCommented:
>>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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

norgrenitAuthor Commented:
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
Anthony PerkinsCommented:
>>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
Anthony PerkinsCommented:
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'
norgrenitAuthor Commented:
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
Anthony PerkinsCommented:
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.
norgrenitAuthor Commented:
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.
Anthony PerkinsCommented:
>>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?
norgrenitAuthor Commented:
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
Anthony PerkinsCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
norgrenitAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.