Post reply

Help with SSIS task design

  • February 9, 2009 at 9:23 am

    #383898

    Hello everyone. It has been 4 years since I worked with DTS in SQL2000, so need some help here as I try SSIS for the first time.

    Here's what I have:

    1: SQL Data Flow Task - gets some data by running "exec StoredProcedure" and outputs result to a text file

    2: On "Success", email is sent with text file attachment.

    3: On "Failure", email is sent to support.

    It work fine when Data Flow task returns data. When no data is returned, Data Flow Task errors with "The job failed. The last step to run was step 1". No file is created and no email sent, even "Failure" condition is not executed.

    It works for me (no data - no email with attachment), except that I get job failure notifications from SQL Server.

    How do I check if Data Flow Task didn't return records and suppress an error?

    Thanks much.

    ~ Steve

  • February 11, 2009 at 9:26 am

    #942268

    Anyone? I don't need exact implementation, just some ideas. Thanks

  • February 11, 2009 at 10:27 pm

    #942610

    Steve, can you give some more information about your package. How are you determining success or failure - are you checking a row count?

    Tim Mitchell
    TimMitchell.net | @tmitch.net | Tyleris.com
    ETL Best Practices

  • February 12, 2009 at 4:50 pm

    #943276

    Tim - I would like to count recordd in Data Flow Task but don't know how.

    I use Precedence Constrainf (Success) from data Flow Task to Email Task, but I think it fails when "Source-Query" task (2nd image) in Data Flow doesn't return records to a "text file creation" task. I didn't find "Success-Failure" precedence constraints in Data Flow, just Data Flow Path.

    I'm attaching screenshots of main SSSIS and detail of Data Flow Task and hope that will clarify my question.

    Thanks for your help.

    ~ Steve

    P.S. Images didn't show usin tag are links to the images.

    http://www23.brinkster.com/lolakers/export%20ssis.png

    http://www23.brinkster.com/lolakers/Data%20Flow.png

  • February 12, 2009 at 5:16 pm

    #943295

    Steve,

    Part of the problem is that you have got your error precedence constraints set to Logical And, which means that both the data flow and the e-mail task must fail before the Notify Support task will be run. You should double-click either of those red connectors in your Control Flow and choose Logical OR, which will turn both connectors to a dotted line instead of a solid red line, indicating an OR rather than an AND condition.

    Another issue may be in the data flow itself. When a data flow executes but does not return any rows, the data flow will not fail, but simply succeed with zero rows. Are you possibly doing something in an error handler for that data flow to force a failure if zero rows are found?

    Tim

    Tim Mitchell
    TimMitchell.net | @tmitch.net | Tyleris.com
    ETL Best Practices

  • February 12, 2009 at 8:35 pm

    #943335

    Thanks Tim. I converted "Failure" constraint to OR type, so that part is good.

    I didn't specifically set error handling in a Data Flow task. It got populated with defaults.

    If I edit Data Flow and look at Error Output, it has "Fail Component" under "Error" column as selected choice. There's just one column that is returned by sproc.

    I changed it to "Ignore" and run package again and got an error.

    Below is the output:

    Information: 0x4004300A at Get Collection Site Data, DTS.Pipeline: Validation phase is beginning.

    Information: 0x40043006 at Get Collection Site Data, DTS.Pipeline: Prepare for Execute phase is beginning.

    Information: 0x40043007 at Get Collection Site Data, DTS.Pipeline: Pre-Execute phase is beginning.

    Error: 0xC02092B4 at Get Collection Site Data, Source - Query [1]: A rowset based on the SQL command was not returned by the OLE DB provider.

    Error: 0xC004701A at Get Collection Site Data, DTS.Pipeline: component "Source - Query" (1) failed the pre-execute phase and returned error code 0xC02092B4.

    Information: 0x40043009 at Get Collection Site Data, DTS.Pipeline: Cleanup phase is beginning

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply