Get Name of Column in Error Output

I would like to get the actual name of the column that has the error.Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?

I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column.I am going from Flat File to OLE DB Destination.I have a Script Component getting the output to write to my sproc, and I just need to get the column name.

Suggestions? Thanks

Not really possible/straightforward. Please search this forum for "error column name" and you should get plenty of posts on this topic.|||

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

|||

Jamie Thomson wrote:

In theory this should be possible by interrogatig the metadata. I've just had a go at this but have come up against a few problems. I've emailed Simon Sabin who I know has solved this problem in the past - hopefully he will reply here.

-Jamie

Yep, hopefully indeed.

In case someone is curious, Simon does have a custom component built to get the error column name. Could be buggy (as indicated on his Web page), so use at your own risk.

http://sqlblogcasts.com/files/3/transforms/entry2.aspx|||

Yeah there is a problem with Simon's component. I've just been discussing it with him offline and he acknowledges it.

I've raised a connect posting asking for an enhancement that will enable us to do this (i.e. get the name of the column):

SSIS: Allow virtual input to see columns in other (synchronous) data paths

(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=272863)

-Jamie

Update: The connect submission contains a demo package that you can download from here.

|||

P.S. I've attached a package to the Connect submission that exhibits the problem!

|||

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

|||

ronemac wrote:

So, is there no way to get to the externalMetadataColumn node collection of the package?

When I look up the value in the source of the actual project file "38003", I find that the <inputColumn> node ID value is what is being logged as the ErrorColumn ( and I am writing this to my error log). Then, I looked at the externalMetadataColumnId="37992" and looked at the <externalMetadataColumn> node where the ID = "37992" and the Name attribute of this particular node does have the column name that I am looking to write to my error log..

Any ideas of how I can get to this information?

Thanks again.

A component only knows about itself and its inputs/outputs. It doesn't have any knowledge of other components in the same data-flow so no, it cannot know about the ExternalMetadataCollection of the task's source adapters.

Take a look at the package I submitted on Connect. There is some code in there that will help but be aware that it will only work i specific circumstances (which is, more-or-less, what I've tried to say above and in the Connect submission).

-Jamie

|||How do I get to the package file (.dtsx)?|||

ronemac wrote:

How do I get to the package file (.dtsx)?

It would be wherever your project stores its files. Look on your hard drive. Use the search program for .dtsx files.

You can use Notepad or your favorite editor to open the file.|||

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

|||

Jamie Thomson wrote:

ronemac wrote:

How do I get to the package file (.dtsx)?

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Email me at jamieDOTthomsonATconchangoDOTcom and I'll send it to you.

-Jamie

D'Oh! Yeah, what he said.|||

Jamie Thomson wrote:

Sorry, I thought you could access it from the Connect submission. Obviously you can't.

Yep, and it doesn't look like they are planning on allowing for user-submitted attachments to be exposed to the public.

https://connect.microsoft.com/Connect/feedback/ViewFeedback.aspx?FeedbackID=35286|||

I was referring more to the statement in the feedback you submitted. The Package11.dtsx file is what I was asking about. Sorry for not being more clear.

"I have attached a package that exhibits the problem. Take a look at the Script Component code. The call to GetVirtualInputColumnByLineageID() fails because the column with the supplied LineageID does not exist in the virtual input.

"

|||

Hey Jamie, I received an email update from Simon Sabin's component that he has updated the issues with it an that it is working now. DO YOU KNOW if there is ANY documentation for his component? I have tried to get it from his blog, but I am not having any luck. It looks like the majority of the folks that have tried to use it have run into the same thing.

Thanks again.