Post data deploy in SSDT

Question

I need to create post deployment script which will upload data from .csv file to my database. But I can't understand how can I do this.

When I tried to deploy my project on my server I receive this error messaege:

(2354,1): SQL72014: .Net SqlClient Data Provider: Msg 4861, Level 16, State 1, Line 9 Cannot bulk load because the file ".\Data\DataFile.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.). (2346,0): SQL72045: Script execution error. The executed script:

Could someone help me please?

P.S. Below you could find my examples of files.

Structure of my SSDT project:

Source
 Data
 DataFile.csv
 Script
 PostDeployment
 DataFile.sql
 PostDeployment.sql

DataFile.sql:

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t
CREATE TABLE #t 
 ( 
 Column NVARCHAR(100),
 Column2 NVARCHAR(100)
 )
; 
BULK INSERT #t
FROM '$(DataFile)' 
WITH (CODEPAGE = '1251', FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', DATAFILETYPE = 'widechar')
SELECT * INTO table1 FROM #t AS tcbm

PostDeployment.sql

:setvar DataFile .\Data\DataFile.csv
:r .\Script\PostDeployment\DataFile.sql

If I change :setvar DataFile to ..\..\Data\CarBrandModel - deployment also will be failed with the same error.

Answer 1

CSV files are not included in the dacpac that you deploy so you will need to make sure that you copy it with the dacpac and that if you use relative paths the working directory is what you think it is.

What I do for extra files is set the "Build Action" of the file to "Copy if Newer", when the dacpac is built in the bin folder you will also have a "Data" subfolder - just make sure you copy that with your dacpac always.

ed