InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:52:59 GMT20http://www.sqlservercentral.com/Forums/Topic1383642-364-1.aspx@opcThat 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.Thu, 22 Nov 2012 15:51:52 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1383642-364-1.aspxDepending 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:[img]http://www.sqlservercentral.com/Forums/Attachment12742.aspx[/img]If you set the SQL statement of the Execute SQL Task as[code="sql"]SET DATEFORMAT DMY;[/code]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.Thu, 22 Nov 2012 13:58:01 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1383642-364-1.aspxYeah. 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.Mon, 12 Nov 2012 07:58:29 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1383642-364-1.aspxThanks 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 likeSet some variablesExecute 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.Mon, 12 Nov 2012 07:31:11 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1383642-364-1.aspxPull 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.Mon, 12 Nov 2012 06:52:39 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1383642-364-1.aspxGuys,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 columnsThe problem I am having is that the dates are in UK format: DD/MM/YYYYWhen running a bulk insert script I use[code="sql"]set dateformat DMYGOBULK INSERT dbo.myTablefrom .../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 MDYMon, 12 Nov 2012 05:40:16 GMTaaron.reese