October 31, 2018 at 3:55 pm
I've got an SSIS project pulling data from a MySQL database into SQL Server.
One of the date values in the MySQL table is coming across as DT_BYTE. I believe in the MySQL table this is stored as a binary date (similar to SQL Server timestamp) to enforce uniqueness.
Is there a way for me to convert this into something readable as a date? I attempted converting to string and was given a completely jibberish value; it didn't even look like binary.
Unfortunately I do need this date field. Otherwise I would just ignore it.
Thank you
October 31, 2018 at 3:58 pm
jklenk86 - Wednesday, October 31, 2018 3:55 PMI've got an SSIS project pulling data from a MySQL database into SQL Server.One of the date values in the MySQL table is coming across as DT_BYTE. I believe in the MySQL table this is stored as a binary date (similar to SQL Server timestamp) to enforce uniqueness.
Is there a way for me to convert this into something readable as a date? I attempted converting to string and was given a completely jibberish value; it didn't even look like binary.
Unfortunately I do need this date field. Otherwise I would just ignore it.
Thank you
If it is like the timestamp (now rowversion) data type then it really isn't a date or date/time value. Not having used MySQL this is simply a guess and you should verify by checking the latest MySQL documentation.
October 31, 2018 at 4:01 pm
Please post a handful of the "0x" values from the table you loaded those DT_BYTE values into so we can have a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 31, 2018 at 4:13 pm
Jeff Moden - Wednesday, October 31, 2018 4:01 PMPlease post a handful of the "0x" values from the table you loaded those DT_BYTE values into so we can have a look.
An example would be : 0x323031342D30342D3037
In further analysis, these also are not unique, as this particular value represents 04/07/2014 on multiple rows.
Should I just be able to convert this to String, and then again to smalldatetime?
Edit to add: this is not appearing as formatted binary on the way out. I do not have a '0x' in the value, just the numbers following.
October 31, 2018 at 4:27 pm
jklenk86 - Wednesday, October 31, 2018 4:13 PMJeff Moden - Wednesday, October 31, 2018 4:01 PMPlease post a handful of the "0x" values from the table you loaded those DT_BYTE values into so we can have a look.An example would be : 0x323031342D30342D3037
In further analysis, these also are not unique, as this particular value represents 04/07/2014 on multiple rows.
Should I just be able to convert this to String, and then again to smalldatetime?
Edit to add: this is not appearing as formatted binary on the way out. I do not have a '0x' in the value, just the numbers following.
You may want to post more than one with the date it represents.
October 31, 2018 at 4:30 pm
Lynn Pettis - Wednesday, October 31, 2018 4:27 PMjklenk86 - Wednesday, October 31, 2018 4:13 PMJeff Moden - Wednesday, October 31, 2018 4:01 PMPlease post a handful of the "0x" values from the table you loaded those DT_BYTE values into so we can have a look.An example would be : 0x323031342D30342D3037
In further analysis, these also are not unique, as this particular value represents 04/07/2014 on multiple rows.
Should I just be able to convert this to String, and then again to smalldatetime?
Edit to add: this is not appearing as formatted binary on the way out. I do not have a '0x' in the value, just the numbers following.
You may want to post more than one with the date it represents.
I mean it'll all be the same, really.
| 323031342D30342D3037 |
| 323031342D30352D3133 |
| 323031342D30352D3134 |
| 323031342D30352D3232 |
| 323031342D30362D3035 |
| 323031342D30362D3036 |
| 323031342D30362D3039 |
| 323031342D30362D3130 |
| 323031342D30362D3131 |
Manually adding 0x in front, I can run this: SELECT CAST(CAST(0x323031342D30342D3037 AS VARCHAR(10)) AS SMALLDATETIME)
And I get a proper date with a midnight timestamp.
The issue is, how do I append 0x in front of the raw values from the source, and then convert that to a smalldatetime inside the Data Flow Task of my SSIS package?
October 31, 2018 at 6:47 pm
jklenk86 - Wednesday, October 31, 2018 4:30 PMLynn Pettis - Wednesday, October 31, 2018 4:27 PMjklenk86 - Wednesday, October 31, 2018 4:13 PMJeff Moden - Wednesday, October 31, 2018 4:01 PMPlease post a handful of the "0x" values from the table you loaded those DT_BYTE values into so we can have a look.An example would be : 0x323031342D30342D3037
In further analysis, these also are not unique, as this particular value represents 04/07/2014 on multiple rows.
Should I just be able to convert this to String, and then again to smalldatetime?
Edit to add: this is not appearing as formatted binary on the way out. I do not have a '0x' in the value, just the numbers following.
You may want to post more than one with the date it represents.
I mean it'll all be the same, really.
323031342D30342D3037 323031342D30352D3133 323031342D30352D3134 323031342D30352D3232 323031342D30362D3035 323031342D30362D3036 323031342D30362D3039 323031342D30362D3130 323031342D30362D3131 Manually adding 0x in front, I can run this:
SELECT CAST(CAST(0x323031342D30342D3037 AS VARCHAR(10)) AS SMALLDATETIME)And I get a proper date with a midnight timestamp.
The issue is, how do I append 0x in front of the raw values from the source, and then convert that to a smalldatetime inside the Data Flow Task of my SSIS package?
If the column you're getting this from is a BINARY datatype, then no need for the 0X. Just CONVERT to CHAR(10) -- no need for VARCHAR.
If the column you're getting this from is character based, still no need for the 0x... you just need one more CONVERT with the binary format of "2". Like this...
SELECT
CONVERT(CHAR(10),CONVERT(BINARY(10),'323031342D30342D3037',2))
If the column is some other datatype, let us know.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 3, 2018 at 8:49 pm
So I did further digging. I have never seen this data type before. the MySQL data type is set to DATE, however when pulling through SSIS Data Viewer, the data looks like this:
For reference, I am using a 32 bit ANSII DSN for the MySQL connection.
November 4, 2018 at 7:56 am
jklenk86 - Saturday, November 3, 2018 8:49 PMSo I did further digging. I have never seen this data type before. the MySQL data type is set to DATE, however when pulling through SSIS Data Viewer, the data looks like this:
- 0x32 0x30 0x31 0x34 0x2D 0x30 0x34 0x 2D 0x30 0x37
Does anyone recognize this? Is there a way to convert this in SSIS, or will this require some conversion prior to pulling this?For reference, I am using a 32 bit ANSII DSN for the MySQL connection.
It's "just" hexadecimal ASCII. Each character has a "0x" before it, which is just an indication that the data is hexadecimal. The numbers 30 thru 39 represent digits of 0 thru 10 (decimal 48 thru 57, if you're interested).
Is this appearing as a single string/field or 10 separate strings/fields?
If you're really interested in such conversions, see the chart at the following link.
http://www.asciitable.com/
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2018 at 8:20 am
If it appears as a single string, then the following will do the trick for you, depending on your need..
--===== This is just simplified test data and is not a part of the solution.
-- The variable is being used instead of a column and can easily be replaced by
-- in the formulas below.
DECLARE @HexDateString CHAR(49) = '0x32 0x30 0x31 0x34 0x2D 0x30 0x34 0x2D 0x30 0x37'
;
--===== This converts the hex data to a human readable string
SELECT CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2))
;
--===== This converts the hex data to a DATE datatype.
-- Same as above but just added one more conversion.
SELECT CONVERT(DATE,CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2)))
;
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 7:12 am
Jeff Moden - Sunday, November 4, 2018 8:20 AMIf it appears as a single string, then the following will do the trick for you, depending on your need..
--===== This is just simplified test data and is not a part of the solution.
-- The variable is being used instead of a column and can easily be replaced by
-- in the formulas below.
DECLARE @HexDateString CHAR(49) = '0x32 0x30 0x31 0x34 0x2D 0x30 0x34 0x2D 0x30 0x37'
;
--===== This converts the hex data to a human readable string
SELECT CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2))
;
--===== This converts the hex data to a DATE datatype.
-- Same as above but just added one more conversion.
SELECT CONVERT(DATE,CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2)))
;
This works well in SSMS, thank you. I was able to - using an SSIS Data Conversion components - do the conversion up to a human readable string: '0x323031342D30342D3037'
What I am struggling with now is how to convert this once more to a Date in SSIS. Just using another Data Conversion component doesn't seem to work. If I use an OLEDB Command to write T-SQL, it states it is unable to convert between Unicode and Non-Unicode, even if I make everything Unicode all the way through the project.
Is there a way to utilize the code you've written in an SSIS component as data is moving through?
November 5, 2018 at 7:23 am
jklenk86 - Monday, November 5, 2018 7:12 AMJeff Moden - Sunday, November 4, 2018 8:20 AMIf it appears as a single string, then the following will do the trick for you, depending on your need..
--===== This is just simplified test data and is not a part of the solution.
-- The variable is being used instead of a column and can easily be replaced by
-- in the formulas below.
DECLARE @HexDateString CHAR(49) = '0x32 0x30 0x31 0x34 0x2D 0x30 0x34 0x2D 0x30 0x37'
;
--===== This converts the hex data to a human readable string
SELECT CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2))
;
--===== This converts the hex data to a DATE datatype.
-- Same as above but just added one more conversion.
SELECT CONVERT(DATE,CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2)))
;This works well in SSMS, thank you. I was able to - using an SSIS Data Conversion components - do the conversion up to a human readable string: '0x323031342D30342D3037'
What I am struggling with now is how to convert this once more to a Date in SSIS. Just using another Data Conversion component doesn't seem to work. If I use an OLEDB Command to write T-SQL, it states it is unable to convert between Unicode and Non-Unicode, even if I make everything Unicode all the way through the project.
Is there a way to utilize the code you've written in an SSIS component as data is moving through?
Heh... total disclosure and a bit of an apology.... I don't do work in SSIS because of things like this. In fact, one of my primary jobs in the past was to convert all SSIS packages to stored procedures and jobs. Someone else will have to help on that final conversion in SSIS.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 8:30 am
Jeff Moden - Monday, November 5, 2018 7:23 AMjklenk86 - Monday, November 5, 2018 7:12 AMJeff Moden - Sunday, November 4, 2018 8:20 AMIf it appears as a single string, then the following will do the trick for you, depending on your need..
--===== This is just simplified test data and is not a part of the solution.
-- The variable is being used instead of a column and can easily be replaced by
-- in the formulas below.
DECLARE @HexDateString CHAR(49) = '0x32 0x30 0x31 0x34 0x2D 0x30 0x34 0x2D 0x30 0x37'
;
--===== This converts the hex data to a human readable string
SELECT CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2))
;
--===== This converts the hex data to a DATE datatype.
-- Same as above but just added one more conversion.
SELECT CONVERT(DATE,CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2)))
;This works well in SSMS, thank you. I was able to - using an SSIS Data Conversion components - do the conversion up to a human readable string: '0x323031342D30342D3037'
What I am struggling with now is how to convert this once more to a Date in SSIS. Just using another Data Conversion component doesn't seem to work. If I use an OLEDB Command to write T-SQL, it states it is unable to convert between Unicode and Non-Unicode, even if I make everything Unicode all the way through the project.
Is there a way to utilize the code you've written in an SSIS component as data is moving through?
Heh... total disclosure and a bit of an apology.... I don't do work in SSIS because of things like this. In fact, one of my primary jobs in the past was to convert all SSIS packages to stored procedures and jobs. Someone else will have to help on that final conversion in SSIS.
(DT_DATE)((DT_STR,10,1252)SUBSTRING(Date,2,1) + SUBSTRING(Date,4,1) + SUBSTRING(Date,6,1) + SUBSTRING(Date,8,1) + "-" + SUBSTRING(Date,12,1) + SUBSTRING(Date,14,1) + "-" + SUBSTRING(Date,18,1) + SUBSTRING(Date,20,1))
Far away is close at hand in the images of elsewhere.
Anon.
November 5, 2018 at 9:00 am
David Burrows - Monday, November 5, 2018 8:30 AMJeff Moden - Monday, November 5, 2018 7:23 AMjklenk86 - Monday, November 5, 2018 7:12 AMJeff Moden - Sunday, November 4, 2018 8:20 AMIf it appears as a single string, then the following will do the trick for you, depending on your need..
--===== This is just simplified test data and is not a part of the solution.
-- The variable is being used instead of a column and can easily be replaced by
-- in the formulas below.
DECLARE @HexDateString CHAR(49) = '0x32 0x30 0x31 0x34 0x2D 0x30 0x34 0x2D 0x30 0x37'
;
--===== This converts the hex data to a human readable string
SELECT CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2))
;
--===== This converts the hex data to a DATE datatype.
-- Same as above but just added one more conversion.
SELECT CONVERT(DATE,CONVERT(CHAR(10),CONVERT(BINARY(10),REPLACE(REPLACE(@HexDateString,'0x',''),' ',''),2)))
;This works well in SSMS, thank you. I was able to - using an SSIS Data Conversion components - do the conversion up to a human readable string: '0x323031342D30342D3037'
What I am struggling with now is how to convert this once more to a Date in SSIS. Just using another Data Conversion component doesn't seem to work. If I use an OLEDB Command to write T-SQL, it states it is unable to convert between Unicode and Non-Unicode, even if I make everything Unicode all the way through the project.
Is there a way to utilize the code you've written in an SSIS component as data is moving through?
Heh... total disclosure and a bit of an apology.... I don't do work in SSIS because of things like this. In fact, one of my primary jobs in the past was to convert all SSIS packages to stored procedures and jobs. Someone else will have to help on that final conversion in SSIS.
(DT_DATE)((DT_STR,10,1252)SUBSTRING(Date,2,1) + SUBSTRING(Date,4,1) + SUBSTRING(Date,6,1) + SUBSTRING(Date,8,1) + "-" + SUBSTRING(Date,12,1) + SUBSTRING(Date,14,1) + "-" + SUBSTRING(Date,18,1) + SUBSTRING(Date,20,1))
Thanks, David. With the understanding that I don't know SSIS syntax, would (DT_DATE)((DT_STR,10,1252)SUBSTRING(Date,2,10) do the same thing or do you really have to do the character by character conversion using individual SUBSTRING(pos,1) like you did?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2018 at 9:21 am
Jeff Moden - Monday, November 5, 2018 9:00 AMThanks, David. With the understanding that I don't know SSIS syntax, would (DT_DATE)((DT_STR,10,1252)SUBSTRING(Date,2,10) do the same thing or do you really have to do the character by character conversion using individual SUBSTRING(pos,1) like you did?
SSIS has no HEX to string conversion functionality, only integer to HEX.
Typical MS :angry:
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply