Bulk Insert Task, set default date format
-
Guys,
I am writing a Package that will do a number of bulk inserts. The files are flat file Pipe delimited and are using a format file to map to the table columns
The problem I am having is that the dates are in UK format: DD/MM/YYYY
When running a bulk insert script I use
set dateformat DMY
GO
BULK INSERT dbo.myTable
from ...
/code]
I am getting error messages from the Bulk insert task that I think are to do with date formats. How do I force the package to use DMY instead of the default MDY
-
Pull the data into a staging table that has a varchar column instead of a date/datetime column, for that data. Then you can use an SQL script task to load it from the staging table to your final destination, with the modifications you need to the data.
It's a pain, but it's the best solution I've come up with dealing with that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Thanks G, as always a font of knowlege.
I feared that would be your answer 🙁
Because I have about 40 tables to import and I already have the format files built and the only problem is the date format, I have elected to convert my script to a stored procedure and generate dynamic SQL. It's not pretty and I'm not pround of it, but it does work.
The SSIS is now going to look like
Set some variables
Execute SQL task: SQL from Variable (picks up the task name as the file to be processed)
Execute SQL task: SQL from Variable (picks up the task name as the file to be processed)
...
Execute SQL task: SQL from Variable (picks up the task name as the file to be processed)
Not very elegant.
-
Yeah. SSIS has some serious holes in it, especially when it comes to forcing specific formats on data that doesn't match the collation of the server. It's like Excel that way, automatically dropping leading zeroes off of Zip codes and things like that.
There are time when I wish Microsoft devs wouldn't assume they know more about what I need/want than I do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Depending on whether my assumptions about your setup are correct there is actually a way to handle this in SSIS that works quite well and may save you what sounds like a boatload of work you are thinking you need to do. I setup a test package with a Bulk Insert Task as follows to mirror my most basic impression of what you're doing to load data, with a couple tweaks, namely the addition of an Execute SQL Task before the Bulk Insert Task and a property change on the OLE DB connection object:
If you set the SQL statement of the Execute SQL Task as
SET DATEFORMAT DMY;then because the OLE DB Connection object has its RetainSameConnection property set to True the Bulk Insert Task will also be subject to the SET option executed by the Execute SQL Task. The RetainSameConnection property is not something a lot of people find a need for, but it allows us to unpaint ourselves out of corners that Microsoft seemingly has painted us into from time to time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
@opc
That sounds like exactly what i am looking for. I have written the usp now but will have a play with that in my dev system.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply