Bulk Insert Task, set default date format

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Bulk Insert Task, set default date format

Bulk Insert Task, set default date format

Rate Topic

Display Mode

Topic Options

Author

Message

aaron.reese

Posted Monday, November 12, 2012 5:40 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

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

[code="sql"]
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

Post #1383642

GSquared

Posted Monday, November 12, 2012 6:52 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1383674

aaron.reese

Posted Monday, November 12, 2012 7:31 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

Thanks G, as always a font of knowlege.

I feared that would be your answer Sad

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.

Post #1383698

GSquared

Posted Monday, November 12, 2012 7:58 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1383712

opc.three

Posted Thursday, November 22, 2012 1:58 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 6,703, Visits: 11,731

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy