none
SSIS Equivalent to DTS Transform Data Task Properties

    Question

  • I am trying to read in a flat file, transform the fields and store into a destination database.

     

    In DTS, this works using Transform Data Task Properties. I define the columns and then have a VB script on the Transformations tab that changes any bad data.

     

    Is there a way to do this in SSIS that I can define the column transformations and re-use my VB scripts?

     

    Linda

    Thursday, May 03, 2007 4:29 PM

Answers

  • Assuming RetailPrice is a string, and you want to keep it as a string, you could simply do the following.  Note that you would have to make RetailPrice a Read/Write column on the Input Columns page of the script component editor.

    Dim d as Double
    If Not Double.TryParse(Row.RetailPrice,d) Then
        Row.RetailPrice = String.Empty
    End If

    If you would rather have NULL than an empty string, then do this instead: Row.RetailPrice_IsNull = True

    Now, if you want to do a type conversion, then you'll need a new column in the Data Flow since columns can't change types.  You can add this new column on the Inputs and Outputs page of the script transformation editor.  Assuming you name the new column "curRetailPrice" and define it as DT_R8, your code would look like this:

    Dim d As Double
    If Double.TryParse(Row.RetailPrice, d) Then
        Row.curRetailPrice = d
    End If

    Thursday, May 03, 2007 10:36 PM

All replies

  • Re-use DTS transformation scripts?  No.  Re-write using Derived Column or SSIS transformation script components? Yes!
    Thursday, May 03, 2007 4:38 PM
  • Thank you. I had not found Derived Column yet. However, I still don't have this solved.

     

    In DTS on the Transform Data Task Properties screen on the Transformations tab I could set up ways to convert my data:

     

    For DateTime screen (Date Time String Transformation Properties) allowed me to change the format of the date string from source date format of 'MM/dd/yy' to destination format 'dd MMMM yy HHmm'. I don't see a similar way to do this in Derived column.

     

    For numeric columns, I have an ActiveX script that has code like:

    If DTSSource("Col010") = ""  Or IsNumeric(DTSSource("Col010")) = False Then
      DTSDestination("RetailPrice") =  Empty
     Else
      DTSDestination("RetailPrice") =  DTSSource("Col010")
     End If

     

    Is Derived Column what I should be using? Where would I find the SSIS transformation script components?

     

    Linda

     

     

    Thursday, May 03, 2007 7:11 PM
  •  Linda Cornerstone wrote:

    Thank you. I had not found Derived Column yet. However, I still don't have this solved.

     

    In DTS on the Transform Data Task Properties screen on the Transformations tab I could set up ways to convert my data:

     

    For DateTime screen (Date Time String Transformation Properties) allowed me to change the format of the date string from source date format of 'MM/dd/yy' to destination format 'dd MMMM yy HHmm'. I don't see a similar way to do this in Derived column.

     

    For numeric columns, I have an ActiveX script that has code like:

    If DTSSource("Col010") = ""  Or IsNumeric(DTSSource("Col010")) = False Then
      DTSDestination("RetailPrice") =  Empty
     Else
      DTSDestination("RetailPrice") =  DTSSource("Col010")
     End If

     

    Is Derived Column what I should be using? Where would I find the SSIS transformation script components?

     

    Linda

     

     

     

    Hi Linda,

     

    The Derived Column component doesn't have an equivalent of IsNumeric(). You wouldn't be the first to complain about this.

     

    Converting your dats into a different format is just a string manipulation problem. The Derived Column can do this, as can the script component. Reply if you need help with this.

     

    The SSIS script component is available in the toolbox. It uses .Net code whereas the ActiveX transforms in DTS used VBScript. Therefore, you have much more scope with teh script component although the learning curve may be a bit steeper - depending on your experience.

     

    Hope that helps.

     

    -Jamie

     

     

    Thursday, May 03, 2007 7:58 PM
  • Thanks Jamie. From your post, I think I need to use the SSIS Script command because derived column will not do what I want to. Is that correct? What I am doing is so simple and it is causing so much trouble.

     

    So, if I have to use SSIS script, how to a code a simple little conversion? The Script editor gives me the following. All I want to do is test the input column (see 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 Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

     

    ********************************OLD VBSCRIPT CODE - what is the .net syntax??????*************************

    ' Copy each source column to the destination column

    If RetailPrice = "" Or IsNumeric(RetailPrice) = False Then

    DTSDestination("RetailPrice") = Empty

    Else

    DTSDestination("RetailPrice") = DTSSource("Col010")

    End If

    *************************************************************************************************************************

     

    End Sub

     

     

    Thursday, May 03, 2007 10:09 PM
  • Assuming RetailPrice is a string, and you want to keep it as a string, you could simply do the following.  Note that you would have to make RetailPrice a Read/Write column on the Input Columns page of the script component editor.

    Dim d as Double
    If Not Double.TryParse(Row.RetailPrice,d) Then
        Row.RetailPrice = String.Empty
    End If

    If you would rather have NULL than an empty string, then do this instead: Row.RetailPrice_IsNull = True

    Now, if you want to do a type conversion, then you'll need a new column in the Data Flow since columns can't change types.  You can add this new column on the Inputs and Outputs page of the script transformation editor.  Assuming you name the new column "curRetailPrice" and define it as DT_R8, your code would look like this:

    Dim d As Double
    If Double.TryParse(Row.RetailPrice, d) Then
        Row.curRetailPrice = d
    End If

    Thursday, May 03, 2007 10:36 PM
  •  Linda Cornerstone wrote:

    Thanks Jamie. From your post, I think I need to use the SSIS Script command because derived column will not do what I want to. Is that correct? What I am doing is so simple and it is causing so much trouble.

     

    So, if I have to use SSIS script, how to a code a simple little conversion? The Script editor gives me the following. All I want to do is test the input column (see 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 Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

     

    ********************************OLD VBSCRIPT CODE - what is the .net syntax??????*************************

    ' Copy each source column to the destination column

    If RetailPrice = "" Or IsNumeric(RetailPrice) = False Then

    DTSDestination("RetailPrice") = Empty

    Else

    DTSDestination("RetailPrice") = DTSSource("Col010")

    End If

    *************************************************************************************************************************

     

    End Sub

     

     

     

    Linda,

    Here is the code you need:

     

    Checking for IsNumeric()

    (http://blogs.conchango.com/jamiethomson/archive/2007/05/03/SSIS_3A00_-Checking-for-IsNumeric_28002900_.aspx)

     

    There is a downloadable demo on there as well!

     

    -Jamie

     

    Thursday, May 03, 2007 10:48 PM
  • Jamie and JayH (and everyone else!) - THANK YOU FOR YOUR HELP. My SSIS package finally imported by flat file correctly.

    Linda

    Friday, May 04, 2007 1:45 AM
  • JayH,

    I was happily reading in my huge flat files. I came to a file that had the string 'NaN' in a field that I am expecting a number (this is a money field in my sql database table that I am writing to). So, I get an error: [OLE DB Destination [9]] Error: There was an error with input column "RetailPrice" (4806) on input "OLE DB Destination Input" (22). The column status returned was: "The value could not be converted because of a potential loss of data.".

     

    I thought my scripting code would take care of this and just set the field not NULL. Am I missing something?

    ' 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 Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim d As Double

    ' Copy each source column to the destination column

    Row.WholesalerCode = Trim(Row.WholesalerCode)

    If Not System.Double.TryParse(Row.RetailPrice, d) Then

    Row.RetailPrice_IsNull = True

    'Row.RetailPrice = String.Empty

    End If

    If Not System.Double.TryParse(Row.BenchmarkRetail, d) Then

    Row.BenchmarkRetail_IsNull = True

    End If

    If Not System.Double.TryParse(Row.DealerCost1, d) Then

    Row.DealerCost1_IsNull = True

    End If

    End Sub

    End Class

     

    Linda

    Friday, May 04, 2007 2:50 AM
  • Good news Smile

     

    Friday, May 04, 2007 2:55 AM
  •  Linda Cornerstone wrote:

    JayH,

    I was happily reading in my huge flat files. I came to a file that had the string 'NaN' in a field that I am expecting a number (this is a money field in my sql database table that I am writing to). So, I get an error: [OLE DB Destination [9]] Error: There was an error with input column "RetailPrice" (4806) on input "OLE DB Destination Input" (22). The column status returned was: "The value could not be converted because of a potential loss of data.".

    I thought my scripting code would take care of this and just set the field not NULL. Am I missing something?

    ' 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 Microsoft.SqlServer.Dts.Pipeline.Wrapper

    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

    Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim d As Double

    ' Copy each source column to the destination column

    Row.WholesalerCode = Trim(Row.WholesalerCode)

    If Not System.Double.TryParse(Row.RetailPrice, d) Then

    Row.RetailPrice_IsNull = True

    'Row.RetailPrice = String.Empty

    End If

    If Not System.Double.TryParse(Row.BenchmarkRetail, d) Then

    Row.BenchmarkRetail_IsNull = True

    End If

    If Not System.Double.TryParse(Row.DealerCost1, d) Then

    Row.DealerCost1_IsNull = True

    End If

    End Sub

    End Class

    Linda



    Unfortunately, NaN (Not-A-Number) is a valid value for that data type.  You can test for the condition using Double.IsNan, but it makes your condition testing a little more complicated.  I suggest you wrap it in a function since you're performing multiple tests.

    Code Snippet


        Private Function DoubleTest(ByVal Value As String) As Boolean
            Dim d As Double
            If Not Double.TryParse(Value, d) Then
                'Windows.Forms.MessageBox.Show(Value + " is not numeric")
                Return False
            End If
            If Double.IsNaN(d) Then
                'Windows.Forms.MessageBox.Show(Value + " is NaN")
                Return False
            End If
            'Windows.Forms.MessageBox.Show(Value + " = " + d.ToString())
            Return True
        End Function


    Friday, May 04, 2007 12:02 PM
  • WOW... i just read in over a million record with no errors. Thank you!!!!!!!!!!!!
    Friday, May 04, 2007 2:45 PM
  •  Linda Cornerstone wrote:
    WOW... i just read in over a million record with no errors. Thank you!!!!!!!!!!!!

     

    That's cool. How long did it take?

     

    And how long did it take in DTS?

     

    -Jamie

     

    Friday, May 04, 2007 3:05 PM
  • Jamie,

    SSIS read in 1,244,652 lines from 31 different flat files in 5 minutes and 37 seconds. Right now, I don't have a DTS time, but will post it later if I get that configured.

    Linda

    Friday, May 04, 2007 4:18 PM
  •  Linda Cornerstone wrote:

    Jamie,

    SSIS read in 1,244,652 lines from 31 different flat files in 5 minutes and 37 seconds. Right now, I don't have a DTS time, but will post it later if I get that configured.

    Linda

     

    Interesting, thanks Linda. If you could get the figures for DTS as well that would be good.

     

    Thanks

    Jamie

     

    Friday, May 04, 2007 4:36 PM
  •  Linda Cornerstone wrote:

    Jamie,

    SSIS read in 1,244,652 lines from 31 different flat files in 5 minutes and 37 seconds. Right now, I don't have a DTS time, but will post it later if I get that configured.

    Linda

     

    One other thing - how are you loading from seperate files? Are you looping over them?

     

    There's some other options here:

     

    Processing data from multiple files all at once

    (http://blogs.conchango.com/jamiethomson/archive/2006/10/14/SSIS_3A00_-Processing-data-from-multiple-files-all-at-once.aspx)

     

    Some will be quicker than others. If you can use a MULTIFLATFILE connection manager then I would expect that to be quickest.

     

    -Jamie

     

    Friday, May 04, 2007 4:45 PM