Converting a string to a integer data type in ssis

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

Answer 1

cast it as DT_STR in the derived column..

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

I added my column name and the expression stays red

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

<!-- /* 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  " "

Answer 12

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

Answer 13

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?

Answer 14

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

Answer 15

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

Answer 16

I am now looking at adding a script component to manage the variable length records.

Answer 17

Thats what exactly I was about to say... :) Add script component and manage data for that column using variable.

Answer 18

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.