icelava.net

why be normal?
Welcome to icelava.net Sign in | Help
in Search

Deploying database projects with data from CSV files

Last post 04-10-2008, 21:57 by icelava. 1 replies.
Sort Posts: Previous Next
  •  03-26-2008, 10:46 2620

    Deploying database projects with data from CSV files

    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.

    1. In the Variables property tab, define a Variable Name like OutputPath with a Variable Value of $(TargetDir) (how come that works here?).
    2. 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

    3. 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.

  •  04-10-2008, 21:57 2757 in reply to 2620

    Re: Deploying database projects with data from CSV files

    icelava:
    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.

    If you have been using Database projects long and extensively enough, you should have realised this method is not going to work well for deploying to remote SQL Servers. The CSV files need to be copied to a local drive directory of the remote SQL Server, with the $(OutputPath) variable pathing that server's filesystem; allowing SQL Server to locate the files for import.

    To make this happen, the following steps has to be taken:

    1. In the Database project Variables properties, adjust the $(OutputPath) variable for the SIT or UAT or whatever remote-server build configuration (not Default, which you would probably use for local development) to that remote server's deployment drop directory. E.g. Instead of "$(TargetDir)" put "D:\project\DB\deployment\"
    2. Open up a network folder share on the remote server for would allow dropping the files to that exact location in the server's hard drive.
    3. Enhance the Build Events macro
      IF "$(Configuration)" == "Default" (
      copy /y"$(SolutionDir)Database\Scripts\Post-Deployment\Data_Code\*.csv" "$(SolutionDir)Database\sql"
      copy /y "$(SolutionDir)Database\Scripts\Post-Deployment\Data_Reports\*.csv" "$(SolutionDir)Database\sql" )

      IF "$(Configuration)" == "SIT" (
      copy /y "$(SolutionDir)Database\Scripts\Post-Deployment\Data_Code\*.csv" "\\SITDB\DB\deployment"
      copy /y "$(SolutionDir)Database\Scripts\Post-Deployment\Data_Reports\*.csv" "\\SITDB\DB\deployment" )

      IF "$(Configuration)" == "UAT" (
      copy /y "$(SolutionDir)Database\Scripts\Post-Deployment\Data_Code\*.csv" "\\UATDB\DB\deployment"
      copy /y "$(SolutionDir)Database\Scripts\Post-Deployment\Data_Reports\*.csv" "\\UATDB\DB\deployment" )

    I tried to use Robocopy instead to make faster comparison and copies of only updated CSV files, but apparently Robocopy does not work well within the Visual Studio execution shell and exits with error code 2. Visual Studio does not like that regards it as a Failure.

    At least with that, deploying straight from Visual Studio to the remote SQL Server should be possible. Of course, you still need network connectivity and security clearance to achieve it.

View as RSS news feed in XML
Powered by Community Server, by Telligent Systems