Bruce W Cassidy (2/26/2009)
[font="Verdana"]Any chance of you sharing some of the techniques you're using for that? I've been doing similar things, building an ODS for a client company. But I use bcp 😀I have issues with SSIS. Namely, to use it well, you have to be fluent in not one, but three programming languages at once (SQL for your database connections, VB for any plug-in code and C for the inline code). It's incredibly complex, and it's insanely hard to try and modify. I have met several companies who have SSIS packages that everyone flat out refuses to change simply because of the complexity of even just understanding what it does.
Give me well-written straight line code in T-SQL any day. It may not perform quite as well as well-written SSIS, but at least I can easily understand and change it.
[/font]
Sure... most of it is just importing whole rows of the file into a 2 column table... one for an IDENTITY and one for the data. Of course, that requires the use of a BCP format file so I can skip over the identity.
Then, I simply do a Tally table split to an NVP (Name/Value Pair) table in order by the IDENTITY column (which gets saved in the NVP) and the order of the Tally table.
The reason I do this is two fold... that allows me to split the data into "columns" in the NVP without knowing how many there are and it also allows me to skip rows with BCP (Bulk Insert, actually). Neither like it very much when there are different numbers of delimiters in the header rows than the body of the file and they won't skip rows based just on the EOL character... the number of delimiters must match in all rows. So, I just treat each row as if it had no delimiters to allow the skip of 20 or 30 header rows.
Note that I do NOT use a splitter function for this... I split the whole table all at once. If you haven't seen that before, take a look at the code in the following article (near the end)...
http://www.sqlservercentral.com/articles/TSQL/62867/
The section that I'm talking about in the article is titled, [font="Arial Black"]"One Final "Split" Trick with the Tally Table".[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.