Post reply

Pulling data from flatfile to SQL /error on date column converting

  • June 13, 2012 at 3:32 pm

    #150954

    Hi All,

    I am newbee to SQL Server. Can anyone try to help me out.

    I am using SSIS to pull the data from source (Flate File) to Destination ( SQL Server)

    My Source file is FlateFile and I have one column as LAST_INVOICE (date) in that and I found some of the fileds are ? and some are date in the date column and when I am trying to pull the flat file to my destination table(OLEDB Destination) its giving me and error:

    Example: LAST_INVOICE

    3/02/2012

    13/03/2012

    ?

    22/04/2012

    ?

    ERROR:

    -1071607780,0x,There was an error with input column "LAST_INVOICE" (208) 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.".

    -1071607767,0x,SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (149)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (149)" 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.

    I think this is because , I have ? in my date column so its giving me an error…

    Note: If i have to use Derived Column Transformation can You please let me know the expression and Datatype i need to use.

    Thanks in advance 🙂

  • June 14, 2012 at 12:58 am

    #1500807

    You have two options:

    * reroute all the rows with a ? to another destination or error table/file

    * replace thet ? with an actual data

    Let me know which one you prefer and I'll help you with that one.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 14, 2012 at 7:33 am

    #1500999

    Hi …

    I want to give you some more information on this:

    This file is in FTP Server , Through job using SQL Server Agent it send the data to Local d( drive ) and from there I am pulling the data to my destination table….

    This process is done by assign the job for SSIS Package..This package runs once in a week.

    I tried mannualy copying the file from D Drive and replace all the ? as empty space and I used this file in my SSIS package it works good…

    But im not suppose to do that manually because the data will be coming from FTP server and update the file in D drive and run the package once a week.

    Thanks Koen…

  • June 16, 2012 at 5:41 am

    #1501950

    Hi Swathu9

    These steps should help you to handle the invalid date field in your flat file; you need to add two components after your flat file source loading the text file you are receiving via FTP.

    1. This is your Flat File Source control.

    2. You add a Script Component linking it to your flat file source, as shown in the figure

    3. Then you add a Union All transformation linked to the Script Component you added at step 2.

    4. Double-click on the Script Component to open its editor and click on its Input Columns, search for the column containing the offending dates, and select it, the column will appear on the lower side of the editor.

    5. Make this column Read/Write

    6. Now click on the Script option of the Editor, click on the Edit Script button (not shown on the image) and enter the following code. Notice the code goes in the Input0_ProcessInputRow method, and it is in C# (that's the language I use)

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

    /*

    Add your code here

    */

    try

    {

    DateTime.Parse(Row.SellStartDate);

    }

    catch(Exception ex)

    {

    MessageBox.Show(Row.SellStartDate);

    Row.SellStartDate = "2001-01-01";

    }

    }

    The last step is to link the Union All transformation to the next component in your Data Flow, adding a data viewer may allow you to monitor the actions performed by the script in your data.

    The sample is defaulting the date file to '2001-01-01' you can default to a value that suites your project.

    The column name SellStartDate was the one available in the project used to illustrate this procedure, you should use the column corresponding to your flat file.

    Good Luck.

    Hope this helps,
    Rock from VbCity

  • June 18, 2012 at 1:26 pm

    #1502413

    Hi Rock,

    Here is the errors i am getting when after i followed the steps you have given.

    I have 3 Columns named LAST_INVOICE, LAST_PAYMENT,LAST_ORDER..These 3 Columns i need to do

    Error1'Input0Buffer' does not contain a definition for 'LAST_INVOICE' and no extension method 'LAST_INVOICE' accepting a first argument of type 'Input0Buffer' could be found (are you missing a using directive or an assembly reference?)C:\Users\slagadapati\AppData\Local\Temp\SSIS\SC_19a7fc3801b14e7f952f4b476b7100cc\main.cs3932SC_19a7fc3801b14e7f952f4b476b7100cc

    Error2The name 'MessageBox' does not exist in the current contextC:\Users\slagadapati\AppData\Local\Temp\SSIS\SC_19a7fc3801b14e7f952f4b476b7100cc\main.cs4313SC_19a7fc3801b14e7f952f4b476b7100cc

    Error3'Input0Buffer' does not contain a definition for 'LAST_INVOICE' and no extension method 'LAST_INVOICE' accepting a first argument of type 'Input0Buffer' could be found (are you missing a using directive or an assembly reference?)C:\Users\slagadapati\AppData\Local\Temp\SSIS\SC_19a7fc3801b14e7f952f4b476b7100cc\main.cs4333SC_19a7fc3801b14e7f952f4b476b7100cc

    Error4'Input0Buffer' does not contain a definition for 'LAST_INVOICE' and no extension method 'LAST_INVOICE' accepting a first argument of type 'Input0Buffer' could be found (are you missing a using directive or an assembly reference?)C:\Users\slagadapati\AppData\Local\Temp\SSIS\SC_19a7fc3801b14e7f952f4b476b7100cc\main.cs4417SC_19a7fc3801b14e7f952f4b476b7100cc

    I am sorry to trouble you again. I didn't understand the error message and frankly i don't have any idea on .NET Scripting i just followed the step you have given me...

    Thanks for the lot of information u had provided and new thing i learned is Script Transformation Editor.

  • June 18, 2012 at 11:18 pm

    #1502577

    It seems you missed step 4

    4. Double-click on the Script Component to open its editor and click on its Input Columns, search for the column containing the offending dates, and select it, the column will appear on the lower side of the editor.

    You added the script alright, as the error message is related to the script, but it seems you did not select the variables you will be using inside the script. If you look up the picture, at step 4, you should double click on the Script Component Transformation then click on the Input Columns the scroll on the list of columns appearing on the Available Input Columns until you see the variable LAST_INVOICE select this column, it will appear on the bottom panel, look at the bullet (5) is pointing, change that to READ/WRITE, so, the script will be able to update the variable.

    Please, don't try to do all the columns the first time you are learning this procedure, just do the LAST_INVOICE, once it is up and running, go ahead and add the remaining two columns.

    Cheers,

    Hope this helps,
    Rock from VbCity

  • June 20, 2012 at 9:10 am

    #1503517

    Hi Rocky,

    Thnaks for the information. I did the same what you told me and I’m not getting any error on LAST_INVOICE…

    But I got an error at SHIP_TO_STATE column and its datatype is Varchar(3),NULL…

    Ex: SIP_TO_STATE

    Tx

    ***

    CA

    OH

    ***

    Below is my error:

    OnError,SQLAPD,APD\slagadapati,Data Flow Task,{F591FCE3-05C2-4DE0-984F-9EA2EDABB628},{6674C70F-FBC4-4387-B9D8-C07150ACA4B9},6/20/2012 9:54:52 AM,6/20/2012 9:54:52 AM,-1071607780,0x,There was an error with input column "SHIP_TO_STATE" (198) on input "OLE DB Destination Input" (22). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

    OnError,SQLAPD,APD\slagadapati,Data Flow Task,{F591FCE3-05C2-4DE0-984F-9EA2EDABB628},{6674C70F-FBC4-4387-B9D8-C07150ACA4B9},6/20/2012 9:54:52 AM,6/20/2012 9:54:52 AM,-1071607767,0x,SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (22)" failed because error code 0xC0209078 occurred, and the error row disposition on "input "OLE DB Destination Input" (22)" 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.

    OnError,SQLAPD,APD\slagadapati,Data Flow Task,{F591FCE3-05C2-4DE0-984F-9EA2EDABB628},{6674C70F-FBC4-4387-B9D8-C07150ACA4B9},6/20/2012 9:54:52 AM,6/20/2012 9:54:52 AM,-1073450974,0x,SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (9) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (22). 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.

    OnTaskFailed,SQLAPD,APD\slagadapati,Data Flow Task,{F591FCE3-05C2-4DE0-984F-9EA2EDABB628},{6674C70F-FBC4-4387-B9D8-C07150ACA4B9},6/20/2012 9:54:52 AM,6/20/2012 9:54:52 AM,0,0x,(null)

    PackageEnd,SQLAPD,APD\slagadapati,Package1,{F7EBAD53-1DB8-4E14-9974-8004A7A6EA56},{6674C70F-FBC4-4387-B9D8-C07150ACA4B9},6/20/2012 9:54:52 AM,6/20/2012 9:54:52 AM,1,0x,End of package execution.

    I tried to follow the same steps as you shown me in the picture tho I do ,I got the above error.

    Can you help me out in this….

    Thanks for your time and patients…

  • June 20, 2012 at 2:31 pm

    #1503678

    Hi Rocky,

    You posts help me a lot and i was out of the issue thanks a lot once again..

    This is really helpful for the new beginners...

    Thanks for your patients and explained me every step clearly...

    Cheers,

    Swathi.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply