March 4, 2014 at 8:53 am
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.
March 4, 2014 at 2:21 pm
Why don't you just use the precedence constraint with the OnFailure option (the red arrows)?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2014 at 3:30 pm
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
March 5, 2014 at 12:06 am
What is the error the source is giving?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 5, 2014 at 11:21 am
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
March 13, 2014 at 7:47 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply