Post reply

SSIS package fails when runs under SQL server Agent

  • November 28, 2008 at 4:52 am

    #125239

    Here is my problem,

    Please note that this is targeted only for advance SSIS developers.

    Description:

    I have 13 SQL servers whose login information is maintained at a sql server database.

    When the SSIS package starts I iterate through those information from a FOR EACH LOOP container and dynamically create an OLEDB connection (By binding variables to expression , connection strings etc)

    There is a Data Flow task inside the FOR EACH LOOP, and its task is to create that dynamic connection and count the number of rows return from a select statement and save it in a table as

    [ServerName],[RowCount]

    To make sure that the For each loop continues even after a server login failure (which in terms cause data flow task to fail) so that SSIS package will try to connect to next server from the next iteration I have correctly set PROPOGATE, MAX ERROR COUNT, ForceExecutionResult at appropriate locations. (As explained in many articles on the net).

    Everything works perfect when runs under VS 2008. (I mean For each loop continues even when there is a login failure to those dynamic connection)

    Look at the image below

    Ever thing works perfect when the package is executed directly from SQL server Package folder. (I mean For each loop continues even when there is a login failure to those dynamic connection)

    For example

    Everything works perfect when Agent Job runs. (Note that I have correctly set all the credentials and service accounts. Job Step’s “Run as” is correctly set with right credentials)

    But…….

    Suppose at least one login fails for those 13 SQL servers during For each loop iteration the entire Agent Job fails. The intended behavior is failing only the Data Flow task inside for each loop and continues from the next server.

    If the logins succeed for those 13 SQL servers even the Agent Jobs runs perfectly.

    Plat form:

    SQL server 2008

    2008 BIDS

    .net 3.5

    Please help as this is a kind of urgent project

    Sriwantha Sri Aravinda

  • November 28, 2008 at 12:05 pm

    #904927

    Try to set the MaximumErrorCount property for Data Flow Task (in your case Row Count) properly, for example 13. Make sure all properties in Execution section for the component are set to the default, false. I have a similar package running in agent job and it dose continue the for each loop when the inside Data Flow task failed.

    Charley

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

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