Specifying a relative path in Post-Deployment SQL files

Question

We have a post-deployment script in our SQL Server project which essentially performs a bulk-insert to populate tables after they're created. This is done by reading several .csv files:

BULK INSERT
    [dbo].[Table1]
    FROM '.\SubFolder\TestData\Data1.csv'
    WITH
    (
        ROWTERMINATOR = '0x0a',
        FIELDTERMINATOR = ','
    )

BULK INSERT
    [dbo].[Table2]
    FROM '.\SubFolder\TestData\Data2.csv'
    WITH
    (
        ROWTERMINATOR = '0x0a',
        FIELDTERMINATOR = ','
    )

The problem is Visual Studio is having a hard time finding the files:

Cannot bulk load because the file ".\SubFolder\TestData\Data1.csv" could not be opened. 
Operating system error code 3(The system cannot find the path specified.).

The .csv files are checked in to the source control and I do see them when I go to the folder they're mapped to on my machine. I assume the problem is . isn't returning current path for the sql file being executed. Is there a way to get the relative path? Is there a macro (or a SQLCMD Variable maybe) that would give me current path of the file?

score 1 · Answer 1 · answered Jan 16 '15 at 14:51

The problem you have is that the .csv files are in your VS project but the script will be executed on the SQL Server, so the files should be in a location that the server will have access to. Maybe, you could add a Pre-Build event that will copy the .csv files to a shared drive on the server and then use a static path in your script that will take the files from the shared location.

magikfingerz · Answer 2 · 2020-02-20T15:07:45.837

I know this question is very old but still relevant. I found a working solution to this problem under the following conditions (what are optional because there are ways to overcome it) :

You will use the "publish" option within the Visual studio IDE to deploy your app.
The csv file is part of your project and configured to be copied to output folder.

Here are the steps:

Open Project Properties and go to SQLCMD Variables Section.
Add a new variable (for example $(CurrentPath))
In default Value put: $(ProjectDir)$(OutputPath)
Change your BULK code to:

BULK INSERT
    [dbo].[Table1]
    FROM '$(CurrentPath)\PathToFolderInsideOutputDirectory\Data1.csv'
    WITH
    (
        ROWTERMINATOR = '0x0a',
        FIELDTERMINATOR = ','
    )

Save all and compile.
Test your deploy using publish, ensure the $(CurrentPath) variable shows the right path(or press "Load values" button), press the publish button, all should work.

It's been a while but I currently have the same issue. When set default to $(ProjectDir)$(OutputPath) the output path is wrong because it begins with a .(dot) which cause the path to be wrong. — nablue, Jul 18 '22 at 15:39

score -1 · Answer 3 · answered May 28 '14 at 23:34

-1

You can create a SSIS package and use foreach Loop container to loop through all the .csv files in a given path. See below the a demo configuration of Foreach Loop Container

enter image description here

answered May 28 '14 at 23:34

M.Ali

67,945
13
101
127

The problem is that I don't know what the path is. The csv files are in source control and different users will have different paths based on the folder they've mapped the solution to. – Arian Motamedi May 28 '14 at 23:46
Yea thats fine you can use a variable for directory path, which will be populated at runtime. Any user who executes the package will provide the path at run time before executing the package. – M.Ali May 28 '14 at 23:53
I'm trying to see if there's a way VS can locate the file itself without having to ask the user to specify the path... – Arian Motamedi May 28 '14 at 23:56
How can you expect sql server to know the path :) – M.Ali May 28 '14 at 23:57
That's exactly why I asked if there's a macro or something :) – Arian Motamedi May 29 '14 at 00:06

Specifying a relative path in Post-Deployment SQL files

3 Answers3