SQL Server 2008 - Execute Batch Job and Bulk Insert in T-SQL

Question

Hi

I am faced with an interesting problem and I am not even sure if it is possible:

I need to create a Stored Procedure in SQL Server 2008 that when executed does the following:

1. Executes a Batch file - located on the SQL Server (i.e. C:\Mybatchfile.bat) - This Batch file will output a single text file to a directory on the SQL Server (i.e. C:\Output\BatchFileOutput1.txt).

2. Bulk Insert the contents of the text file into table within my database.

3. Deletes the text file from the server.

Before I really start digging into how to achieve this, is this even possible?

Thanks in advance

Answer 1

You should be able to put all of this in one procedure:

1) to execute the batch file use: xp_cmdshell (Transact-SQL). However, many consider this a security risk, and access to it may not be enabled within the database. You could use a CLR to execute the batch file, which would be more secure.

2) you can use the bcp Utility to copy the rows into a table.

3) you would use the method from #1 to delete the file. Either issue the delete command from the xp_cmdshell, which is a security risk. Or create a CLR where you pass in the file name to delete the file. I would not allow the path to be passed in, hard code that, so only files in your "special" directory can be deleted and not C:\, etc.

Answer 2

Alternatively you could do this in an SSIS pacakge and then have a job to execute the package rather than doing in a stored proc. Or the proc could just have the code to execute the SSIS pacakge.