September 3, 2018 at 1:00 pm
Hi Experts,
I have a text file to load to a database.The file has a column Datetime 2013-07-16 15:05:43 and in Database i have two separate colums 1.Date How can i apply the logic to separate 2013-07-16 15:05:43 into each column.What expressions must i use in SSIS.
2.Time
Please help.
Many thanks
T
September 3, 2018 at 7:29 pm
Don't do it. Don't split date from time and put them in separate columns. It will cause you more problems than you can shake a stick at. If you absolutely must, create computed columns in the table to do the separation but keep that original column together.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2018 at 1:44 am
Thanks,the database was set up like that and i am new with the company it's something i cannot change.
Any help?
Thanks
T
September 4, 2018 at 5:35 am
There are a couple of ways you can do this.
1) Build the file import connection manager to treat date and time as two separate import columns. This is easiest if you can do it because date and time should always have the same number of characters, 10 for date and 8 for time with a space in between.
2) Put a Derived Column Transformation Step in a Data Export Task. Alter the column to CHAR or VARCHAR and use SUBSTRING on it to split them out. Basically, you'll need a Type Cast (you can pull it into the Condition) and then a String Function. I usually use the DT_STR Type Cast. The length needs to be the max number of characters you expect on this column, the code ... depends on your system and language defaults. For U.S., I use 1252. This entire thing goes before the column name. Then SUBSTRING goes around all of this, and works just like in normal T-SQL. You'll need to do 2 columns, one for date and one for time.
FYI: You can outside the SUBSTRING for the date cast it back to date (DT_DATE, I think, but doublecheck that) and you should be able to do the same for the time column, but I don't know if it's DT_DBTIME or DT_DBTIME2. Before you do this, though, make sure the SUBSTRING is working correctly so you have what you need in character form at the very least.
September 4, 2018 at 5:46 am
tmmutsetse - Tuesday, September 4, 2018 1:44 AMThanks,the database was set up like that and i am new with the company it's something i cannot change.Any help?
ThanksT
Understood, especially on the being new thing. That, notwithstanding, I'd make the suggestion to them. They might respond if you told them that one of the heavy hitters on SQL Server Central suggested it.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2018 at 5:49 am
Jeff Moden - Tuesday, September 4, 2018 5:46 AMtmmutsetse - Tuesday, September 4, 2018 1:44 AMThanks,the database was set up like that and i am new with the company it's something i cannot change.Any help?
ThanksT
Understood, especially on the being new thing. That, notwithstanding, I'd make the suggestion to them. They might respond if you told them that one of the heavy hitters on SQL Server Central suggested it.
I agree with Jeff, for the record. If you can convince them to change this (or add a new column that is datetime so current functionality doesn't get disrupted while the other change is being made) it will make life much easier for you and them.
September 4, 2018 at 5:51 am
Brandie Tarvin - Tuesday, September 4, 2018 5:35 AMThere are a couple of ways you can do this.1) Build the file import connection manager to treat date and time as two separate import columns. This is easiest if you can do it because date and time should always have the same number of characters, 10 for date and 8 for time with a space in between.
2) Put a Derived Column Transformation Step in a Data Export Task. Alter the column to CHAR or VARCHAR and use SUBSTRING on it to split them out. Basically, you'll need a Type Cast (you can pull it into the Condition) and then a String Function. I usually use the DT_STR Type Cast. The length needs to be the max number of characters you expect on this column, the code ... depends on your system and language defaults. For U.S., I use 1252. This entire thing goes before the column name. Then SUBSTRING goes around all of this, and works just like in normal T-SQL. You'll need to do 2 columns, one for date and one for time.
FYI: You can outside the SUBSTRING for the date cast it back to date (DT_DATE, I think, but doublecheck that) and you should be able to do the same for the time column, but I don't know if it's DT_DBTIME or DT_DBTIME2. Before you do this, though, make sure the SUBSTRING is working correctly so you have what you need in character form at the very least.
This is one of just many reasons why I suggest loading it into a "whole" DATETIME column and then let the computed columns split it.
I also hope that they're doing the loads into a staging table and validating the data before they hit the final table. Even if they want to make the mistake of storing the split DATE AND TIME, the staging table could be easily equipped to do so.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2018 at 8:26 am
Thank you so much.
@Brandie Tarvin i am grateful for your explaination however i am not coming right i, think i am missing something.Kindly would you mind helping the above with an example.I am still getting familiar with SSIS.
Many Thanks
T.
September 4, 2018 at 8:44 am
tmmutsetse - Tuesday, September 4, 2018 8:26 AMThank you so much.
@Brandie Tarvin i am grateful for your explaination however i am not coming right i, think i am missing something.Kindly would you mind helping the above with an example.I am still getting familiar with SSIS.Many Thanks
T.
Unfortunately I can't give you an exact example because I don't know any of your names or data sources. If you're having issues with this, I suggest you do as Jeff suggested. Import the text file as is into a staging table on your database and use T-SQL to shred the datetime column into the final destination.
In fact, as Jeff mentioned, it's the easiest solution. Using the DTS Import / Export wizard, you can create a package that you can use for later text file imports (if this is more than a one time thing) and then just create a job around it with stored procedures or T-SQL in-job scripts that does an INSERT from the staging table to the final destination. https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/save-ssis-package-sql-server-import-and-export-wizard?view=sql-server-2017
Here are a few links to get you started if you want to go the SSIS route, though.
Data Flow Task - http://www.itdeveloperzone.com/2016/02/import-flat-file-to-sql-server-ssis.html
Derived Column Transformation:
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/derived-column-transformation?view=sql-server-2017
https://www.codeproject.com/Articles/1074421/Step-by-Step-SSIS-Derived-Column-Transformation-TI
http://sqlblog.com/blogs/andy_leonard/archive/2009/02/04/ssis-expression-language-and-the-derived-column-transformation.aspx
September 4, 2018 at 12:00 pm
If at all possible - import to a staging table as a single datetime column and then from that you can move the data to your target table as separate columns. If you cannot do that - then you need to define the column as a string in your connection manager with a length of 20 (19 would work also).
In your data flow - add a derived columns transformation between the source and destination. In this transformation you are going to setup new columns '<add as new column>' and then you can use this for the date column: TRIM(TOKEN([your date field]," ",1)), and this: TRIM(TOKEN([your date field]," ",2)) for the time column. Since the date format is already set to YYYY-MM-DD you don't need to do anything else to get that date to parse correctly into a DATE column in SQL Server. The time column will be HH:MM:SS which will also parse correctly into a time data type in SQL Server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 4, 2018 at 12:03 pm
TOKEN?
I had not heard of that one. I shall have to try it.
September 5, 2018 at 7:45 am
Jeff Moden - Monday, September 3, 2018 7:29 PMDon't do it. Don't split date from time and put them in separate columns. It will cause you more problems than you can shake a stick at. If you absolutely must, create computed columns in the table to do the separation but keep that original column together.
There have been several posts after this, but there can be good reasons to split this. If it's going into a DW environment you have to. Date and time information need to be in separate dimensions. Otherwise the whole thing becomes unwieldy. You may have run into situations where this caused issues. But the addition of the date and time data types has been a huge help for me. Having this as an absolute rule is not a good idea. In this case (unlike all too many normalization issues) it really does depend.
September 5, 2018 at 10:46 am
RonKyle - Wednesday, September 5, 2018 7:45 AMJeff Moden - Monday, September 3, 2018 7:29 PMDon't do it. Don't split date from time and put them in separate columns. It will cause you more problems than you can shake a stick at. If you absolutely must, create computed columns in the table to do the separation but keep that original column together.There have been several posts after this, but there can be good reasons to split this. If it's going into a DW environment you have to. Date and time information need to be in separate dimensions. Otherwise the whole thing becomes unwieldy. You may have run into situations where this caused issues. But the addition of the date and time data types has been a huge help for me. Having this as an absolute rule is not a good idea. In this case (unlike all too many normalization issues) it really does depend.
If you do need to have it split, don't do it at the expense of not having the complete date and time. Import the whole date/time and let a persisted computed column do the split so that when you need it, you actually end up with something that's both SARGable and is capable of temporal accuracy then it comes to "wrap arounds" with dates. For example, things like shifts (an other similar things) that straddle a day play hell on the separated columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 5, 2018 at 11:17 am
Jeff Moden - Wednesday, September 5, 2018 10:46 AMRonKyle - Wednesday, September 5, 2018 7:45 AMJeff Moden - Monday, September 3, 2018 7:29 PMDon't do it. Don't split date from time and put them in separate columns. It will cause you more problems than you can shake a stick at. If you absolutely must, create computed columns in the table to do the separation but keep that original column together.There have been several posts after this, but there can be good reasons to split this. If it's going into a DW environment you have to. Date and time information need to be in separate dimensions. Otherwise the whole thing becomes unwieldy. You may have run into situations where this caused issues. But the addition of the date and time data types has been a huge help for me. Having this as an absolute rule is not a good idea. In this case (unlike all too many normalization issues) it really does depend.
If you do need to have it split, don't do it at the expense of not having the complete date and time. Import the whole date/time and let a persisted computed column do the split so that when you need it, you actually end up with something that's both SARGable and is capable of temporal accuracy then it comes to "wrap arounds" with dates. For example, things like shifts (an other similar things) that straddle a day play hell on the separated columns.
I don't run into any of these issues when splitting it. Both the date and time have value in themselves and the two columns are easily combined. I understand why the source database combines them. Normally that makes sense in an operational database. But without further questioning, I don't think it's a good idea to say it's automatically a bad idea.
September 5, 2018 at 11:36 am
RonKyle - Wednesday, September 5, 2018 11:17 AMJeff Moden - Wednesday, September 5, 2018 10:46 AMRonKyle - Wednesday, September 5, 2018 7:45 AMJeff Moden - Monday, September 3, 2018 7:29 PMDon't do it. Don't split date from time and put them in separate columns. It will cause you more problems than you can shake a stick at. If you absolutely must, create computed columns in the table to do the separation but keep that original column together.There have been several posts after this, but there can be good reasons to split this. If it's going into a DW environment you have to. Date and time information need to be in separate dimensions. Otherwise the whole thing becomes unwieldy. You may have run into situations where this caused issues. But the addition of the date and time data types has been a huge help for me. Having this as an absolute rule is not a good idea. In this case (unlike all too many normalization issues) it really does depend.
If you do need to have it split, don't do it at the expense of not having the complete date and time. Import the whole date/time and let a persisted computed column do the split so that when you need it, you actually end up with something that's both SARGable and is capable of temporal accuracy then it comes to "wrap arounds" with dates. For example, things like shifts (an other similar things) that straddle a day play hell on the separated columns.
I don't run into any of these issues when splitting it. Both the date and time have value in themselves and the two columns are easily combined. I understand why the source database combines them. Normally that makes sense in an operational database. But without further questioning, I don't think it's a good idea to say it's automatically a bad idea.
Both the date and time have value in themselves and the two columns are easily combined
Ok. What does the code look like when you do combine the columns? Also, do you ever have to combine the columns in a WHERE clause?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply