May 28, 2012 at 2:18 am
Hi all
I have a list of file i want to Bulk insert using the Bulk Insert command.
I also have an XML format file for the data.
I want to store the format files in SQL and then just pass them as an XML variable to the bulk insert statement.
Does anyone know how i might achieve this.
The basic premise is that i wan the database to be a single piece to migrate between servers and i don't want to have to remeber to move the format files with it.
So far the only way i have thought to do it is to use cmdshell to write the format files out first and then read them back in. but this is cumbersome and i will have to worry about SQL having write permissions etc...
And help would be appreciated thanks.
May 28, 2012 at 6:14 am
Use SSIS
May 28, 2012 at 6:26 am
Hi There
Not very helpful as SSIS still asks for a file as the format file.
So you still have the format file on the drive.
Also SSIS packages are outside of the database so this does not fit the model i am trying to get to.
thanks for your answer but If you were not planning on reading my question. I would prefer if you didn't answer it.
May 29, 2012 at 11:53 am
ashley.wardell (5/28/2012)
Hi allI have a list of file i want to Bulk insert using the Bulk Insert command.
I also have an XML format file for the data.
I want to store the format files in SQL and then just pass them as an XML variable to the bulk insert statement.
Does anyone know how i might achieve this.
According to the documentation BULK INSERT can only accept a path to the format file, i.e. not the content directly as you were hoping. Using xp_cmdshell to write the content to a file and then pass the path into BULK INSERT may be your best bet if you want to keep everything in T-SQL.
The basic premise is that i wan the database to be a single piece to migrate between servers and i don't want to have to remeber to move the format files with it.
So far the only way i have thought to do it is to use cmdshell to write the format files out first and then read them back in. but this is cumbersome and i will have to worry about SQL having write permissions etc...
With every technique there are pluses and minuses. As an aside I would use SSIS for this task as well. Doing this task completely within the data tier may buy you some items but IMHO it is an attempt to turn your SQL Server into an application server and is an extension of a hammer-nail pattern if you will.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 30, 2012 at 3:42 am
Thanks for the advise.
I am going to stick with the cmdshell.
I apprecieate the whole anti-pattern situation but unfortunately if i am going to use SSIS i will be writing outside of SQL and if i am going to do this i might as well write an APP to do this seperately. Or maybe write a new bulk insert CLR.
HUM... actually that could be an idea. just thought of that whilst i was writing this.
May 30, 2012 at 4:19 am
On the project I'm working on I faced the same problem (xp_cmdshell disabled). What I came up with was an SSIS package that first calls an SP that returns the contents of the format file (the plain text version, not the XML one), and then after that a script task takes that result set and writes it to disk, calls bcp.exe and finally cleans up behind itself.
May 30, 2012 at 4:25 am
ashley.wardell (5/30/2012)
Or maybe write a new bulk insert CLR.
I've seen this done before, and it worked very well using SqlBulkCopy. You might be tangentially interested in http://sqlblog.com/blogs/alberto_ferrari/archive/2009/11/30/sqlbulkcopy-performance-analysis.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 30, 2012 at 4:33 am
Cheers for that answer is helpful. I have used SQLBulkCopy to load from an MSMQ and the performance was really good.
May 30, 2012 at 6:58 am
SQL Kiwi (5/30/2012)
ashley.wardell (5/30/2012)
Or maybe write a new bulk insert CLR.I've seen this done before, and it worked very well using SqlBulkCopy. You might be tangentially interested in http://sqlblog.com/blogs/alberto_ferrari/archive/2009/11/30/sqlbulkcopy-performance-analysis.aspx
Nice paper, I can tell a lot of work went into producing it, but I have a few issues with it. For one they are relying on the WriteToServer(IDataReader) method which is far from the most common use of the SqlBulkCopy class in this space. Typically the entire file is loaded into a DataTable and that is written to the server using WriteToServer(DataTable). This implies the entire file must be loaded into memory which obviously becomes a limiting factor.
The code from the article uses a random data generator to "produce" an IDataReader. I have never seen this done from a flat-file, except in theory. I think you would need to use an ODBC text driver to do it, or write your own parsing and buffer methods using something like TextStream.ReadLine so you can control how much of the file you load into memory at one time...in which case why bother, we already have bcp, BULK INSERT and SSIS. Speaking of SSIS, they kind of knocked it in the paper but ade no mention of tuning the SSIS buffers in their tests. It would depend on a lot of factors (as usual) but off the shelf I cannot see using SqlBulkCopy over SSIS for anything but a corner-case in this space. I think the world of the SQLCLR and the options it brings, but not for EXTERNAL_ACCESS tasks like this.
To the OP, how large are the files we're talking about? To achieve your non-technical goals (being able to keep all your code in the DB sounds like a big win for you) I think you are on the right track with xp_cmdshell+BULK INSERT as you seem to be leaning, especially if any of your files are of non-trivial size.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 30, 2012 at 3:48 pm
opc.three (5/30/2012)
Nice paper, I can tell a lot of work went into producing it, but I have a few issues with it.
You should comment on Alberto's blog and discuss it with him; I just provided the link in case it happened to be interesting / useful.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 31, 2012 at 5:13 pm
SQL Kiwi (5/30/2012)
opc.three (5/30/2012)
Nice paper, I can tell a lot of work went into producing it, but I have a few issues with it.You should comment on Alberto's blog and discuss it with him; I just provided the link in case it happened to be interesting / useful.
It was an interesting read. I added a comment.
EDIT: remove content related to another thread
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply