March 21, 2012 at 11:51 am
Any particular reason this has to be done via stored procedures? SSIS would be easier.
- 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
March 21, 2012 at 12:49 pm
GSquared (3/21/2012)
Any particular reason this has to be done via stored procedures? SSIS would be easier.
id agree that ssis would be easier. as far as Book3 giving you the error you do not have an if statement for it of course it will error. you may try removing the return in the if statement. im flying a little blind here. also you need to take the framework im giving you and adapt it so the names will make sense to the next guy who has to work on it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 22, 2012 at 7:16 am
Hi GSquared,
The reason I wanted it as a stored proc was because other users from different countries (50+ so far) will may need to add/delete columns in the raw text/cvs files (for their own test environment) meaning there may be changes needed to made to the SQL scripts and I thought with documentation any user without experience with MSSQL would be ok to make simple changes.
The second reason is that I have zero experience with SSIS.
Any advice appreciated.
March 23, 2012 at 7:34 am
That makes sense.
Here's what I'd do in that case:
Use OpenRowset, with the Bulk operator, and have them define format files for each file type they want to use. Adding a column to a format file is really, really easy.
You can define the OpenRowset operator in dynamic SQL, with input parameters for the file name, the format file, and the destination table. If the destination table doesn't exist, use Select Into to create it, and you can script some standard Alter Table commands (dynamic SQL again) to add things like a clustered index, or any other standard features you want on all the tables.
OpenRowset is defined: http://msdn.microsoft.com/en-us/library/ms190312.aspx
Format Files are defined: http://msdn.microsoft.com/en-us/library/ms178129.aspx
Does that help?
- 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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply