none
How to Convert String Data type to DateTime

    Question

  • Hi,

    I have a CSV file as source. One of the column in it Date column. The format of the date is like 12-Jul-09. I have to load this data in a table which has the column of datatype datetime. When I am executing the package its is throwing an error. I used Data Conversion Task to convert the above date into datetime. But the only datatype that the date is taking is string.
    Need help with this as how to load the above date to the specified column in the database.  

    Thursday, August 27, 2009 4:30 PM

Answers

  • Hi Kunal

    I dont get it ..
    when source is flat file and some field is coming as string only u have to do the cast convert transformation in package itself and specially when ur destination table is having colunm as datetime ... are we suggesting change in destination column datatype .. load string value as it is from  flat file to database table then do the cast convert ... manually / or probably execute sql task ??
    and with my experience script component if written properly considering proper test cases ... give u much more flexibility and good command over ETL ...
    u can actually pin point the exact row where an exception occurs ..... etc etc ...

     

    Remember we are talking about an extraction -> transforming -> loading. Whenever we need to convert a string like '12-Jul-09' to datetime we know we are in the first phase of the ETL-process i.e. flat file/ csv to a table. One should not load a csv directly or could not load it directly into either a DW or a relational model, many thing can go wrong i.e. constraints/ fk/ pk etc.

    Instead we should extract data from the source, do some simple transforming to comply with a very flexible target table and then load it. Easy in, Easy out!! Small packages and simple logic.

    Your target table is normally located in some sort of a staging database and that table or these tables are hereafter ready for further processing, i.e. converting to your desired data types, manipulating strings etc. (if needed you can use SSIS again for fuzzy-operations, remember the licensing), after this you can do some datamining for identifying anomalies and when that is over you start populating relational tables, creating PK/ FK and utilizing the SQL-engine as it is supposed to.

    You are NOT dealing with a DW loaded directly from CSV, don't behave or threat data like that!!!

    The problem with a script component is the very low re-usability and the lack of just simple debugging, all that stuff you can do much easier in T-SQL, sprocs, functions etc., and if you for some extraordinary reason is in need for very special logic you have the CLR with you and a phenomenal debugging environment!

    And of course you can pinpoint the error just as precise in SQL, and more often SQL handles errors a lot better i.e. function returns null and so. Much easier to locate and see the data in sql than in some non-user friendly SSIS designer where data viewers lacks different things, error flows missing from some components, messy lineageId's when something very trivial changes.
    NO NO don't try to convince anybody that SSIS is the right choice of advanced or just trivial data handling!

    • Edited by Janus007 Thursday, August 27, 2009 8:11 PM
    • Marked as answer by Tony Tang_YJ Thursday, September 03, 2009 6:10 AM
    Thursday, August 27, 2009 8:07 PM
  • You make a great point Kunal - doing the conversion in-memory, in-line with your other transformations is very arguably the better way to go if your source is a plain text file.

    Whether you do the conversion in a Script or in a Derived Column is up to your level of comfort.  (Script feels like overkill to me.)


    Todd McDermid's Blog
    • Marked as answer by Tony Tang_YJ Thursday, September 03, 2009 6:10 AM
    Thursday, August 27, 2009 8:11 PM
  • i agree with yu Todd
    just a small point, you know that lots of customers have multiple tables in one excel sheet  and etc...
    e.g. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa

    the best and the easiet tool to deal with that is SSIS, some cases it's one of the best tools

    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    • Marked as answer by Tony Tang_YJ Thursday, September 03, 2009 6:10 AM
    Thursday, August 27, 2009 8:33 PM

All replies

  • hi,
    please check the following Todd's blog for Converting Strings to Dates in the Derived Column ...

    he has explained each combination for converting string to datetimes. hope this will help you. just you need to check the similar one for your case. :)

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Thursday, August 27, 2009 5:07 PM
  • You have to use a "Script Component" for the date field and chek the date STRING, charectre by charecter to find the DAY , MONTH (which is a string e.g. feb, mar ,..... ) and finally the Year

    and make your self a date string like "YYYY-MM-DD"  and overwrite it in the "Script Component" for the same record, you dont need to convert it because you have it in a right format.
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    • Proposed as answer by Kunal Joshi Thursday, August 27, 2009 7:31 PM
    Thursday, August 27, 2009 5:19 PM
  • Hi Ravi

    You should do such things after the ETL-process, directly using SQL. Not in the SSIS.

    SSIS is not intended for usage like this. Always avoid SSIS for string manipulation etc. The most important thing is to extract the data (The E in ETL), do some minor transformings (The T in ETL) to conform with some very open table data types, then Load (The L in ETL), into your destination staging tables. After this you are ready for whatever you wish for. Maybe do some different work, maybe back into SSIS for the Fuzzy technology and out again.

    But whatever you do, keep the usage of SSIS at an absolut minimum, SSIS isn't a very mature ETL-tool and simple operations can often lead into serious headaches.

    Using SQL it is just a walk in the park... using convert or cast... where ISDATE(field1) = 1

    Don't waste your time in SSIS!
    Thursday, August 27, 2009 6:22 PM
  • Hi
    Janus007 has apoint, do this is SQL server
    e.g. what happend if the script in SSIS was wrong or had a bug, you will have to fix it and deploy the pacakge again, but if it was a sp you just change the SP no need to change the package,
    and lots of other reasons that Janus007  had mentioned and more
    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    Thursday, August 27, 2009 6:27 PM
  • Everyone's entitled to their own opinion, but I must disagree and say that SSIS is intended for usage exactly like this.

    You can opt for a T-SQL approach if you wish - that's certainly an alternative.
    Todd McDermid's Blog
    Thursday, August 27, 2009 6:28 PM

  • Hi Todd
    I think you are wrong, SSIS date-handling is one of the worse I've ever seen (and you know it ;-) )

    Sure ETL by concept is developed with such transformations in mind, but until SSIS is more mature then we are better of doing that in SQL.


    Thursday, August 27, 2009 6:41 PM
  • I dont get it ..
    when source is flat file and some field is coming as string only u have to do the cast convert transformation in package itself and specially when ur destination table is having colunm as datetime ... are we suggesting change in destination column datatype .. load string value as it is from  flat file to database table then do the cast convert ... manually / or probably execute sql task ??
    and with my experience script component if written properly considering proper test cases ... give u much more flexibility and good command over ETL ...
    u can actually pin point the exact row where an exception occurs ..... etc etc ...

     
    Thursday, August 27, 2009 7:30 PM
  • Hi Kunal

    I dont get it ..
    when source is flat file and some field is coming as string only u have to do the cast convert transformation in package itself and specially when ur destination table is having colunm as datetime ... are we suggesting change in destination column datatype .. load string value as it is from  flat file to database table then do the cast convert ... manually / or probably execute sql task ??
    and with my experience script component if written properly considering proper test cases ... give u much more flexibility and good command over ETL ...
    u can actually pin point the exact row where an exception occurs ..... etc etc ...

     

    Remember we are talking about an extraction -> transforming -> loading. Whenever we need to convert a string like '12-Jul-09' to datetime we know we are in the first phase of the ETL-process i.e. flat file/ csv to a table. One should not load a csv directly or could not load it directly into either a DW or a relational model, many thing can go wrong i.e. constraints/ fk/ pk etc.

    Instead we should extract data from the source, do some simple transforming to comply with a very flexible target table and then load it. Easy in, Easy out!! Small packages and simple logic.

    Your target table is normally located in some sort of a staging database and that table or these tables are hereafter ready for further processing, i.e. converting to your desired data types, manipulating strings etc. (if needed you can use SSIS again for fuzzy-operations, remember the licensing), after this you can do some datamining for identifying anomalies and when that is over you start populating relational tables, creating PK/ FK and utilizing the SQL-engine as it is supposed to.

    You are NOT dealing with a DW loaded directly from CSV, don't behave or threat data like that!!!

    The problem with a script component is the very low re-usability and the lack of just simple debugging, all that stuff you can do much easier in T-SQL, sprocs, functions etc., and if you for some extraordinary reason is in need for very special logic you have the CLR with you and a phenomenal debugging environment!

    And of course you can pinpoint the error just as precise in SQL, and more often SQL handles errors a lot better i.e. function returns null and so. Much easier to locate and see the data in sql than in some non-user friendly SSIS designer where data viewers lacks different things, error flows missing from some components, messy lineageId's when something very trivial changes.
    NO NO don't try to convince anybody that SSIS is the right choice of advanced or just trivial data handling!

    • Edited by Janus007 Thursday, August 27, 2009 8:11 PM
    • Marked as answer by Tony Tang_YJ Thursday, September 03, 2009 6:10 AM
    Thursday, August 27, 2009 8:07 PM
  • I'm afraid I don't agree with your claim at all Janus.  I don't understand how SSIS's date handling is "poor" - it's almost identical to T-SQL date manipulation, specifically with regards to conversion to and from string types.  The only thing I see users making mistakes with is in their assumption that SSIS (or any tool) will "magically" figure out their cryptic date formats for them.  T-SQL, Excel, or any other tool would make the same (incorrect) conversions given ambiguous inputs.  Simply by converting their ambiguous date strings to the standard ISO/SQL format of YYYY-MM-DD makes SSIS date conversions unambiguous - and I do (and recommend) the same action be taken when using any tool - T-SQL and Excel included.

    SSIS definitely has rough edges that could make using it better, but I'm not sure that "date handling" is one of them.  Sure, it could use some more functions in the expression language, such as ISNUMERIC, but I have to disagree that "T-SQL is better" for this kind of manipulation.  IMO, the "self-documenting" and easy understanding/debugging of SSIS packages usually (but not always) beats the T-SQL equivalent.
    Todd McDermid's Blog
    Thursday, August 27, 2009 8:09 PM
  • You make a great point Kunal - doing the conversion in-memory, in-line with your other transformations is very arguably the better way to go if your source is a plain text file.

    Whether you do the conversion in a Script or in a Derived Column is up to your level of comfort.  (Script feels like overkill to me.)


    Todd McDermid's Blog
    • Marked as answer by Tony Tang_YJ Thursday, September 03, 2009 6:10 AM
    Thursday, August 27, 2009 8:11 PM
  • i agree with yu Todd
    just a small point, you know that lots of customers have multiple tables in one excel sheet  and etc...
    e.g. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/20944506-5276-41e7-8b65-7f36d6e4bdaa

    the best and the easiet tool to deal with that is SSIS, some cases it's one of the best tools

    Sincerely SH -- Please kindly don’t forget to mark the post(s) that answered your question and/or vote for the post
    • Marked as answer by Tony Tang_YJ Thursday, September 03, 2009 6:10 AM
    Thursday, August 27, 2009 8:33 PM
  • Thanks a lot guys for your suggestions and solutions
    Friday, August 28, 2009 12:43 PM