Recordset destination producing strings with extra padded spaces

Question

Recordset destination producing strings with extra padded spaces

Alex Levene

Grasshopper

Points: 22
More actions
May 19, 2006 at 5:18 pm

#366211

I'm attempting to shred a recordset after populating it from a Recordset destination component in a Data Flow task. The recordset variable seems to populate just fine.   But when I have a Foreach container read each of the individual field values, all of the string variables are padded with spaces all the way to the full length (e.g. Column FirstName was specified as varchar(20). The resulting variable value is "Fred                " instead of "Fred"). This is causing many problems downstream.
I've been able to populate the recordset and variables correctly using an Execute SQL task, but I have some transformations that are much easier to implement using the data flow. I feel like I'm missing something simple. Any ideas?

The process I've setup is as follows:
1. Data flow task
   a. Read from OLE DB source [Table or view]
   b. Some transformations (I've removed these to debug...still no improvement)
   c. Write to Recordset destination variable
2. Foreach ADO enumerator
   a. Variable Mappings for all fields in recordset 0-n

Thanks
Alex

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jamie Thomson SSChampion Points: 11805 More actions · Answer 1

Alex,

I've never seen these problems with the recordset destination though I dare say I've maybe never used strings in this way. Can you post a repro - perhaps against AdventureWorks (just copy the contents of the XML file into your reply)

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Alex Levene Grasshopper Points: 22 More actions · Answer 2

I don't have AdventureWorks installed on this server. I created a simple package that extracted FirstName and LastName from a table created by:

create table temp.Names (FirstName varchar(30), LastName varchar(30))

Below are links to the control and dataflow screenshots. I haven't made any odd property or expression changes.

http://i68.photobucket.com/albums/i11/alevene/controlflow.jpg

http://i68.photobucket.com/albums/i11/alevene/dataflow.jpg

I would copy the entire package XML, but SQLServerCentral keeps replacing my colons with smileys. Do you know how to escape or remove them?

Also, we don't have SP1 installed. Could that be causing this?

Thanks

Jamie Thomson SSChampion Points: 11805 More actions · Answer 3

Alex,

I can repro the problem and I think is a bug. I've added a bug report here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=08bbef21-d05c-434e-82d8-5314c8f4959e

Please feel free to vote for it and/or add a comment.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson