SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dataflow rowcount inside foreach loop


dataflow rowcount inside foreach loop

Author
Message
SuzSQL
SuzSQL
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 Visits: 299
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.



Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27753 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
SuzSQL
SuzSQL
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 Visits: 299
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



Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27753 Visits: 13268
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
SuzSQL
SuzSQL
SSC-Addicted
SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)SSC-Addicted (452 reputation)

Group: General Forum Members
Points: 452 Visits: 299
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. Smile

thanks

S



aaron.reese
aaron.reese
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1047 Visits: 902
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search