Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dataflow rowcount inside foreach loop Expand / Collapse
Author
Message
Posted Tuesday, March 4, 2014 8:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:11 PM
Points: 408, Visits: 228
Hi all,

I am hacking through the use of this tool so please bear with me.

I have a foreach loop that is looping through excel files. I am processing one sheet at a time in each file. So I process sheet 1 and if that was successful process sheet 2 etc. If sheet1 fails I want the file moved to a error directory. In the dataflow for sheet1 I have a rowcount to the error handler. In the control flow between sheet 1 and sheet 2, I branch based on the number of rows in the rowcount variable.

It isn't working. I branch in the controlflow if the dataflow truly errors but in the flow where I interrogate the rowcount variable it never goes to the the branch where rowcount > 0. I have read that the rowcount is not completed until the dataflow is complete. Does this mean that I can't interogate the rowcount until the entire foreach loop is complete? Some guidance and suggestions would be appreciated.


thanks, SR

--------------------------------------------------------------------------------

The light at the end of the tunnel may be you.



Post #1547390
Posted Tuesday, March 4, 2014 2:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:49 AM
Points: 12,947, Visits: 10,719
Why don't you just use the precedence constraint with the OnFailure option (the red arrows)?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547564
Posted Tuesday, March 4, 2014 3:30 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:11 PM
Points: 408, Visits: 228
I am using the red arrow for true failures but since my source is excel the driver posts a fatal error when the data doesn't convert and will not go into the fail constraint. It red x's at the data task unless I change from fail to redirect row. I am using error output to write the rows to a table. I am trying to use the rowcount in the dataflow that is counted going into the error output back in the control flow to branch on success and rowcount > 0 to a file task to move the file to an error directory. I am processing 100's of xlsm files.

I never would have expected that this is a difficult thing to do. I am beginning to loath excel. I have little control over the source files since they are generated by the user.

read file
read sheet1
write to table1 on error move file to error directory
on success read sheet2
write to table2 on error move file to error directory delete rows from table 1
on success move file to completed directory
loop until no more files... sigh



Post #1547586
Posted Wednesday, March 5, 2014 12:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:49 AM
Points: 12,947, Visits: 10,719
What is the error the source is giving?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1547654
Posted Wednesday, March 5, 2014 11:21 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 12:11 PM
Points: 408, Visits: 228
Thanks for the reply. Your response got me to think again about the failure at the source......

I fiddled with the package and increased the maximum errors to 0 and removed the row redirect.

It spins through them all now and moves the offending files.

Sometimes all it takes is to talk about a problem to help clear the fog. :)

thanks

S



Post #1547950
Posted Thursday, March 13, 2014 7:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
And once again Excel and SSIS don't play nicely together - please sort this out Microsoft. After RDBMS, Excel is the most common source of data for most organisations

Post #1550705
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse