If the input dates are always 8 digits long (including leading zero) and on that format then I would use I was going to jump on this 5 year old post, but thought I would create a new one. But I could be bringing in 60 to 70 files per import, I'm not sure, but I'm assuming I'll need to convert at least one date field on each. Right now we are talking a few hundred rows per import, I wouldn't expect this to be more than a couple thousand per import, but you never now. Of course my initial thought is to ask that they send the dates in a different format, but I don't know how open they will be to that. Then I was going to use SQL to 'Transform' the data from the 'Work' table before inserting into the final tables. I will import this data from the text files into a 'work' table, each column defined as varchar(500), very little chance of failure on load. I don't want to do this conversion in the SSIS package, unless someone can prove it would be the best solution. SELECT 4) + 4) AS DATE) - stole from another site SELECT 3, 0, '/'), 6, 0, '/') AS DATE) - Sean Lange solution from other post Here is the 2 ways I found to convert this data to a date: This does not convert in SSIS directly to a date, it will fail converting or casting directly as a date.
The dates on this file will be coming in with the format of MMDDYYYY. The issue is I will be receiving files from an outside source, the file will be a pipe delimited text file. My head is a little foggy today, could be the holidays coming up or this cold I got from the grand kids.
I'm also making an assumption that either of these ways would be better than using substring to parse each piece. First I will say I've found a couple different SQL solutions for this, I'm asking if there is a better way or what would be the most efficient way of doing this.
0 kommentar(er)
