I am a beginner with MS SQL server 2014. Need some advise with data type conversion from SSIS to SQL server.

I am working with DATE TIME field, an example value : '01272016122044123' which is in format 'MMDDYYYYHHMMSSsss'

From SSIS I have used data type "database timestamp with precision [DT_DBTIMESTAMP2]" and in SQL Server I have used data type "DateTime2(7)".

When I am trying to import the data from SSIS it gives me error "An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid date format".

The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Need to know where exactly i go wrong ?

Thanks Syed Ayaz Mahmud

5

1 Answer

SSIS is super fussy about converting a string to datetime. I would do a script task in a dataflow instead:

1. Select the value from the database as a string
2. Add a script component transformation
3. Create a column in the output buffer that is "database timestamp with precision [DT_DBTIMESTAMP2]," making sure that you adjust the scale to 3

4. Use the following in the script:

   using System.Globalization;
   public override void Input0_ProcessInputRow(Input0Buffer Row)
   { CultureInfo provider = CultureInfo.InvariantCulture; Row.newDate = DateTime.ParseExact(Row.MyDate, "MMddyyyyhhmmssfff", provider);
   }

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.