Using seed data in Visual Studio
Published 14 August 2019
Add a flat file to the root of your SQL Change Automation project, and set the Copy to Output Directory property to Copy always.
Add a blank new migration script to the project and specify the $(DeployPath) variable as part of the input filename in a BULK INSERT statement, for example:
BULK INSERT [Production].[TransactionHistory] FROM '$(DeployPath)TransactionHistoryData.csv' WITH (FIELDTERMINATOR = ',', FIRSTROW = 2);
Storing seed data files in a sub-folder
Instead of storing the data file in the root of the project, you can also elect to store your files in a subfolder of your SQL Change Automation project. Just be sure to include the relative path to your data file in the BULK INSERT statement, for example, $(DeployPath)Seed-Data\TransactionHistoryData.csv
Co-location of deployment agent and SQL Server instance
In order for the deployment script to be able to access the bulk data file, the client's file system must be accessible from the SQL Server instance. The simplest way to achieve this when deploying within Visual Studio is to connect to a local instance of SQL Server so that the path contained in the DeployPath variable is accessible from your migrations. When deploying in an automated fashion, for example using Octopus Deploy, the same can be achieved by installing a Tentacle on the SQL Server machine itself. Similarly with Team Foundation Server Build, a build agent needs to be installed on the SQL Server machine.
If co-locating the agent and the instance is not feasible, then an alternative is to use a UNC path for the BULK INSERT data_file argument to access the client/agent machine remotely from the SQL Server instance (see BULK INSERT Arguments). Note that the network share on the client/agent must grant the SQL Server instance read access. In order to form the sub-path to the agent's working folder, the content of the DeployPath variable can be manipulated to prefix it with the agent name instead of the local drive letter, for example, change C:\Working\<AutoGeneratedFolder>\ProjectFolder\ to \\MyBuildAgent\C\Working\<AutoGeneratedFolder>\ProjectFolder\