How can I get the actual line number from inside flatfile sources for each record?

hi all,

maybe this is not a serious problem, but I tried for days to come toi a solution without success.

My Problem:

I have have several flatfile sources I need to import into a sql-server 2005 DB.

It is very important for me to have the original line number from inside the source file for each record. The rowcount transform doesn't fit in for this task, because it accumulates all rows until the end of the dataflow.

I tried script components and it works fine if i assume there are no errors in my source. then I simply could declare a local variable and count it up and add a custom collumn to my output. But for errors in my source this won't work, because a second script component won't know the actual value of a package level variable, which i use to store the value, because i am only allowed to access this variable in the post execujte method of the script.

How can I achieve my goal? Please help me...

Thanks in advance .. Bernd

Can I clarify something?

Do you want the number of rows in the file or the row number adding for each row? Your post above is a little ambiguous about this because you say "original line number from inside the source" but then mention the rowcount component which is nothing to do with line numbers.

-Jamie

|||

I want the line number of the Flat File, which holds the Record.

I don't have them in the source. you may think of it as an Row ID.

Thanks Bernd

|||

OK, gotcha. Well the rowcount component is completely irrelevant here.

SSIS does not give row numbers to rows in the pipeline. I've previously asked for this to be changed which you can vote for and add a comment on here if you like: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335

In the meantime, the alternative is to artifically create your own row numbers as explained here: http://www.sqlis.com/default.aspx?37 however this may not work for you as the row numbers cannot be added until after the rows have been extracted into the pipeline. It depends on your requirement.

If you need row numbers added as the file is parsed then you're in script source component or custom component territory I'm afraid.

-Jamie

|||

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

|||

berndS wrote:

Thank you very, even if my problem isn't solved.

Personally I think it is a major flaw, because we are not able to track direcly back into the source, which sometimes is extremely important. I support your request for adding this information into the Pipeline without question.

Thank you again

Bernd

Thanks Bernd,

In that case, click the first link in my last post and vote for it. I forgot to add the link in before (sorry) but I have now edited it!

I also think this is a really important feature!

-Jamie

|||

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

|||

kenambrose wrote:

Hmm, hope it's OK to play devil's advocate here. How would this work in a data flow merge, conditional split, etc? Seems like a lot of the processing that is needed in ETL and supported in SSIS would confound the meaning of source row number?

Ken

Yeah of course it would, absolutely - in which case it has no meaning and you don't use it. Ideally you'd simply have a boolean property on every source adapter saying whether or not to add this "rownumber" column.

If its there and you don't want it - ignore it!

I'd still like to have this feature.

-Jamie

|||

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

|||

kenambrose wrote:

I was just wondering if this might be one feature that sounds good on paper but has limited practical value. Since I insist on data models and domains that make use of natural keys (and always design solutions based on this paradigm), I can't see how it would be of much use in my work. If I ever need to find a row, I always can do so by using the key...

True enough. Where I think it would be useful would be when importing a file. If a row goes down the error flow it'd be nice to know which row it was.

-Jamie

|||

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors? Otherwise it would be easy to add the row number by a counter (there are some ways to do that, you know)... The only problem are the error lines. SSIS simply changes the value of a non-readable field to NULL. You don't get any error information (at least that's what I see). If there would be any way to find out that a record has an error (and probably what kind of error in which field) then we could just ignore all errors first, add the counter and do some manual error handling later...

Thanks,

|||

Thomas Pagel wrote:

Jamie,

is there any way to get the error details from a source connector when you set it to ignore errors?

I don't think so Thomas, no. Do these errors trigger the OnError eventhandler? I'm 99% sure they don't but don't have a SSIS instance to hand to check.

-Jamie