Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SSIS For Loop Question

Answered SSIS For Loop Question

  • Friday, January 11, 2013 7:10 AM
     
     
    Sign In to Vote
    0

    I have a for loop container which runs with no problems. Outside the loop I have another SQL Task that needs to run after the for loop process has completed. I have a Precedence Contraint set to run the SQL Task when the loop container has successfully completed. However, it never runs. The processes in the loop container is working with no errors/problems. Can someone point me in the right direction? Please...

    Thanks!

    Susan

     

All Replies

  • Friday, January 11, 2013 7:13 AM
    Moderator
     
     
    Sign In to Vote
    0
    So the package finishes without errors and without running the Execute SQL Task?
    Have you changed anything to the Precedence Contraint? Your requirement is the default setting...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter



     
  • Friday, January 11, 2013 7:14 AM
     
     
    Sign In to Vote
    0

    Hi,

    try to look if for loop container havent set property forceExecutionResult to any value other than none. If yes, set it to none.

    Otherwise try to delete Precedence constraint and create new one.

    Zdenek

    Please mark as helpful and propose as answer if you find this as correct. nosekz.eu


    • Edited by Zdenek Nosek Friday, January 11, 2013 7:15 AM
    •  
     
  • Friday, January 11, 2013 7:18 AM
     
     
    Sign In to Vote
    0

    No it is set to the default Success

    Susan

     
  • Friday, January 11, 2013 7:20 AM
     
     
    Sign In to Vote
    0

    Thanks for the quick response. I looked at the forceExecutionResult and it is set to 'None'. I also took your suggestion and deleted the Precedence and create a new one and it's still not calling the SQL Task. Any other suggestions?

    Susan

     
  • Friday, January 11, 2013 7:26 AM
     
     
    Sign In to Vote
    0

    Hi,

    are you sure, that loop Ends all iteration loops? because it turns green after every loop not after the last one, but next task will be executed after all loops are done.

    Please mark as helpful and propose as answer if you find this as correct. nosekz.eu

     
  • Friday, January 11, 2013 7:33 AM
     
     
    Sign In to Vote
    0
    Yes the For Loop Container is green as well as all processes within the container.

    Susan

     
  • Friday, January 11, 2013 7:40 AM
    Moderator
     
     
    Sign In to Vote
    0
    Yes the For Loop Container is green as well as all processes within the container.

    Susan


    Strange... changed any of the other properties of the foreach loop (not those that are available in the Foreach Loop Editor)? Perhaps post a screenshot of your Control Flow... or your log

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Friday, January 11, 2013 7:42 AM
     
     
    Sign In to Vote
    0

    The loop generates two excel spreadsheets based on two different sql select statements (pulling from same table and columns). Both files are generated as expected. The last to processes within the loop is to rename the files and copy a new format file over to the export directory for the next time this process runs. It did produce both spreadsheets, renamed them approiately and copied the format file to the directory as expected.

    Susan

     
  • Friday, January 11, 2013 7:49 AM
     
     
    Sign In to Vote
    0

    No everything is set to defaults other then the InitExpression, Eval Expression and AssignExpression

    Susan

     
  • Friday, January 11, 2013 8:30 AM
     
     
    Sign In to Vote
    0

    What is the expression you are using the Precedence constraint ?

    Thanks

    Sumit

     
  • Friday, January 11, 2013 9:40 AM
     
     
    Sign In to Vote
    0

    try adding a simple script task both inside the loop and outside it - set the script IN the loop to be the last item processed within the loop and simply pop up a message box

    The script outside the loop should be attached to the precedence constraint from the loop (i.e. before your SQL statement). Again, a simple messagebox should suffice

    This should give you an indication of exactly what is happening

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Friday, January 11, 2013 9:43 AM
     
     
    Sign In to Vote
    0

    Additionally, check your on error settings for each component in the loop

    It may be that one of your components is failing but if it is set to ignore, the loop may run again and be successful in the next iteration - this will mean that you don;t see any components as "red" because they go green on the next iteration. The loop may also show green but the underlying status may be failure. You can check this by adding another precendence constraint to the loop that uses the fail;ure constraint rather than success - again a simple script with message box wuill show if this is the case

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Friday, January 11, 2013 5:06 PM
     
     
    Sign In to Vote
    0
    Added message boxes but they are never being called due to a failuer. I'm looking at the logging now to see if I see anything wrong.

    Susan

     
  • Friday, January 11, 2013 5:10 PM
     
     
    Sign In to Vote
    0

    It is strange that immediate task after for each loop doesnt run. Try saving your package and close it in bids window. Then again open it from solution explorer and then run.

    Thanks,

    hsbal

     
  • Friday, January 11, 2013 5:24 PM
    Moderator
     
     
    Sign In to Vote
    0
    Perhaps it is easier to create a new foreach loop and try again...

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter


     
  • Friday, January 11, 2013 7:59 PM
     
     
    Sign In to Vote
    0

    Ok I've looked at the logging and this is what I found. How to fix, no clue! Anyone have a direction they can point me. Would greatly appreciate it.

    Logging: (See the italic text)

    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_pre: The object is ready to make the following external request: 'IDBCreateCommand::CreateCommand'.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_post: 'IDBCreateCommand::CreateCommand succeeded'. The external request has completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_pre: The object is ready to make the following external request: 'ICommandText::SetCommandText'.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_post: 'ICommandText::SetCommandText succeeded'. The external request has completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_pre: The object is ready to make the following external request: 'IGetDataSource::GetDataSource'.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_post: 'IGetDataSource::GetDataSource succeeded'. The external request has completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_pre: The object is ready to make the following external request: 'ICommandProperties::SetProperties'.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM

    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_post: 'ICommandProperties::SetProperties failed'. The external request has completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_pre: The object is ready to make the following external request: 'IDBProperties::SetProperties'.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_post: 'IDBProperties::SetProperties failed'. The external request has completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_pre: The object is ready to make the following external request: 'ICommand::Execute'.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    Diagnostic,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},ExternalRequest_post: 'ICommand::Execute failed'. The external request has completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM


    User:ExecuteSQLExecutingQuery,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},Execution of the SQL statement is completed.,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM
    OnProgress,IT96,CITY\sbrazier,SQL Sample Task 1,{eddc57b6-77a7-49df-a028-a3210a115daf},{DF544D39-1D0D-40E3-90C0-2633FEB00E84},Executing query "CREATE TABLE `nada` (
    `acct_num` Long,
    `item_num` ".,1/11/2013 2:35:44 PM,1/11/2013 2:35:44 PM

    Susan

     
  • Saturday, January 12, 2013 7:44 AM
     
     
    Sign In to Vote
    0
    That's probably a bit too detailed. Can you set logging up on the package to be a bit less verbose - need to know on which ssis step its failing

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Monday, January 14, 2013 12:08 PM
     
     
    Sign In to Vote
    0
    Well at least you know why the element after the loop didn't execute - due to failure in the loop!

    Rgds Geoff
    ----------------------------------------------------------
    Mark as Answer if this resolves your problem or "Vote as Helpful" if you find it helpful.

     
  • Friday, January 18, 2013 8:05 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
     

    Hi sbrazier,

    IDBProperties::SetProperties can fail to set properties for a number of reasons, including the following:

    1. The property was not supported by the provider.
    2. The data source object or enumerator was not initialized, and the property was not in the Initialization property group.
    3. The data source object was initialized, and the property was not in the Data Source property group.
    4. The property set was not supported by the provider.
    5. The property is read-only and was not set to its default value.
    6. It was not possible to set the property.

    Details please see:
    http://msdn.microsoft.com/en-us/library/windows/desktop/ms723049(v=vs.85).aspx
    http://msdn.microsoft.com/en-us/library/windows/desktop/ms711497(v=vs.85).aspx

    Thanks,
    Eileen

    If you have any feedback on our support, please click here



    Eileen Zhao
    TechNet Community Support