none
SSIS Script - System.FormatException: String was not recognized as a valid DateTime

    Question

  • I am importing data from CSV file to SQL using SSIS. The SSIS job has been running perfectly fine for last few months but suddenly it gives error message while running a script. The SSIS job is not changed at all.

    It gives error while running a script which calculates maximum date from three date.

    I am not sure what is causing the error as it's been running fine always. Error is as below.

    [Script Component [7437]] Error: System.FormatException: String was not recognized as a valid DateTime.
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
       at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
       at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)

    Thanks in advance!

    Wednesday, March 12, 2014 4:29 AM

Answers

  • I thing in your CSV file contain in the O character instead 0 zero.

    So, open the CSV in Excel, and try to change the type of the column to Date type, if it give an error, so you must clean your data before load it in SQL Server, if it not give an error, so the problem is your SSIS, in this case try the install the SP of SSIS or the latest updates.

    • Marked as answer by SB_KS Thursday, March 13, 2014 9:21 PM
    Thursday, March 13, 2014 2:20 AM

All replies

  • Whats the format in which date values are coming? It may be that data contain some spurious values in different format or outside range.

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, March 12, 2014 5:22 AM
  • Check whether the format of date is valid or not from CSV File.

    Regards

    Naveen

    Wednesday, March 12, 2014 5:40 AM
  • The date is formatted as "ShortDate" in csv file. When it's imported in SSIS, it comes as String. I tried to change that to (DT_Date) in SSIS but it gives me different error. It looks like the Script is setup to accept String and converts to Datetime. Some date fields have blanks but it was the same in past when it loaded successfully.

    Thanks for the reply!

    Wednesday, March 12, 2014 9:10 PM
  • If you are using a script component, I would create a second output and write each row that gives you an error (just use try catch syntax).  Then you can see exactly what values are creating errors.

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, March 13, 2014 1:57 AM
  • I thing in your CSV file contain in the O character instead 0 zero.

    So, open the CSV in Excel, and try to change the type of the column to Date type, if it give an error, so you must clean your data before load it in SQL Server, if it not give an error, so the problem is your SSIS, in this case try the install the SP of SSIS or the latest updates.

    • Marked as answer by SB_KS Thursday, March 13, 2014 9:21 PM
    Thursday, March 13, 2014 2:20 AM
  • The csv file had all the fields in correct format. The issue is the install of SSIS. I tried to run the same job from different machine and ran fine with no error. Thank you all for taking time to help with the issue.

    Thursday, March 13, 2014 9:23 PM