locked
Issue with data type when importing rows from Flat File to SQL Server RRS feed

  • Question

  • Package fails when importing data from flat to SQL Server

    Number of columns in Flat File

    ProductID,Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost

    Table structure in SQL Server

        [ProductID] [int] NULL,
        [Name] [nvarchar](50) NULL,
        [ProductNumber] [nvarchar](25) NULL,
        [MakeFlag] [bit] NULL,
        [FinishedGoodsFlag] [bit] NULL,
        [Color] [nvarchar](15) NULL,
        [SafetyStockLevel] [smallint] NULL

    Used derived column to convert the data types

    MakeFlag1        <add as new column>        MakeFlag == "False" ? "0" : "1"        Unicode string [DT_WSTR]        1  

    FinishedGoodsFlag1        <add as new column>        FinishedGoodsFlag == "False" ? "0" : "1"        Unicode string [DT_WSTR]        1      

    SafetyStockLevel1        <add as new column>        (DT_I2)SafetyStockLevel        two-byte signed integer [DT_I2]              

    Mapped the columns correctly  with the newly created derived columns in OLE DB destination.I am getting error message in the case of    'SafetyStockLevel' . As far i know everything has been done properly here what could be the issue.Please correct me if i am going wrong any where. Please find the error message as below          
                     

    Error: 0xC0049064 at Data Flow Task, Derived Column [58]: An error occurred while attempting to perform a type cast.
    Error: 0xC0209029 at Data Flow Task, Derived Column [58]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Derived Column" (58)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "SafetyStockLevel1" (83)" 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: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (58) failed with error code 0xC0209029 while processing input "Derived Column Input" (59). 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                  




    Smash126



    • Edited by Smash126 Friday, July 20, 2012 6:58 PM
    Friday, July 20, 2012 6:53 PM

Answers

  • Hi Smash,

    You can use either SQL quries or SSIS, but check the source once again which delimiter you are using..

    What i will suggest is read the entire record(row) in to single column in flat file source and the use script component to derive columns and assign values in script itself..

    If you know how to derive new columns in script component means continue... or wait for some time, so i can come up with an example..

    • Proposed as answer by Eileen Zhao Friday, July 27, 2012 4:45 AM
    • Marked as answer by Eileen Zhao Monday, July 30, 2012 2:01 AM
    Tuesday, July 24, 2012 6:12 AM

All replies

  • Please find the sample data for the flat file

    ProductID,Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel
    1,Adjustable Race,AR-5381,False,False,,1000
    2,Bearing Ball,BA-8327,False,False,,1000
    3,BB Ball Bearing,BE-2349,True,False,,800
    4,Headset Ball Bearings,BE-2908,False,False,,800
    316,Blade,BL-2036,True,False,,800
    317,LL Crankarm,CA-5965,False,False,Black,500
    318,ML Crankarm,CA-6738,False,False,Black,500
    319,HL Crankarm,CA-7457,False,False,Black,500


    Smash126

    Friday, July 20, 2012 7:00 PM

  • Issue remains the same  changed the data type in derived column for the column 'SafteyStockLevel1'

    SafetyStockLevel1        <add as new column>        (DT_I4)SafetyStockLevel        four-byte signed integer [DT_I4]                                

    Mapped in the destination.



    Smash126

    Saturday, July 21, 2012 4:40 AM
  • hi Smash,

    modify the metadata of the database table also...

    Table structure in SQL Server

        [ProductID] [int] NULL,
        [Name] [nvarchar](50) NULL,
        [ProductNumber] [nvarchar](25) NULL,
        [MakeFlag] [bit] NULL,
        [FinishedGoodsFlag] [bit] NULL,
        [Color] [nvarchar](15) NULL,
        [SafetyStockLevel] [smallint] NULL change this to  [SafetyStockLevel] [int] NULL then map the saftey stock level column to this..

    thanks

    Saturday, July 21, 2012 8:19 AM
  • It could be some data issue. I would suggest you to redirect error records to error table. It helps in debugging.
    Saturday, July 21, 2012 6:30 PM
  • Is this the right approach to change the table structure. I be live we should not change structure.Yes  we can debug the data.

    What is your thought?


    Smash126

    Monday, July 23, 2012 3:46 AM
  • Its just for debugging purpose,...
    Monday, July 23, 2012 7:27 AM
  • After debugging found issue if SafetyStockLevel is less than double digit

    This is how the data should be inserted in the destination  table

    ProductID    Name                                          ProductNumber    MakeFlag    FinishedGoodsFlag    Color    SafetyStockLevel
    716              Long-Sleeve Logo Jersey, XL      LJ-0192-X            0                 1                                Multi    4

    When i upload the flat file as source

    and when i preview the data,this is how the data looks

    ProductID    Name                                          ProductNumber    MakeFlag    FinishedGoodsFlag    Color    SafetyStockLevel

    716              Long-Sleeve Logo Jersey           XL                         LJ-0192-X    0                               1          Multi,4

    We have around 54 records to deal with. What should be the approach . I was thinking about using SQL queries to correct the data


    Smash126

    Tuesday, July 24, 2012 5:24 AM
  • Hi Smash,

    You can use either SQL quries or SSIS, but check the source once again which delimiter you are using..

    What i will suggest is read the entire record(row) in to single column in flat file source and the use script component to derive columns and assign values in script itself..

    If you know how to derive new columns in script component means continue... or wait for some time, so i can come up with an example..

    • Proposed as answer by Eileen Zhao Friday, July 27, 2012 4:45 AM
    • Marked as answer by Eileen Zhao Monday, July 30, 2012 2:01 AM
    Tuesday, July 24, 2012 6:12 AM