January 29, 2009 at 4:04 pm
Hi all,
When pulling a string column into a recordset destination it seems like it is adding trailing spaces. I though I was going crazy until I found this post online about it.
http://flyingtriangles.blogspot.com/2006/08/workaround-to-ssis-strings-are-not.html
It's easy to fix using a trim on the column after the recordset destination task is done, but I guess I don't understand why its happening. Has anyone else else ever experienced this?
Thanks,
Strick
October 25, 2010 at 8:32 am
I am having the same problem, but I don't have enough knowledge to fix it. I am new to VBA/SQL, so I'm not sure how to do what you described. Here's my code:
Dim conn As ADODB.Connection
Dim strLabTable As String
Dim strLabCol As String
Dim strPtTable As String
Dim strPtCol As String
strLabTable = "dbo_vw_LAB_CHOL"
strLabCol = "PtID"
strPtTable = "Patient"
strPtCol = "DOB"
Set conn = CurrentProject.Connection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = conn
rs.Source = "Select FullName, FirstName, MiddleName, LastName, SSN, Sex FROM " & strLabTable
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open
Do Until rs.EOF
rs!FirstName = RTrim(SeparateFirst(rs!FullName))
rs!LastName = Trim(SeparateLast(rs!FullName))
rs!MiddleName = Trim(SeparateMiddle(rs!FullName))
If IsNull(rs!SSN) = False Then
rs!SSN = StripSSN(rs!SSN)
End If
rs!Sex = ConvertGender(rs!Sex)
rs.MoveNext
Loop
rs.Close
The SeparateFirst function takes a field that contains a full name and separates into first, middle and last. When the function returns the variable, it is trimmed. Howevever, when it is copied to the recordset, the field is padded out to 255 characters. I have tried trimming, but that does not seem to work. Any help would be greatly appreciated.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply