June 16, 2008 at 8:55 pm
Hi
I'm new to SSIS and have been climbing the steep side of the learning curve...
I have built a file loader which imports a few dozen .csv files each day into a MSSQL database table, and generally it works ok.
My only problem remaining is how SSIS interprets dates in the .csv file. My data is mm-dd-yyyy, and SSIS is interpreting it as dd-mm-yyyy
How do I enforce the interpretation to be mm-dd-yyyy? ( I assume this is set at the point you read the data.. is that correct?)
Any help would be greatly appreciated.
June 22, 2008 at 9:39 pm
Well i'm still working at this...
I have a date in the format of mm-dd-yyyy and I need to put it in to a date or datetime field. After reading other peoples posts I have tried again.. :doze: to use a data conversion node.
In the expression field I have added this: CONVERT([ACCT_START],105).
And I now get this:
[font="Courier New"]Error at load cust extract data [Derived Column [4676]]: The function "CONVERT" was not recognized. Either the function name is incorrect or does not exist.
Error at load cust extract data [Derived Column [4676]]: Attempt to parse the expression "CONVERT([ACCT_START],105)" failed and returned error code 0xC004708A. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.... [/font]
I would have thought that a basic convert function that works in SQL would also work in SSIS. Apparently not.
June 22, 2008 at 9:59 pm
Well I must say I am now completely underwhelmed with SSIS.
I couldn't get past the varchar to unicode conversion thing so I have dropped my table and recreated in with varchars instead pof nvarchars.
Now instead of handling dates properly I have gone back to the person who is producing the .csv files and asked them to change the date format in the PL/SQL script producing the data. (which took them about 2 minutes.)
Before a bunch of [edit: learned individuals] tell me to learn how to use SSIS properly. I'd like to add that I could have perfomed this function quicker and easier in PL/SQL, Perl, Java or in other integration tool such as Webmethods or Tibco. Even MS-Access would have been easier.
I was really excited about learning SSIS. Not any more. I feel it's been a frustrating exercise and a huge mistake. I will will be backing out of SSIS and implementing this in a another more user freindly language/product.
June 22, 2008 at 11:51 pm
thepotplants (6/22/2008)
Before a bunch of sanctimonious pricks wade in and tell me to learn how to use SSIS properly. I'd like to add that I could have perfomed this function quicker and easier in PL/SQL, Perl, Java or in other integration tool such as Webmethods or Tibco. Even MS-Access would have been easier.
You do know that with bulk loading or whatever you could have done the same, without SSIS.
First off, I aint gonna tell you how to use the SSIS tool properly, but rather tell you what I tell every other Joe Soap in my environment, fix the problem at source. There are ways to do this in SSIS, but that is irrelevant. Why should my environment do extra processing when I can get to the root cause of the problem and fix it there?
Good luck on your PL/SQL, Perl, Java, Webmethods, Tibco, MS-Access endeavours!
~PD
June 24, 2008 at 9:59 am
I agree, fix the problem at source. If the source data was in ISO date format, SSIS would have no trouble importing it, because the date format is not ambiguous.
June 24, 2008 at 10:25 am
Actually, took exception to the way in which the author was asking for help more than anything else, but yeah, once more, fix the problem at source
~PD
June 25, 2008 at 3:27 am
Yep, I agree with your sentiments too! Just because SSIS was not working exactly how the original poster thought it should, is no reason to rubbish SSIS in general. Still, I'm not the one worrying about a failing import 😉
June 25, 2008 at 10:52 pm
Thank you for your responses. I appreciate that you took the time to reply.
Now that i've had a rant i've calmed down a bit.
Yes, in a perfect world I agree 100% that this data issue should be fixed at source. In fact I would prefer if i wasn't using .csv files at all. Unfortunately I don't live (or work) in a perfect world.
I am stuck between the client organisation and an oursourcing/vendor. I am not permitted to extract data from the production system. Instead I am provided with csv files. I don't have any choice in the matter. I have no control over how these files are produced or who by
I consider myself extremely lucky I could get them to change the date format without a paid change request.
As I wanted to reimport this data to a database for sharing and use by a business department, I needed a tool to import multiple .csv files to a table. After reading microsofts own description of SSIS I thought this would be the ideal tool for the job.
I readily admit that I had something up and running quite quickly and SSIS made some aspects relatively simple to implement. However I am still astounded at how 2 such trivial problems ground such a simple project to a halt.
I apologise for my rant. But I stand by my comments.
June 25, 2008 at 11:45 pm
thepotplants (6/22/2008)
Before a bunch of sanctimonious pricks wade in and tell me to learn how to use SSIS properly.
That was pretty poor form. Please accept my apologies.
June 25, 2008 at 11:48 pm
Hey don't you guys think you are being a "LITTLE BRUTAL?" - I'm sure you were not an EXPERT when you started out either! 🙂
I know I have spent HOURS trying to figure out something - and when ALL of my HAIR is COMPLETELY PULLED OUT - my LAST HOPE - is begging for HELP or looking for the ANSWER on our forums. :w00t:
I personally think you guys who know this stuff inside and out are "GODS".
But - what you think of as a STUPID question - is TOTALLY something we NEWBIES do not know about or understand.
AND unfortunately - some of us live in "the boonies" and the Classes needed for Certifications etc are not available.
It is so hard to expose yourself and ask for help - and even harder if you know you are going to get made fun of. Love you guys - Just a plea for mercy! :kiss:
June 26, 2008 at 12:17 am
Good luck on your PL/SQL, Perl, Java, Webmethods, Tibco, MS-Access endeavours! ~PD
Ummm... Ok, Can I start again? :blush:
Hi guys i'm new to SSIS. I'm stuggling to convert a date string read from a csv file to SQL server acceptable date. I would know what I was doing if I was using Perl, Java, PL/SQL, TSQL or MS-Access See below.
Perl: [font="Courier New"] my %date;
$_ = '25/02/1990 23:48:00';
@date{ qw/ day month year hour minute second/} = m<^(\d\d?)/(\d \d?)/(\d{4}) +(\d\d): (\d\d): \d\d)>;
$date{ minute } += 6;
printf "%2d/%2d/%4d %2d:%2d:%2d", @date{ qw/ day month year hour minute second/};[/font]
PL/SQL: [font="Courier New"]to_date(rate_dt,'YYYYMMDD HH24:MI:SS')[/font]
Java: [font="Courier New"]Date date = new SimpleDateFormat("MM/dd/yy").parse("05/18/05");[/font]
TSQL: [font="Courier New"]CONVERT(char(8),left(b.trans_dt,8),112)[/font]
MS-Access: [font="Courier New"] Format((Date()-190),"yyyymmdd")[/font]
I have tried using a TSQL Convert in the data type expression but it clearly doesn't like that. :unsure:
I've been googling till my eyes water :crying: and reading forums, msdn, technet and all sorts of blogs, but clearly i'm missing the point. It really shouldn't be that hard should it?. :blink:
Could someone please point me in the right direction? How do I convert a US date string into a proper date format with SSIS.?
Regards. (with my sincerest apologies for throwing my toys out of the pram.) 😉
thepotplants...
June 26, 2008 at 3:11 am
hehe, ok, apology graciously accepted (on behalf of all Forum users).
Can I point out that there are no Gods on this Forum? There are certainly some very experienced users, but they don't need their egos tickling with thoughts of divinity.
Now that you have explained the situation, including the fact that changing the data at source is not easy, let's suggest a fix. I have had numerous problems with importing dates in the past. The one reliable method I normally stick with, when I know the incoming date is of a particular format, is to programmatically split the date into portions and then rebuild it into ISO date format.
I am not saying this is the best way to do it, nor the quickest, but I have used it for many different data imports and it works reliably (which is normally the most important factor).
I'll fish out some code and post it when I have a minute.
June 26, 2008 at 3:27 am
laura.marshall (6/25/2008)
Hey don't you guys think you are being a "LITTLE BRUTAL?" - I'm sure you were not an EXPERT when you started out either! 🙂I know I have spent HOURS trying to figure out something - and when ALL of my HAIR is COMPLETELY PULLED OUT - my LAST HOPE - is begging for HELP or looking for the ANSWER on our forums. :w00t:
I personally think you guys who know this stuff inside and out are "GODS".
But - what you think of as a STUPID question - is TOTALLY something we NEWBIES do not know about or understand.
AND unfortunately - some of us live in "the boonies" and the Classes needed for Certifications etc are not available.
It is so hard to expose yourself and ask for help - and even harder if you know you are going to get made fun of. Love you guys - Just a plea for mercy! :kiss:
Hey Laura,
Nahhhh, none of us are gods, and I fully agree that sometimes we all spend hours on finding an answer.
However, understand that if you come asking for help with a really snide comment, expect a really snide remark right back.
~PD
PS> Pots, apologies accepted dudie...
Will post a fix the moment I get a chance...
June 26, 2008 at 3:27 am
I couldn't find exactly what I was looking for (VB code for an SSIS project), but you may find that pure T-SQL will accomplish what you need. For example:
declare @myDate varchar(10)
declare @myISOdate smalldatetime
set @myDate = '11-23-2007'
set @myISOdate = (select substring(@myDate, 7, 4) + '-' + substring(@myDate, 1, 2) + '-' + substring(@myDate, 4, 2))
select @myISOdate
Can you use this code snippet in your data import routine?
Andy
June 26, 2008 at 3:41 am
SUBSTRING also works in SSIS (*use as a derived column)
However, if you are saying that SSIS is reading your data in the incorrect format (mm/dd/yyyy as opposed to dd/mm/yyyy), perhaps start by checking your regional settings from where the SSIS is running.
If you need to absolutely gaurantee that it runs, no matter what the settings are, it would be best to break it up into day, month and year with some kind of substring functionality, and then put it back into ISO format.
Good luck and shout if you need some more assistance
~PD
ps> My apologies, I never welcomed you properly into the SSIS clan...
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply