Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
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
 
Old May 22nd, 2006, 03:44 PM
Registered User
 
Join Date: May 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
 
Old October 4th, 2006, 02:45 PM
Registered User
 
Join Date: Oct 2006
Location: timonium, md, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I run into the same problem.



aspeng
 
Old October 11th, 2006, 12:44 PM
Registered User
 
Join Date: Oct 2006
Location: Devizes, Wiltshire, United Kingdom.
Posts: 1
Thanks: 0
Thanked 1 Time in 1 Post
Send a message via MSN to Jewels
Default

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:
MarkDeVerno (April 24th, 2009)
 
Old April 24th, 2009, 03:25 PM
Registered User
 
Join Date: Apr 2009
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
Default Thank you

Thank you, your suggestions worked for me.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Possible typo on pg 233 WebDNA BOOK: Ivor Horton's Beginning Visual C++ 2008 ISBN: 978-0-470-22590-5 1 October 15th, 2008 12:21 PM
Chapter 2 - End of chapter exercises whizzkid1892 BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 1 July 30th, 2008 12:02 PM
Generics chapter 12 difficult chapter i found ...? Larryz C# 2005 1 July 4th, 2007 09:40 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.