February 24, 2011 at 7:09 am
Using data task flow
in source table I am using an SQL command (query text) I have:
[FILE_RUN_DATE] [varchar](8) NULL,
in destination It is mappied to this column:
[FileRunDate] [datetime] NULL,
a. can I convert it in the query text of the SQLCommand of the source table or do I have to use the conversion step.
b. If I use ssis data converter what data type do I use for date that is date but only 8 charactes. I thought there was a datatype for just date and no time
c. I can preferrebly convert it in the SQL command of source query, what data type of date is 8 character. Am I going crazy or is there a data type with 8 characters. smalldatetime has seconds but just another range.
February 24, 2011 at 11:48 am
adam spencer (2/24/2011)
Using data task flowin source table I am using an SQL command (query text) I have:
[FILE_RUN_DATE] [varchar](8) NULL,
in destination It is mappied to this column:
[FileRunDate] [datetime] NULL,
a. can I convert it in the query text of the SQLCommand of the source table or do I have to use the conversion step.
b. If I use ssis data converter what data type do I use for date that is date but only 8 charactes. I thought there was a datatype for just date and no time
c. I can preferrebly convert it in the SQL command of source query, what data type of date is 8 character. Am I going crazy or is there a data type with 8 characters. smalldatetime has seconds but just another range.
In reverse order:
A for c) There is no data type with 8 characters. Data types have bytes, though, and several of them are 8 bytes. So I think you might be mixing up terms in your head. We've all done that.
A for b) When you use the data converter task, you will need to add in delimiters to your string and then convert it to datetime (database timestamp is the SSIS data type I believe). Delimiters can be / or -. I usually use / to separate out the month, day, and year.
A for a) Your source is an OLE DB Source, right? Then, yes, you can convert the 8 character string to a date in your T-SQL (or stored procedure). I advise a similar methodology to my answer for question b, but look up Convert() in Books Online so you get the proper style for your date conversion. It would look something like this: Convert(datetime,(Substring(MyVal,1,4) + '-' + ... ),101)
Remove the ellipses, of course, and enter in the rest of the code to make it work.
February 24, 2011 at 11:48 am
SSIS doesn't have a date datatype, so in my opinion the best option is to do the conversion immediately in the source query.
20110224 --> 8 characters.
You'll need the following function:
CONVERT(DATE,[FILE_RUN_DATE])
I'm not sure, but I'm afraid SSIS will append a time portion to the data type to conform the value to it's internal datatime datatype.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 24, 2011 at 11:50 am
Koen Verbeeck (2/24/2011)
SSIS doesn't have a date datatype, so in my opinion the best option is to do the conversion immediately in the source query.
The name is deceptive (database timestamp) but it is a date data type. I use it all the time.
EDIT: Deceptive because "timestamp" in this instance isn't a timestamp in the SQL Engine sense of the word. It actually is a date and time.
EDIT EDIT: I just tested this. Went into a package, threw in a T-SQL command to pull from a table I have with dates in it. I pulled the datetime column into a data conversion editor and "database timestamp" did automatically come up as the data type without me doing anything else.
February 24, 2011 at 11:55 am
Brandie Tarvin (2/24/2011)
Koen Verbeeck (2/24/2011)
SSIS doesn't have a date datatype, so in my opinion the best option is to do the conversion immediately in the source query.The name is deceptive (database timestamp) but it is a date data type. I use it all the time.
EDIT: Deceptive because "timestamp" in this instance isn't a timestamp in the SQL Engine sense of the word. It actually is a date and time.
EDIT EDIT: I just tested this. Went into a package, threw in a T-SQL command to pull from a table I have with dates in it. I pulled the datetime column into a data conversion editor and "database timestamp" did automatically come up as the data type without me doing anything else.
You are right. The names are deceptive and I don't have my SSIS references with me (I could've looked it up in Google, but I guess I was too lazy :-D).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply