Deduping to multiple outputs

I know that you can dedupe with the Sort transformation, but that seems to drop the dupes completely. Is there a way to dedupe and have the uniques go to one output, while the dupes go to another?One way that I've accomplished this is to Multicast the source. Feed one dataset to an Aggregate transform selecting count(*) and the duplicated fields. The output of the Aggregate is then fed into a Conditional Split with one criteria being count(*) == 1 and the other being count(*) > 1. You can then Merge Join the two outputs with another instance of the original multicast and continue on with your flow.
Its a round about way of getting there, but it works. It's also not very performant because each instance of the Multicast requires a memcopy for each row.
Larry

Decoding Decimal Form of HRESULT from ErrorCode

I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to another OLE-DB Command transformation that logs the error on that row to a "failed rows" table. In this table I log the ErrorCode and ErrorColumn values that come with the error path from the first OLE-DB Command.

OK, that's all working great. However, here's the kicker: there's no error description value. The ErrorCode value, naturally, is the decimal form of an HRESULT--for example, -1071607696. Without some further information, however, this code is not useful for troubleshooting.

Has anyone figured out a trick here? I'm not even certain that this is an SSIS HRESULT, since it could for all I know be from the OLE-DB layer, the database layer, or somewhere else.

Thanks,
Dan

http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0209070.html

The DescribeRedirectedErrorCode method may also be of some use here. Not sure what you actually expect to do, but normally I would log the full error via another means, such as the built in SSIS logging. Use that for the text description. The code would alllow you to automate handling of different error scenarios.

|||Hi Darren,

Thanks for the link to that error info (I had actually found that subsequent to my original post with some additional searching), and for the pointer to DescribeRedirectedErrorCode. I did not know about the existence of this method. It's also interesting to find out that this is an SSIS HRESULT even though the error pertains to a foreign key constraint violation in the database layer--is SSIS re-interpreting the original SQL Server exception? I wonder whether an ErrorCode will always be a native SSIS error code...?

You refer to logging the full error via another means. I get the feeling that I'm missing an opportunity here to be logging a row-level error in a data flow in a different way than I am now. I'd obviously prefer to log the full error info instead of just ErrorCode and ErrorColumn. However, I don't see how this would work.

Do you redirect the row first through a script component so that you can programatically use the ErrorCode to call DescribeRedirectedErrorCode for additional info for the subsequent logging?

Or are you catching the error in the control of flow? How does that work exactly? Does a row-level exception in a data flow fire an error event at the control of flow level? I guess I was specifically trying to prevent that by using Redirect Row from my OLE-DB Command transform--I just want to log the problem with that row and keep moving through the rest of the rows...

Thanks,
Dan

|||

You may also be interested in taking a look at "Enhancing an Error Output with the Script Component," which was new in the December drop of BOL.

Also, be aware of the "Integration Services Error and Message Reference" list which includes the HRESULT in hex. As for converting (in code), although I don't have the code that I used to create the list in front of me right now, I believe there are format specifiers that you can use with .ToString() to convert quite simply between decimal and hexadecimal representations.

-Doug

|||That was exactly what I needed, Douglas, thank you. I am going to use that trick on future error pathways. Hopefully a future release of SSIS will make this unnecessary by adding an intrinsic ErrorDescription column to go along with ErrorCode and ErrorColumn.

Here is the link for those who'd like to read the article:

http://msdn2.microsoft.com/en-us/library/ms345163.aspx

Has anyone else noticed that Google's URLs pointing to MSDN articles have a "(d=robot)" in them, so that when you click from Google to MSDN the article shows up with no styling or sidebar navigation. Example:

http://msdn2.microsoft.com/en-us/library(d=robot)/ms345163.aspx

I've noticed it doing this the last couple days.

Thanks again,
Dan

|||

Doug,

What is the difference between GetErrorDescription and DescribeRedirectedErrorCode, they both seem remarkably similar, apart from the hosting class. Context maybe?

How does using GetErrorDescription like this know about the upstream component that raised the error? Surely it needs to know, since if as a component author I generate my own error codes, I would then override DescribeRedirectedErrorCode to give you the description, but how do you call my implementation?

|||

Darren,

A complete answer will need to come from the dev team. The methods seem to do the same thing, as you observed - get a description from an error code. I suspect that this works only with Integration Services errors and messages, and that it is made possible (or easier) by the fact that all of these are consolidated in the managed Microsoft.SqlServer.Dts.Runtime.HResults class. I'll see what I can find out.

-Doug

|||

You can also use my enhanced error component to add the column name of the column that failed to the error output.

I guess I should add the error description as well

Declaring DataTable in script causes error?

As discovered when trying to create a custom transformation for this question (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=899895&SiteID=1&mode=1) I tried creating an object of type DataTable and I get this error

dim myTable as DataTable

Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. dts://Scripts/ScriptComponent_3851bc3613714d2d904d79bc006234f9/ScriptMain 19 24 ScriptComponent_3851bc3613714d2d904d79bc006234f9

Even if I add "Imports System.XML" I get the same error. Isn't DataTable part of System.Data (which is imported by default in Script Components)? Everyone else get the same behavior?

Chris,

The Imports directive brings namespaces from referenced assemblies. please see the link below for more details:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vastmimports.asp

You need to add a reference to System.Xml.dll through the project pane in order to be able to use any types from System.Xml namespace.

|||Thanks that works!... so even though its listed as part of System.Data it uses something from System.Xml?

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 / 464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800/ 464

OR

SELECTCONVERT(DECIMAL(10,2), 6800/ 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 /

464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

Debug BIDS code?

Hello,

Can you debug code for a script transformation in BIDS?
Thanks.

This is relational calculus, try the links below. This not DTS it is the place you clean and aggregate the data before moving it to datawarehouse. The second link is the industry matrix by the person who created the industry. Hope this helps.

http://www.sql.ru/club/DataTransformationServices.shtml

http://www.intelligententerprise.com/showArticle.jhtml?articleID=54200319

|||Hello,
I couldn't find anything in those links regarding the issue that I'm having. What I want to do is debug code in a script component, as a destination, and I'm not having any success. Did you see something that I didn't?
Thanks for your help.|||There is a PPT file for developers in the first link you can download and all those articles in the first link are by people using Integration Services. Hope this helps.|||I just checked the PPT file Donald Farmer of Microsoft is debugging C#. Hope this helps.