 |
BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9  | This is the forum to discuss the Wrox book Professional SQL Server 2005 Integration Services by Brian Knight, Allan Mitchell, Darren Green, Douglas Hinson, Kathi Kellenberger, Andy Leonard, Erik Veerman, Jason Gerard, Haidong Ji, Mike Murphy; ISBN: 9780764584350 |
|
Welcome to the p2p.wrox.com Forums.
You are currently viewing the BOOK: Professional SQL Server 2005 Integration Services ISBN: 0-7645-8435-9 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

May 22nd, 2006, 03:44 PM
|
Registered User
|
|
Join Date: May 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Chapter 7 example, p232-233,
I can't make this example work at all. I don't understand the statement "Row.CleanedZIP = zip" because there is no zip field that I can find. If you follow the example, as shown on page 232, there is indeed a CleanedZIP field but no Zip field.
I also suspect that the regular expressions shown on page 233 need some correction.
Any help appreciated,
barkingdog
|

October 4th, 2006, 02:45 PM
|
Registered User
|
|
Join Date: Oct 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I run into the same problem.
aspeng
|

October 11th, 2006, 12:44 PM
|
Registered User
|
|
Join Date: Oct 2006
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
|
|
I also ran into the same problem.
I created additional output fields CleanedZip and CleanedState.
I had to change the syntax of the regular expressions to get them to work(Detailed below).
The initial declaration of isGood needs to be set to "True" and not "False".
And finally, In the flat file source editor, on the default page(Connection Manager) the "Retain null values from the source as null values in the data flow" needs to be checked for the script to work correctly.
I hope this helps
Is is the full script below:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports System.Text.RegularExpressions
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Private zipRegex As Regex = New Regex("(^\d{5}$)|(^\d{5}-\d{4}$)", RegexOptions.None)
Private stateRegex As Regex = New Regex("\b([A-z]{2})\b", RegexOptions.None)
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
' All fields except zip must have a value
Dim isGood As Boolean = True
If Row.FirstName_IsNull Or Row.LastName_IsNull Or Row.City_IsNull Or Row.State_IsNull Then
Row.GoodFlag = False
Return
End If
If Not Row.Zip_IsNull Then
Dim zip As String = Row.Zip.Trim()
' zip must match regex if present
If zipRegex.IsMatch(zip) Then
Row.CleanedZip = zip
isGood = True
Else
' try to clean up the zip
If zip.Length > 5 Then
zip = zip.Substring(0, 5)
If zipRegex.IsMatch(zip) Then
Row.CleanedZip = zip
isGood = True
Else
isGood = False
End If
End If
End If
End If
If isGood Then
Dim state As String
state = Row.State.Trim().ToUpper()
If stateRegex.IsMatch(state) Then
Row.CleanedState = state
Else
isGood = False
End If
End If
Row.GoodFlag = isGood
End Sub
End Class
|
The Following User Says Thank You to Jewels For This Useful Post:
|
|

April 24th, 2009, 03:25 PM
|
Registered User
|
|
Join Date: Apr 2009
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
|
|
Thank you
Thank you, your suggestions worked for me.
|
|
 |