I thought this would be a very common style of deployment. Why write thousands of lines of INSERT when you can prepare CSV data in quicker fashion and use the BULK INSERT command? Surely developers would want that. Well, the Visual Studio team apparently did not think so. They must have been rushing to push the fourth Team Edition for Visual Studio 2005 - Database Professionals - out of the door that working out an easy way to achieve this is a low priority.
It took a long read on a lengthy and attention-breaking discussion to realise developers are not having an easy time loading data into their freshly deployed databases. There is no outright convenient way of putting CSV files in one's database project and have a script BULK INSERT it. Extra files in the database project are given the expectation that it somehow has to be a T-SQL or SQLCMD script; with only build options of Build, Not in Build, Predeploy, and Postdeploy. Where is the Copy of Output Directory option?
The suggestion was to either use the database project's Build Events property to manually copy data files to the output directory, and hopefully get a post-deploy script to read it for BULK INSERT. But guess what? Several important macro variables for the project are not supported in database projects! In the end I had to make do with the $(SolutionDir) variable which thankfully worked.
copy "$(SolutionDir)MyDatabaseProject\Scripts\Post-Deployment\Data\CSV\*.csv" "$(SolutionDir)MyDatabaseProject\sql"
Note: .\Scripts is the defined output directory, so I could reference it relatively from the solution path. If the output directory was defined to be somewhere else outside the solution, good luck.
So by using Build Events we get to copy the CSV files over to the output directory. How now, can we get a post-deployment script to know the full path to use in the BULK INSERT statement?
It turns out, Service Release 1 saves the day by allowing us to copy project macro variables into script variables for use in pre/post-deployment.
- In the Variables property tab, define a Variable Name like OutputPath with a Variable Value of $(TargetDir) (how come that works here?).
- Probably better to include another script file for post-processing in Script.PostDeployment.sql
/* IMPORTING SAMPLE DATA FROM CSV FILES */:r .\Data\dbo.ImportTable.sql
- And in that script file, instead of regular INSERTs, get the BULK INSERT to reference via the variable
BULK INSERT dbo.ImportTable FROM '$(OutputPath)ImportTable.csv' WITH
(FIELDTERMINATOR = ',' ,
FIRSTROW = 2) -- Using Header row
Convoluted? Yes. Is there a more elegant way? Perhaps. Does it have to be like that in the first place? No.