Relative paths

We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?

For example: Our absolute config path is "D:\Program Files\Microsoft SQL Server\90\DTS\Packages\ETLProject\ETLBase\config\Default.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:

'D:\Program Files\Microsoft SQL Server\90\DTS\Packages\DEVDataExchange\ETLBase\config\config\Default.dtsconfig'.

Ideas?

I got the same problem. I'm creating a set of packages that i would like to distribute to my customers. A lot of them don't like the idea of me creating environment variables on their server. Therefore I want to be able to use something like .\filename.dtsconfig as the path. This has worked fine for me, but from one moment to another it stopt working ... and I don't know why. I just get an error when I'm loading the ssis package that the .dtsconfig file can't be found. Strange thing is that I haven't changed a thing.

Hope someone can help me out here. I really would like to use relative paths for my configuration file.

Thanks,

Matthijs

|||Nope. You must use absolute paths for reliability and consistency. Create the environment variable and be done with it. Mandate it!|||

Relative paths cannot be used in SSIS. Many people complain about that but there is a rationale for it. If the package is stored in SQL Server, what would teh path be relative to?

That's why absolute paths are used.

-Jamie

Relative paths

We have a growing issue where we have a relative dtsconfig file (which stores the absolute base path of the ETL packages). This way we can keep the ETL projects failry portable - only having to modify one value in the dtsconfig file. The master package that defines the dtsconfig location (which is config/Default.dtsconfig) usually interpretates this location to be relative the project. The problem is that every now and again when you open this package in .NETStudio, the path is interpreted differently and causes: config/Default.dtsconfig to state invalid path. But when we delete the variable (which defines the dtsconfig path), save/close and open/recreate it works again. This may or may not be supported MS method, but I was curious to know why this gets messed up. Is there somehwere in the .NET framework that defines what "/" is relatively under?

For example: Our absolute config path is "D:\Program Files\Microsoft SQL Server\90\DTS\Packages\ETLProject\ETLBase\config\Default.dtsconfig" but using: "config/Default.dtsconfig" for xml file value works. However, sometimes we will get an error stating that this file cannot be found, and when we just try to delete (without saving and closing) and immediatelly try to put "config/Default.dtsconfig" again and hit next, we get an error and the path is now:

'D:\Program Files\Microsoft SQL Server\90\DTS\Packages\DEVDataExchange\ETLBase\config\config\Default.dtsconfig'.

Ideas?

I got the same problem. I'm creating a set of packages that i would like to distribute to my customers. A lot of them don't like the idea of me creating environment variables on their server. Therefore I want to be able to use something like .\filename.dtsconfig as the path. This has worked fine for me, but from one moment to another it stopt working ... and I don't know why. I just get an error when I'm loading the ssis package that the .dtsconfig file can't be found. Strange thing is that I haven't changed a thing.

Hope someone can help me out here. I really would like to use relative paths for my configuration file.

Thanks,

Matthijs

|||Nope. You must use absolute paths for reliability and consistency. Create the environment variable and be done with it. Mandate it!|||

Relative paths cannot be used in SSIS. Many people complain about that but there is a rationale for it. If the package is stored in SQL Server, what would teh path be relative to?

That's why absolute paths are used.

-Jamie

Relative path for child packages

hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.

Is there any property in the SQL Server Agent or mayby a workaround to solve this?
Use absolute paths. Any reason you need relative paths?|||

Hi Santiago,

Unfortunately you cannot pass parameters to SQL Agent jobs. Workarounds usually involve:

1. Package configurations: set the path in a variable and use Expressions in the Package Connection Managers to dynamically apply the variable value.

2. Call the package dynamically: use a stored procedure to build a "dtexec" command-line and execute it via xp_cmdshell.

There are other ways to accomplish this as well. Personally, I recommend package configurations. I use xp_cmdshell less and less these days and when I do I enable it, do what I need to do, and disable it.

Hope this helps,

Andy

|||You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.|||

jwelch wrote:

You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.

Same here. This is (one of) the reason(s) that I always use the same folder structure on all of my projects.

Common folder structure

(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx)

I also include the root path variable in my package template.

SSIS: Package Template

(http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx)

-Jamie