Data Developers

Problem: I am trying to load some data from csv file to my SQL server. There is a column for Date which has data type Unicode (WSTR) data type in csv file and the column for storing date in SQL server is of Datetime data type. When I used DATA CONVERSION transformation to convert WSTR data to DBTIMESTAMP data, it got changed but with an error that it interchanged the month and date which gives me the wrong date. Like Date should be like 2019-09-03 (For 3rd Sep 2019), but it gives me 2019-03-09. Please suggest what's the issue i am facing? Solution: Problem cause This may occurs when converting a string to a date value without specifying the date format. Reffering to the  SSIS data conversion transformation official documentation : If you are converting data to a date or a datetime data type, the date in the output column is in the ISO format, although the locale preference may specify a different format. Assume that the data is stored in the csv file with the...

Problem: I'm working on an SSIS ELT script that needs to parse dates from a TSV file that are stored in the format  [INTEGER].[INTEGER]  ( Excel integer dates  followed by second since midnight, e.g., 42825.94097; or microseconds since midnight, e.g., 42831.1229166667). I've come up with the following approach: Derived Column function to split the input into a date part and a time part Derived Column function to append the parsed dates together, e.g., DATEADD ( "day" , StartTime_Date , DATEADD ( "second" , StartTime_Time ,( DT_DATE ) "1/1/1900" )) Is there a more elegant way to do this without resorting to a Script Component? Solution: The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point valu...

Problem: I have trade_date column which looks like this : Trade_date 10-02-2012 1-23-2014 feb-14-2016 1 / 2 / 2012 01 / 02 / 2012 01 / 01 / 12 2014 / 10 / 26 I want to have one unified format after transformation which is MM/dd/yyyy. Does anyone have this problem before or anyone know how to fix this problem? Solution: You need a Script Component to do that, since you are looking to convert multiple date formats. First, you have to create an array of strings that contains all formats needed then you should use  DateTime.ParseExact()  function After adding a Script Component, Make sure you add a New Output Column of type string (Or date if you are looking to convert values into date column), then use the following lines of code within the script: string [] formats = { "dd-MM-yyyy" , "yyyy-MM-dd" , "d-M-yyyy" , "MMM-dd-yyyy" , "dd/MM/yy" , "yyyy/MM/dd" , "dd/MM/yyyy" , "}; Row . outColumn = ...

Problem: How to remove from DateTime variable hours, minutes, seconds and other parts in SSIS If I have DateTime like  21 jul 2019 8:30:05 , and I want have it as  21 jul 2019 00:00:00  and still as DateTime not string Solution: You can achieve that using a derived column: ( DT_DBTIMESTAMP )( DT_DBDATE )@[ User :: DateTimeVariable ] Casting to  DT_DBDATE  will remove the time part, then recasting to  DT_DBTIMESTAMP  will re-add a time part but with  12:00 AM  value =  00:00:00 Example: Original post:  https://stackoverflow.com/questions/57221410/how-to-remove-from-datetime-variable-hours-minutes-seconds-and-other-parts-in/57221736#57221736