Create Text File From Stored Procedure

Question

I have an ETL process (in SSIS) that extracts data from the database and writes the data to a predefined text file. Currently, I create the directories and files manually to satisfy this process.

To add a new variable to this process, I run a usp that adds a new row to a table, which is read in to the ETL process and determines which packages to run.

To remove the manual creation of the directories and files mentioned above, and to remove the possibility of human error, I want to create the new directory and file when a new row is added via the usp.

In short; I check to see if the subdirectory exists using xp_dirtree, and then use xp_create_subdir to create the new subdirectory. What I want to do now is create an empty text file, in the new subdirectory, that will have data written to it during the ETL process.

The options I think I have are the following:

1. Use xp_cmdshell; I recognise that there is a stigma attached to this, but I am not adverse to using it if it is the most optimal way. This is disabled by default, but I can reconfigure on and off within the usp.
2. Use fso; I'ved used fso within .NET scripts, but have no experience of using it within SQL Server. I can't seem to find any resources that dictate how to create a text file, only read and write.
3. Use SSIS; within the ETL project I have a flat file connection manager, where the connection string is dynamically set, based on the name of the variable. I'm unsure if the text file will automatically be created if it does not exists? I have manually created them to avoid any chance of failure. If the text file is automatically created, this could be an option.

I've purposely left BCP out of the options above, as I don't have a result set to copy, and therefore presume this is not a viable option? Unless I can "copy" an empty dataset to create the text file?

Can anybody provide any useful resource for how to accomplish this? Or can anybody provide any pointers on how best to achieve this? Is xp_cmdshell the best way? I want to create an empty text file when I create the new subdirectory.

Thanks in advance for any help offered.

Answer 1

You could use SQL CLR. This allows (more-or-less) any C# code to be executed from within SQL Server as if it were a stored procedure. This includes OS and file system manipulation. The code can accept values from and return values to T-SQL. Permissions can be controlled just like any other stored procedure, sidestepping many of the concerns with xp_cmdshell.