February 29, 2012 at 7:34 am
laurence.kirk (2/29/2012)
Unfortunately, real life and deadlines get in the way of hacking around in SSIS ...
Amen to that 🙂 As it is I'm going to miss a BIG one tomorrow, but hey, we're only human, right? Right?
April 4, 2012 at 5:00 am
Hi board,
For the benifit of any future Googlers, the solution was very simple for me. I had an Excel file with date as string - in en-GB format (dd/mm/yyyy).
I needed to import the data from the file into a DB table. The date format in each data row was not consistent, some where assuming UK format date some where assuming US.
The solution - click on the Excel Source component in Data Flow view and take a look at properties panel (bottom right). I had so specify LocaleID as English (UK) so that all dates were interpreted correctly.
You should do the same for the destination compontent.
Let me know if this helps anyone.
April 5, 2012 at 7:32 am
What is your code page in SSIS flat file connector?
What is your database collation?
I just imported from a csv file the below data into test1 table with fields id int and sdate datetime
id,stdate
1,03/01/2012
2,2012-03-01
3,01-NOV-2010
The results came out correctly when I query the database.
12012-03-01 00:00:00.000
22012-03-01 00:00:00.000
32010-11-01 00:00:00.000
*****
Give a man a fish, and you've given him a meal. Teach a man to fish and he'll ask to borrow your boat.:-P
April 10, 2012 at 6:29 am
leewrigh7 (4/4/2012)
Hi board,Let me know if this helps anyone.
How easy it is to miss glaringly obvious things such as this. Thanks for that Lee, had this been better documented or I less hasty to go for the complicated solution, it would have saved me some time. To elaborate, it appears that the only container that needs to be set is the one doing the convert which seems sensible although you have raised another less sensible point which is bugging me.
My only concern is that SSIS will implicitly cast mixed date format values at all. This leaves me nervous as I would usually consider a mixed format date in a file to be an error in the file and rejected. This vagary seems worthy of the Jet driver and should be discouraged IMO as I get enough fun of second guessing data type interpretation from that (MAXSCANROWS=0 anybody?). I would say that if the situation could not be avoided, this is a good way to deal with mixed dates, otherwise, the other methods mentioned here would fail the import which I would say is functionality that is useful to most.
This is actually making me think that I should go over my production packages and change to a stricter data conversion method.
L
May 10, 2012 at 4:48 pm
laurence.kirk (10/13/2011)
Sorry to resurrect but thought the solution to this may interest any future googlers. Andy pretty much had it with SSIS being based on .net, so the way to set date format in .net is to change the regional culture of the thread. If you put this line of code into a script task in the control flow, it should solve your problem.
System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US", True)This works for 2005 and 2008 and you can find a list of culture names here:http://www.csharp-examples.net/culture-names/
Hope this helps
Laurence
Thankyou, this was very helpful in my situation.
For some reason, some servers were importing the datetime string as DDMMYYYY and others were MMDDYYYY, but when I check dbcc useroptions they all tell me that they are set to MDY
But putting that line in made the import consistent on all servers.
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply