July 31, 2009 at 7:54 am
john do i need to create row count variable on conditional split transofrmation?
July 31, 2009 at 8:28 am
Rupa
Your method is working and even I am stuck on the exceptional handeling, i know we need to add one more oledb tranformation in between conditional split, but have no idea how to configure raiseerror there.
August 3, 2009 at 7:15 am
well guys I have attached an screnshot of my data flow. I have created a variable with rowcount, if an error exceeds more than 50 then the entire packagae should fail, but in my case its not failing. I have 495 errors in my file, all 495 records are written in flat file instead the packagae supposed to be fail due to that conditional split transformation. any one have any clue. please help me out
August 3, 2009 at 7:16 am
well guys I have attached an screnshot of my data flow. I have created a variable with rowcount, if an error exceeds more than 50 then the entire packagae should fail, but in my case its not failing. I have 495 errors in my file, all 495 records are written in flat file instead the packagae supposed to be fail due to that conditional split transformation. any one have any clue. please help me out
August 3, 2009 at 2:26 pm
OK, good news, bad news. Up until now, I had not tried to use the RowCount transformation in conjunction with the Conditional Split transformation.
I went ahead and put to gether a mock up of the example that I gave only to find out that it does not work as I hoped. There are a couple of problems with it. First, the RowCount transformation does not appear to assign the value to the variable until the entire data flow is complete. Obviously this is problematic as we are attemting to access it in the Conditional Split.
So I though about adding in a derived column using a Script Component Transformation, but since we need to know the # of error rows in the pipeline and the data flow processes chunks of rows at a time, this won't work because this would in-effect create an identity value for each row in the pipeline. Rows with a value < 50 would still go to your error processor and the package would only fail once you hit row 50. Maybe this is OK, but I'm guessing you don't want the first 50 error rows to continue to your flat file.
In case you were wondering, that's the bad news!
The good news is that I found a way around it. Remember, the problem I was having was that the data flow is processed in chunks and I need to know the total # of rows in the data flow after all rows have been processed so we need a way to 'stop' the data flow until all rows have made it to a certain point. Enter the Sort Transformation. The Sort Transformation cannot create it's output until it receives all of the rows from the source. So a well-placed script component (just before the Sort) will be able to count all rows in the pipeline. With that said, here's a revised data flow for you (this has been tested w/ the Conditional Split so this time I know it works):
1. Redirect error output from destination component.
2. Replace your RowCount transformation with a Script Component. Here's the script, assuming you are using RowCount as your variable name:
Dim vars As IDTSVariables90
Dim Count As Integer
Me.VariableDispenser.LockOneForRead("RowCount", vars)
Count = vars(0).Value
vars.Unlock()
Me.VariableDispenser.LockOneForWrite("RowCount", vars)
vars(0).Value = Count + 1
vars.Unlock()
3. Directly after the script component, add a Sort transformation. Sort the data by whatever you think makes sense. I would choose a narrow column like an smallint, int. We don't really need the data sorted, but this will 'halt' the data flow long enough for the Script Component above to be able to add up all of the rows in the pipeline.
4. Add another Script Component. This time, we'll use the script component to add to RowCount value into our data flow pipeline. On the Inputs and Outputs screen, go to the Output Columns folder icon and select the 'Add Column' button. Add a column called RowCount and set up its' data type property appropriately. Here's the script:
Dim vars As IDTSVariables90
Me.VariableDispenser.LockOneForRead("RowCount", vars)
Row.RowCount = vars(0).Value
vars.Unlock()
5. Now that the RowCount is in the pipeline and it holds the correct value, we can use the Conditional Split. Configure the Conditional Split to do the comparison on the RowCount column now in our data flow.
That's it!
August 3, 2009 at 2:44 pm
John
thanks for your response, I dont have a knowledge of .net, i added a script component in my data flow, and just paste your code on 'design script' and i got the foollowing error, please se an attachment, whether I am correct?
August 3, 2009 at 2:54 pm
Sorry, my bad. In both scripts, above the Imports section at the top, add this entry: Option Strict Off.
August 3, 2009 at 3:13 pm
john
thanks, that parse the .net code, now when I am trying to execute a package I am getting follwoing message
"[Script Component [3513]] Error: System.Runtime.InteropServices.COMException (0xC0010001): The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)"
and please see an attachemnt of my data flow execution
August 3, 2009 at 3:20 pm
Check the variable name in the script and make sure it matches (including case) the variable name that you created in your package.
August 3, 2009 at 3:26 pm
John
I created vareable name 'errorrowcount' and even edited the variable name on the script that u sent me. variable name is correct, now my question, i created variable on package level, do i need to create variable on data task level or package level is fine, and what do u mean by (including case)?
August 3, 2009 at 3:34 pm
Package level scope is fine. You don't need to create a new variable. By case, I mean case sensitive. If your variable is 'errorrowcount', make sure it's not 'ErrorCount' in the script. So you've checked the variable names and your still getting the error?
August 3, 2009 at 3:44 pm
john my bad it was case sensetive error, i got it, now your method is running very well, i really appricaiate, now a bit confusion, i have attached my data flow execution also,
here i have execute a package with a condition <=50, let me explain a bit about my data,
i have 500 records in a source and there are 495 bad records, when i executed with <=50, package failed but reamining 5 good records are transfered on destination table, so it means package is not failed completley. please see the attachedd file
error on oledb command transformation
"[OLE DB Command 1 [3127]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.".
August 3, 2009 at 4:03 pm
So, you want a failure to roll back the 5 rows?
Here's what I'd do.
Right now, you're using the error condition that the DB engine raises whenever you attempt to insert rows into your destination and the insert fails. Your're error handling logic then occurs after the destination component. By doing it this way, you are forcing your DB engine to validate the data.
I would move the data validation to your package. Perform the validation checks prior to getting to the destination component. This way, you know if you want to abort the package execution or not prior to attempting to insert the rows. This removes the burden of the insert/rollback from the DB engine.
So what makes these 495 rows 'bad'?
August 3, 2009 at 4:15 pm
these 495 records are sample data that i am trying to test, and u are right that I need to fail the whole package, the remainig 5 records should not be inserted in the destination table incase our condition erroroutput<=50 fails. so how i do the data validation and avoid those insertation when task fails. and you are amazing, your method is in a very right direction, i have been using my head for this issue from last one week. can you please help me out to do the data validation and avoid insertaion after task fails.
August 3, 2009 at 4:28 pm
You can do the validations in SSIS, but we need to know what conditions cause the rows to fail. In other words, what makes the rows 'bad'?
Viewing 15 posts - 16 through 30 (of 110 total)
You must be logged in to reply to this topic. Login to reply