sadie519590


Hi,

I just realized something. In the old DTS package I am migrating, there is an ActiveX script that checks for a certain condition in a row. If the condition is true, then it does:

DTSTransformationStat_SkipRow

I just can't believe there isn't an equivalent functionality in SSIS.

However, so far, I have tried the following:

1.) Redirect file error output (on all columns in the file)

2.) Use a conditional transform to search for a text string in a column (the "bad" row has different text in it)

And still, I keep getting errors that there is an "impartial row" in the file. Yes, I know that - why doesn't the error redirection catch this Why doesn't the conditional expression catch it either

Am I missing something here Is it just buggy I find it hard to believe I have to work around something that worked just fine in DTS.

Thanks




Re: There has got to be a way to do this - dealing with bad rows in a file

K108


Additional research and I see that I should be using a conditional split to filter rows.

The problem is, the conditional split works beautifully as long as all the rows have the same number of columns.

For example:

FINDSTRING([column0], "rain", 1) > 1 ---> case1

FINDSTRING([column0], "rain", 1) <=0 ---> case2

col0

"the rain in spain falls mainly on the plains", "text", "text" ---> case1

"blue and yellow goggles", "text", "text" ---> case2

But in the event that a particular row doesn't share the same number of columns as the other rows, it errors out.

It seems that it only is happy filtering values as long as the rows are uniform. This doesn't help with situations where you have row whose number of columns does not match the other rows.

Is it truly the case that I have to pre-process my file first to get rid of the row Has anyone else had to solve this problem

Thanks






Re: There has got to be a way to do this - dealing with bad rows in a file

jwelch

Not sure I understand what you're doing. How do you have rows with a different number of columns going into the same conditional split





Re: There has got to be a way to do this - dealing with bad rows in a file

Phil Brammer

I have to ask again, why do you keep starting new threads on the same topic Please keep your posts together in the same thread when they deal with the same problem.

Phil Brammer
SSIS Forum Moderator





Re: There has got to be a way to do this - dealing with bad rows in a file

JayH

Yes, SSIS can handle this.

First, I could not reproduce your problem. I created a text file with a partial row at the end and SSIS ignored it completely except in the preview of the connection manager, where it gave me a warning. If the partial row occurred somewhere else in the file, then the source component successfully routed it to an error destination when configured to redirect errors. So I'm not sure what is going on in your situation.

SSIS does not explicitly support flat files with multiple formats. However, you can read the file as a single column and then perform the parsing yourself in a custom script component. This gives the opportunity to recognize the format of the line and parse it accordingly. Please see this thread. When the "bad" row is identified in the script, you can send it to a different output or just drop it.

Probably your best case is to try and get the error redirection working. I'd be interested to know what you row formats look like that that it isn't. But if that isn't possible for some reason, using script to roll your own parser should be a perfectly viable solution.





Re: There has got to be a way to do this - dealing with bad rows in a file

Phil Brammer

JayH wrote:
Yes, SSIS can handle this.

SSIS does not explicitly support flat files with multiple formats. However, you can read the file as a single column and then perform the parsing yourself in a custom script component. This gives the opportunity to recognize the format of the line and parse it accordingly. Please see this thread. When the "bad" row is identified in the script, you can send it to a different output or just drop it.



Which is what I suggested be done in the thread that started this whole conversation.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1429166&SiteID=1

K108, If you feel there should be additional handling of flat files, please feel free to submit your feature request over at http://connect.microsoft.com/sqlserver/feedback

JayH wrote:
Probably your best case is to try and get the error redirection working. I'd be interested to know what you row formats look like that that it isn't. But if that isn't possible for some reason, using script to roll your own parser should be a perfectly viable solution.


This won't work because the error redirection on the flat file source is for conversion errors. You are correct, SSIS ignores the bad row and throws a warning (not an error which would stop processing, perhaps).

Thanks,
Phil





Re: There has got to be a way to do this - dealing with bad rows in a file

K108

Sorry. I didn't realize that I was doing that. I don't always remember what's in previous posts.



Re: There has got to be a way to do this - dealing with bad rows in a file

K108

JayH,

Can you please tell me exactly what your pkg looks like. Perhaps mine is not set up correctly.

I completely simplified my task to see if I could get the error redirection to work. This is what it looks like:

DataFlow Task --> Flat File Source ---error output---> Flat File Destination

And in the Flat File Source, under Advanced -> Error Output -> Error, I have set all my columns to "Redirect Row". I thought that would do the trick.

But I am still getting this error: [Flat File Source [799]] Warning: There is a partial row at the end of the file.

Thanks much





Re: There has got to be a way to do this - dealing with bad rows in a file

Phil Brammer

K108 wrote:

JayH,

Can you please tell me exactly what your pkg looks like. Perhaps mine is not set up correctly.

I completely simplified my task to see if I could get the error redirection to work. This is what it looks like:

DataFlow Task --> Flat File Source ---error output---> Flat File Destination

And in the Flat File Source, under Advanced -> Error Output -> Error, I have set all my columns to "Redirect Row". I thought that would do the trick.

But I am still getting this error: [Flat File Source [799]] Warning: There is a partial row at the end of the file.

Thanks much



I've given you the best option to handle this. Read in each row as one big string and check it for completeness. (Or exclude it based on your FINDSTRING method). Then use the resulting output to do what you want.

Please READ out posts. You cannot redirect the error output of the Flat File Source in this scenario. It won't work. Perhaps because IT IS NOT AN ERROR, it is a WARNING. SSIS ignores the bad record.





Re: There has got to be a way to do this - dealing with bad rows in a file

K108

I am very curious because JayH said his error redirection was working. I want to know what is different between his package and mine.

I already have a developer writing code to remove the last line. That isn't the problem, and my solution is already in place. However, I would still like to know how JayH got his package to work, under his specific circumstances. I'm just curious what is different. Is that ok





Re: There has got to be a way to do this - dealing with bad rows in a file

Phil Brammer

K108 wrote:

I am very curious because JayH said his error redirection was working. I want to know what is different between his package and mine.

I already have a developer writing code to remove the last line. That isn't the problem, and my solution is already in place. However, I would still like to know how JayH got his package to work, under his specific circumstances. I'm just curious what is different. Is that ok



When I tried it, the error row was not redirected; it was just ignored. Jay will have to check that the row did, in fact, get redirected.





Re: There has got to be a way to do this - dealing with bad rows in a file

K108

Right, I understand. But in your case, it's ignoring the error. In my case, it's breaking my package. All things being equal (service packs and the like) there must be something I'm not setting up correctly. If my pkg ignored the impartial row at the bottom, that would be fine by me, and I wouldn't have a problem :-)

My package looks like:

Data Flow Task --> Flat File Source (with bad row at the bottom) --- error redirection ---> Flat File Destination

Is that what your package looks like

Thanks





Re: There has got to be a way to do this - dealing with bad rows in a file

Phil Brammer

K108 wrote:

Right, I understand. But in your case, it's ignoring the error. In my case, it's breaking my package. All things being equal (service packs and the like) there must be something I'm not setting up correctly. If my pkg ignored the impartial row at the bottom, that would be fine by me, and I wouldn't have a problem :-)

My package looks like:

Data Flow Task --> Flat File Source (with bad row at the bottom) --- error redirection ---> Flat File Destination

Is that what your package looks like

Thanks



Yes, well I'm not going to a destination for the purposes of this exercise. Just a row count transformation.

Source file:
1234|38bfd|291.00
3918|bwe82|2.94
8492|h8k2i

http://www.ssistalk.com/ssis_badrow1.jpg
http://www.ssistalk.com/ssis_badrow2.jpg
http://www.ssistalk.com/ssis_badrow3.jpg
http://www.ssistalk.com/ssis_badrow4.jpg





Re: There has got to be a way to do this - dealing with bad rows in a file

JayH

I found that if the missing columns are strings, SSIS will ignore the row completely whether it appears at the end or in the middle. However, if one of the missing columns is defined as an integer, then SSIS will consider this an error and the row can be redirected. Probably any non-string datatype will cause the error, I only tested with an integer.




Re: There has got to be a way to do this - dealing with bad rows in a file

Phil Brammer

JayH wrote:
I found that if the missing columns are strings, SSIS will ignore the row completely whether it appears at the end or in the middle. However, if one of the missing columns is defined as an integer, then SSIS will consider this an error and the row can be redirected. Probably any non-string datatype will cause the error, I only tested with an integer.


Are you simply shifting the columns though So perhaps it is expecting to see string data but encounters an integer That would be expected that it would redirect the error then because it is a conversion error.

What is your sample data When I use the data set I provided a few posts up and set the last column to numeric, it errors out, perhaps because it doesn't know where the end of the "numeric" data is and hence throws an error.