This data flow was working fine then all of a sudden it quit. Everything runs successfully, but only 540 rows make it out of the derived column "Add count; convert ints" instead of all 99,932 that I'd expect. Any idea what would have caused this? I've been changing a lot of things in this package and I can't seem to figure out what would have caused this.

It's could have nothing to do with the Derived Column at all - is the rest of the data flow complete? Do you have an OLE DB Command later in the data flow? Talk to me now on

No but I have 26 Oracle Destinations following a few different conditional splits. I tried deleting everything after the derived column and just put in a row count and it did the same thing. So I'm thinking it has to be the derived column or something before it perhaps? I'm stuck, any possible troubleshooting suggestions?

Update: I have 49 different derived columns in that one task so I went through one by one deleting a column and running the job. I was able to narrow it down to one column causing the issue. It's just a simple data type cast (DT_R8)TX_I and it's named tx_i_int. It's used in just one of the Oracle Destinations. After the derived column, it goes to a conditional split. The condition is !ISNULL(si_cs_int) && !ISNULL(tx_i_int) && !ISNULL(SI_N) && !ISNULL(si_x_int) && !ISNULL(dl_int) && si_cs_int == 1 && SI_N == "DKPurchase" && si_x_int == 5 && dl_int == 0 Then it goes to goes to a row count then an oracle destination. See any issues with that?

So, do the records arrive to the conditional split? You named tx_i_int as 'int' but used DTR8 - a doubleprecision floating point variable, is that ok?Arthur My Blog

1)Remove (DT_R8)TX_I from AddCountConvert. 2)Add a RowCount after AddCountConvert step. see if it works. else, 3)Replace (DT_R8)TX_I with (DT_UI8)TX_I from AddCountConvert. else, 4)Add a Conversion before AddCountConvert step. Try to Convert (DT_R8)TX_I. So you can be sure if it is a conversion error. "Im the light of the world, no follower of mine shall walk in darkness"

Only about 500 of the 100,000 I'd expect make it to the conditional split. The rest don't make it past the derived column. The cast is correct, I should probably name it something else without int in the name. I could see how that could be confusing.

Have you tried Remove/Delete derived column? and drag/drop new one?"Im the light of the world, no follower of mine shall walk in darkness"

"Have you tried Remove/Delete derived column? and drag/drop new one?" I tried that and it didn't' work. It does work when i try do #1 that you suggested, that's how I realized that was the column that was causing the issue. 1)Remove (DT_R8)TX_I from AddCountConvert.

Are you catching any error rows? Can you check your data source, if you have any row with TX_I is empty string. You are checking for ISNULL but you are not checking for empty strings? If there is empty values conversion will fail in derived column. Also are you receiving any errors? or the package runs without any problem? "Im the light of the world, no follower of mine shall walk in darkness"

"3)Replace (DT_R8)TX_I with (DT_UI8)TX_I from AddCountConvert." Doesn't work, I get a data type conversion error. "4)Add a Conversion before AddCountConvert step. Try to Convert (DT_R8)TX_I. So you can be sure if it is a conversion error." I did this and didn't get any errors, so I'd assume it's not a conversion error?

I'm not receiving any errors. The package runs fine as far as finishing successfully anyways. I checked the data source and there are no empty strings for TX_I, if there were I'd have a lot worse problems. I'm going to try a couple other things and see if I can narrow it down to something else. But as far as I can tell the only thing that will make it work the way I'd expect is to is: remove tx_i_int from AddCountConvert and remove any reference to it in the conditional split and destination.

Himm, interesting :) There is no conversion error. Package runs well, when you remove (DT_R8)TX_I from AddCountConvert. Otherwise only 540 rows passes to multicast step right? What are the steps after multicast? can you give some information?"Im the light of the world, no follower of mine shall walk in darkness"

After the multicast it goes to a conditional split, the condition is !ISNULL(si_cs_int) && !ISNULL(tx_i_int) && !ISNULL(SI_N) && !ISNULL(si_x_int) && !ISNULL(dl_int) && si_cs_int == 1 && SI_N == "DKPurchase" && si_x_int == 5 && dl_int == 0 Then it goes to a row count, then the oracle destination. After the oracle destination I have a flat file destination for Truncation errors.

Can you add a Data Viewer between AddCountConvert and Multicast to see the passing values of (DT_R8)TX_I ?"Im the light of the world, no follower of mine shall walk in darkness"

I can but only a couple records are going to have a value for TX_I of the 100,000 records I'm processing. I'd be suprised if there was even 1 in the 540 records that actually make it out of the derived column.

Can you check Configure Error Output of AddCountConvert? And tell me Error and Truncation column values for TX_I?"Im the light of the world, no follower of mine shall walk in darkness"

Can you check Configure Error Output of AddCountConvert? And tell me Error and Truncation column values for TX_I?"Im the light of the world, no follower of mine shall walk in darkness"

No records go to the error output.

I fixed it by recreating all the components outside the data flow and it worked. It was painful but I'm glad I just did that instead of messing with it anymore. I'm not sure what the stem of the issue was. It seems like if something weird is happening with SSIS just recreate it and it will work. I've had to do that several times working with it for the past 2 years.