Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert Task, set default date format Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 5:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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
Posted Monday, November 12, 2012 6:52 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Monday, November 12, 2012 7:31 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
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.

Post #1383698
Posted Monday, November 12, 2012 7:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Posted Thursday, November 22, 2012 1:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:19 PM
Points: 7,127, Visits: 12,655
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


  Post Attachments 
SSIS.RetainConnection.jpg (71 views, 55.49 KB)
Post #1387981
Posted Thursday, November 22, 2012 3:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:34 AM
Points: 386, Visits: 624
@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.
Post #1387993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse