Is there a way to make a variable in an SSIS project point to [relative-project path]\fixed_foldername\fixed-filename.csv ?
I have an SSIS project in Visual Studio which processes multiple CSV files. It is the typical "process all CSV files in a given directory" sort of job.
Even though the SSIS package will look at every file in a given directory, we do need to specify one file as a "template" when mapping the columns in the source CSV file. This is the file that I would like to include with the solution.
Reason being is that we use visualstudio.com for source control, and I would like to be able to reference the template file (in case we need to do change, etc) regardless of which machine I am working on.
So what I have done is to create a directory called "Template Source Data Files" inside my project directory (alongside the bin and obj directories) to include my source CSV files. Then I added the CSV files to the project, making them appear under the 'Miscellaneous' folder.
However this only solves half of my problems. Even tough the CSV file will now be available in any host via visualstudio.com source control, there is still the issue of the file path being hard-coded in the variable name.
A variable named 'CSVFileName' (which holds the name of the current CSV file being processed) points to the template CSV file name by default. My concern is that the path to the file will of course change depending on the host that I am working on.
I only need this for helping with Visual Studio development so the correct file name/path is referenced during development. This wouldn't really apply as parameters for when executing the package in production.
After reading some of the comments above and talking to some people at Microsoft, indeed it is NOT possible to do what I want.
In the end what I did is to follow Matt's suggestion and have the files available at a network share available everywhere. To do so, I made the files available via an Azure storage file-share.