none
Converting a string to a integer data type in ssis RRS feed

  • Question

  • I need to import a string data field into a sql table. The column in the table is an int datatype. I am importing the data from a fixed length flat file source.  for example the data I am importing is 0000000012 and I need the data to appear as 12 in the table.

    I tried using the derived column transformation editor... the values I used are Derived column name QtyInt
                                                                                                                     Derived Column <add as new column>
                                                                                                                      Datatype four-byte signed integer[DT_I4]

    The package errors occur in the derived column transformation
    [Derived Column [1651]] Error: An error occurred while attempting to perform a type cast.
    [Derived Column [1651]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (1651)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "qtyint" (1879)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

    Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (1651) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.

    what is the best way to convert this data in ssis? It seems I am only able to use ssis with importing string data to a string field
    Wednesday, November 4, 2009 4:53 PM

Answers

  • Also do I need to check for nulls as well as  " "

    First, if you are using TRIM() don't check for a space (" ").  Just check for an empty string ("").

    I would check for NULLs though, yes.

    ISNULL([AT01]) || TRIM([AT01]) == "" ? (DT_I4)0 : (DT_I4)[AT01]


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by jj3k2009 Thursday, November 5, 2009 7:50 PM
    Thursday, November 5, 2009 2:39 PM
    Moderator

All replies

  • cast it as DT_STR in the derived column..
    Wednesday, November 4, 2009 5:04 PM
  • You can do it without using Devived Column Transformation. If value can be stored in integer column than SSIS will take care of automatically.

    I mean if you have two column data in flat file like:

    testing1  00000123
    testing2  00000012

    Flat file source will show that data type is DT_STR but when you will transfer into table that has 2nd column defined as integer. it will get transformed with no issue.

    Wednesday, November 4, 2009 5:06 PM
  • In your flat file connection go to the advanced tab and change the column data type to [DT_I4]
    Then in you flat file source set the Error Ouput to redirect rows and connect the error output to another flat file just for reference as to what went wrong.
    Now run the package.
    Hope this helps !!
    Sudeep   |    My Blog
    Wednesday, November 4, 2009 5:08 PM
  • hi,
    can u please check the your data? i think, in your data that column data is blank( or data having some character) or some rows might be blank. 
    try to redirect those error row and check those rows.

    Thanks-  

    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, November 4, 2009 7:06 PM
  • ETL
    you are correct. The process halts at record 171 because the next record contains blanks.

    In this case how would I convert these blanks into 0 in some cases and blanks in other cases? I tried to use the derived column transformation using a NULL function but the process still stops at the same record
    Wednesday, November 4, 2009 8:58 PM
  • you need to use a expression in derived column on your input column. replace all the blanks with 0

    trim([column1])=="" ? 0; [column1]

    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, November 4, 2009 9:01 PM
  • I added my column name and the expression stays red

    trim([qtyint])=="" ? 0; [qtyint]



    Wednesday, November 4, 2009 9:45 PM
  • you should do this before converting the string to INT. if still error is there then again configure the error output for the derived column and redirect the rows for analysis.
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Wednesday, November 4, 2009 9:48 PM
  • I added my column name and the expression stays red

    trim([qtyint])=="" ? 0; [qtyint]




    Pass zero as a string
    trim([qtyint])=="" ? "0"; [qtyint]

    OR

    trim([qtyint])=="" ? 0; (DT_I4)[qtyint]
    Hope this helps !!
    Sudeep   |    My Blog
    Thursday, November 5, 2009 1:03 PM
  • Folks - the expression is red because you are using the wrong character for the if-then-else logic.  (Using a ' ; ' character instead of a ' : ' character)

    Use this:

    trim([qtyint]) == "" ? (DT_I4)0 : (DT_I4)[qtyint]


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, November 5, 2009 1:57 PM
    Moderator
  • <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> The derived column editor was unable to parse the ;

     

    i changed the ; to :

    TRIM(AT01) == " " ? 0 : (DT_I4)AT01 and it parsed the expression

     but I still get errors in the

    derived column stage .

    [Derived Column [9742]] Error: The conditional operation failed.

    Also do I need to check for nulls as well as  " "
    Thursday, November 5, 2009 2:34 PM
  • Also do I need to check for nulls as well as  " "

    First, if you are using TRIM() don't check for a space (" ").  Just check for an empty string ("").

    I would check for NULLs though, yes.

    ISNULL([AT01]) || TRIM([AT01]) == "" ? (DT_I4)0 : (DT_I4)[AT01]


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    • Marked as answer by jj3k2009 Thursday, November 5, 2009 7:50 PM
    Thursday, November 5, 2009 2:39 PM
    Moderator
  • The derived column task still fails.   The text file I am importing is fixed length but some of the rows look like this
    namenamenameaddressadressphonephone000012 crlf
    namenamenameaddressadress crlf


    Just wondered if that would matter or should the expression still work.
    Also do I need a dataconversion component before the derived column component?


    [Derived Column [9742]] Error: The conditional operation failed.

    [Derived Column [9742]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (9742)" failed because error code 0xC0049063 occurred, and the error row disposition on "input column "AT01" (9808)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

    Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (9742) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. 

    There may be error messages posted before this with more information about the failure. Error: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.

    also when I configure errors to redirect the row where do I see the results?


    Thursday, November 5, 2009 3:18 PM
  • First, that isn't fixed length unless the phone columns are filled with spaces.

    When you redirect errors, you need to connect the red arrow from the derived column component to another component such as an OLE DB Destination or a Flat File destination or a Row Count component.  You can then use a data viewer on that path to inspect the data at runtime.
    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Thursday, November 5, 2009 3:26 PM
    Moderator
  • When I remove the records with the missing data I get no errors
    But when I run the same file with a record missing data  it fails.
    When I look at the file in a text editor there are no delimiters just text and spaces with a end of line character

    Is there a way to bypass those records with missing data or someway to handle this as any of the records missing this data are not required

    also
    I configured the error output and   add a flatfile destination. I connect it to the red arrow and get a warning and an error

    [Flat File Destination [11614]] Warning: The process cannot access the file because it is being used by another process.

    [Flat File Destination [11614]] Error: Cannot open the datafile "C:\data.txt".
    Thanks
    Thursday, November 5, 2009 4:10 PM
  • I am now looking at adding a script component to manage the variable length records.
    Thursday, November 5, 2009 7:50 PM
  • Thats what exactly I was about to say... :) Add script component and manage data for that column using variable.
    Friday, November 6, 2009 4:22 PM
  • Mohan,
    I am trying to build a script component .  I have a file that has most records over 6000  characters with approx 200 fields,but some records are approx 800 chars with only 20 fields. I am trying to create a script just to test before I write the code for all the fields. All I want to do is determine the records that are <800 chars.. and somewhow make them the same length as the complete records so they can be inserted into a table. I was trying to work with an exmaple and modified it as below

    Public Class ScriptMain
        Inherits UserComponent
        Private Sub CompleteRec(ByVal LineValue As String)
            OutputTextBuffer.AddRow()
            OutputTextBuffer.RefNum = Trim(LineValue.Substring(1, 24))
            OutputTextBuffer.Junk = Trim(LineValue.Substring(25, 674))
            OutputTextBuffer.char = Trim(LineValue.Substring(699, 10))
        End Sub
        Private Sub IncompleteRec(ByVal LineValue As String)
            OutputTextBuffer.AddRow()
            OutputTextBuffer.RefNum = Trim(LineValue.Substring(1, 24))
            OutputTextBuffer.Junk = Trim(LineValue.Substring(25, 687))

        End Sub

        Public Overrides Sub CreateNewOutputRows()
            '
            ' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"
            ' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
            '
            Dim sr As StreamReader
            Dim type As String
            Dim line As String
            Dim taskresult As String

            sr = New StreamReader(Me.Connections.BOABidfileConnection.ConnectionString, False)

            Try
                line = sr.ReadLine

                While line.Length <> 0 'And taskresult <> "failure"

                    Select Case type
                        Case "A"
                            IncompleteRec(line)
                            line = line.Remove(0, 687)


                        Case "B"
                            CompleteRec(line)
                            line = line.Remove(0, 697)
                    End Select
                End While
            Catch ex As Exception

            End Try

        End Sub

    The package never  executes the script it just turns yellow. I dont understand how the script knows which case to choose.
    Friday, November 6, 2009 7:54 PM