Row yielded no match during lookup

I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.

Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.

Or else any other way to send an email after generating the text file ocntaining the non matching rows.

Please suggest using steps or example

There's likely to be more than one way to do this. Here' one possiblity. After the Dataflow task in the Control Flow, use a sequence container that contains two tasks, a script task that checks for the existence of the error file. Here's the script to check setting a Boolean variable, FileExists, to either two of false.

Dim objFile AsNew FileInfo(Dts.Variables("TempFileName").Value.ToString)

Dts.Variables("FileExists").Value = objFile.Exists

The second task in the container is the Send Mail task which is connected by a Precedence contraint which only executes if the FileExists variable is true.

HTH

|||

Thanks for reply.

Is there any way to invoke the send email task (Assume that the send email task) is present in eventhandler, when we transfer the non matching rows to flat file using flat file destination.

Please suggest.

|||

I'm not sure I understand what you're trying to do. I think you're expecting the OnError event of the DataFlow task to be triggered when a match is not found in the Lookup component. However, if you specify to redirect rows on the Lookup component when no matching row is found, the OnError event of the DataFlow task will not be fired. Am I guessing correctly? If not, which Event (there are several) are you planning on using the SendMail task. OnPostExecute of the DataFlow task? The two tasks used in the Sequence container above could be used in this event to obtain the same result.

|||

thanks for reply.

Your guess is right.

All I need is when lookup transform encounter no match then it should record all such distinct nonmatching rows and then send the same detail to other member using email.

I am new in SSIS, Please suggest me how to do this either by event or any other way

thanks

Row delimiter {LF} is ignored?

Hi!

Im trying to import some data from a Flat File Source, where a row delimiter is {LF} and column separator is SPACE.

Data looks like this:
GI$0c2 T08b 1 1 20060508 000655 6 8 8 c0a81f1f 1 1 1 00A 3 24206816 3 24206816 1 1 3 59910000 001 1 3 14730050 0 25 F10 XX 317148-131136 loop TG_MRB 0 M027 1 3 0 20060508 000655 0 3 59910000 SIP

This is the first record that generates around 41 columns and sorts data as it should, but if the second record is smaller the row delimiter {LF} is ignored and put into one of the columns untill all 41 columns from previous record are filled. It seems to me that columns separator has the priority over the row delimiter which is very wrong. :). If there is a {LF} in the file that should mean that it needs to be in a new row as a new record. I try to keep this all in a SQL 2005 package without using any additional software, i know there might be a solution with the scripting component, but would like to see if theres someone with the similar solution before i start writing any scripts. (i dont like parsing strings with scripts from bulky files:))

Thanks!

Sebastijan L.

Is the file delimited by spaces or is it a fixed-column-width format?

When you say the second record is smaller" what do you mean? Does it have some missing columns? If so, which columns?

There may be some fairly easy ways to parse this, but they depend very much on exactly how you expect records to vary from row to row.

Donald Farmer

|||Thank you for a quick response Donald!

Records are delimited by spaces as shown in the data sample above. For example:
If my 1st row contains 41 columns that are space delimited (row ends with {LF}), and the second row is smaller and contains only enough data for 39 columns, it will fill up the rest of the 2 columns with the data that is already a third record, and in that data i can see a row delimiter which is in my case{LF} from the second row!!

What it should do is whenever there is a specified row delimiter in data it should create another record, and in that case it doesnt..

I can import the data within SQL 2000, Access, Excell with no problem, just that im trying to keep everything inside SQL2005 as some features are pretty good, without the need of 3rd software.

Sebastijan L.
|||

Thanks.

SSIS does not know that columns 40 and 41 are missing - perhaps it was columns 1 and 2, or columns 8 and 13. And it does not know that you do not expect the characters used as row delimiters embedded in the middle of a record - there are cases where that can happen.

Other applications do make assumptions about row delimiters - some of them read ahead to the row end, take the whole row from delimiter to delimiter into memory and parse it out column by column from there. That is good for your scenario, but slow, and unreliable where delimiters can be embedded.

We can - and most likely will at some point - build some more "smarts" into the flat file source to handle some of these situations, although users do need to be aware that every new conditional property we add will decrease the performance of the source adapter.

Meanwhile, you can indeed handle this situation using a script or even expressions in the derived column component. The best solution for you will depend on how complex the error handling has to be, for example, if you know what columns are likely to be missing it makes it much easier.

Donald Farmer

Row Count - Variable Name is Case sensitive

I defined a variable called "NoOfRecords" for a Data Flow Task and within the Data Flow Task I am outputting records from Flat File to RowCount before doing anything else. I typed Variable name as "NoofRecords" in Advanced Editor for Row Count and it didnt like it. It gave the following error.

Error at Load Customers[Row Count [138]]: The variable "NoofRecords" specified by VariableName property is not a valid variable. Need a valid variable name to write to

As soon as I changed the VariableName to NoOfRecords, it worked.

It looks like a case sensitive issue.

Hi Sutha,

Variable names are indeed case sensitive. This is By Design.

Thanks,
Mark

|||Thanks Mark for clarifying it.

row by row reading of textfile

hi,

how do i do in ssis if i want to read the textfile row by row? i need to do this since every line in my flat file has different conditions to fulfill depending on the content of the row.

cherriesh

Have you tried using the flat file source? Perhaps treat the file as delimited, but just have one column, and the row being delimited by a carriage return.

You could also write your own source by using the sript component. Here you can just use the power of VB.Net to open the file, and then read each line as you requie. Pass out data as and when you want. SQL Server Books Online includes topic on how to "Creating a Source with the Script Component", complete with an example titled "Flat File Source Example".

|||

Take a look at this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1568089&SiteID=1

I posted an example on my blog : http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx

Jamie has also pointed out that this can be done without the script task, by using the derived column transform and a conditional split.

Rounding error: Between flat file connection manager Source & OLE DB Connection Destination

I have a Rounding error: Between flat file connection manager Source & OLE DB Connection Destination (SQL Server 2005) in my Dataflow.

File looks like this lets call column names Col A,B,C,D

70410000 RD1 1223631.92 196042.42
70329000 ICD 11025.84 3353.88
71167300 COL 104270.59 24676.96

flat file connection manager settings: first row Column names then Advanced tab Col A float , Col B float , Col C string ,Col D float ,

OLE DB Connection Destination (SQL Server 2005)

CREATE TABLE [dbo].[PT_CUST_ABR](

[PARTY_NO] [float] NULL,

[PARTY_NAME] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[TELECOMABR] [float] NULL,

[GENIABR] [float] NULL,

Problem: ColA (Source) Rounding error to PARTY_NO (Destination)

I have a field of text of in a flat file that the flat file connection manager Source picks up correctly “70000893”

However when it gets the OLE DB Connection Destination the data has changed to 70000896. That’s before its even Written to the database.

The only clue that something is wrong in the middle is the great Data viewer shows the number as 7.000009E+07

Other clues looking at the data it appears there is a rounding error on only the number that dont end in 00

ColA (Source) PARTY_NO (Destination)
71167300 71167296
70329000 70329000
70410000 70410000

Any ideas people?

Thanks in advance

Dave

Float type by definition is not precise, it holds about 7 decimal digits, so it is expected.

If you want to keep exact values, you should use types that are big enough to hold them. E.g. Int for ColA and Decimal for ColC & ColD.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_6r3g.asp

|||

Thanks Michael--already tried that --turned out to the XML file in SSIS had my old settings locked in and my new settings in the flat file connection manager Source did not take effect

I fixed the problem by changing the datatype to string in the flat file connection manager and bigint in the SQL Server 2005 table. However I needed to delete all the objects in the dataflow task and re-recreate them again.--this fixed the XML metadata problem

Dave

Rougue CR/LF in my bcp output flat file!?!?!

The following bcp command is present in an NT job I have scheduled each daybcp "EXECUTE DailyProd.dbo.GetIndexComponentStocks_XML '2006-04-05'" queryout "D:\TABLES\INPUTAP\IndexComponentStocks.wrk"
-S(local) -c -T -o"..\IndexComponenet_XML_LOG.txt" -e"..\IndexComponenet_XML_ERR.txt"
My trouble is that every 2034 characters the output contains a :0D:0A (CR/Linefeed).

Other than that all the output data looks peachy.

Is there some line size setting I am missing (would it be packetsize?)?

Ideally I would just like to not have any CR/LF in my file at all...is there some way to turn it off? I see there are flags to set to change the column and row terminators, but how to turn them OFF?

Thanks much!
PaulYup, just had to add the -r and -c flags without any character following them to turn 'em off.