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.
Best Answer
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