Blogs
Welcome to EMC Consulting Blogs Sign in | Join | Help

Welcome to EMC Consulting Blogs

SSIS Junkie

SSIS: Absolute and relative paths

SSIS uses absolute paths to point to objects on the file system. What does that mean? In short it means that in order to point to a file I would have to use the full absolute path, I couldn't give a path that is relative to where the packages resides.

A common complaint about SSIS is that it does not allow the use of relative paths. The most useful application of this feature would be the ability to execute packages (using the Execute Package Task) where all the packages are (as is typical) stored in a single folder and there have been many people complaining about the absence of this feature over on the SSIS forum. I do not know why relative paths are not supported in SSIS but I suspect that it is because packages do not always reside in the file system, sometimes they reside in SQL Server's msdb database and in this instance there is no location for a file path to be relative to.

Due to the reason just stated I have always agreed that stipulating the use of absolute paths within SSIS was the right thing to do (and indeed I have championed it) however of late I have changed my mind. Support for relative paths would greatly simplify package deployment and package management and for this reason I have made the following submission to Connect:

Description

A common complaint about SSIS is that file references cannot be relative to wherever the package resides. Relative paths would greatly simplify package deployment and management, especially when using the Execute Package Task.

Proposed Solution

Please introduce a new system variable called [System::PackageLocation] (or similar) that contains the location of the package on the file system. This variable could be used (in conjunction with BLOCKED EXPRESSION to provide support for relative paths.
A developer would be able to deploy all of his/her packages to the same location and be confident that through the use of [System::PackageLocation] the Execute Package Task could always easily find the packages that it was executing.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=341880

[This is in addition to a number of existing submissions for similar functionality.]

 

If you require similar functionality today then you can ape the [System::PackageLocation] behaviour that I just described by telling the package where it is when it executes. I describe a strategy for doing this in my blog entry Common Folder Structure from January 2006.

What do you think? Should SSIS support relative paths? (That's probably a rhetorical question but I'll ask it anyway.)

-Jamie

UPDATE, 14th July 2008: Greg Galloway has posted an alternative method to leveraging relative paths in SSIS2005. Read it here: http://www.artisconsulting.com/Default.aspx?tabid=94&EntryID=9

Published 04 May 2008 20:19 by jamie.thomson

Comments

 

John Welch said:

Yes, I think it should - but what should the variable contain when the package is stored in MSDB?

May 5, 2008 03:17
 

Trumpi said:

Yes please!!! I want!!!

May 5, 2008 09:01
 

Matthias said:

Curious, I was just wrestling with Business Intelligence Studio and had some issues with relative paths.

I had an Access database set up as an OLE DB source and wanted to use relative paths to point to the actual file as the package later will be deployed on another machine where I can't exactly know the paths. Strangely, when testing the connection in the Studio, it worked, but when I tested the final package, it didn't. Both situations assumed different starting points.

Now, somehow relative paths are accepted (though very confusingly supported), but you say they ain't. I'm confused.

Anyway thanks for sharing, I'll see if I can implement your workaround.

So yes, support for relative paths is a must!

May 5, 2008 10:43
 

MatthewRoche said:

Voted and commented - I think this is a great idea.

May 5, 2008 14:23
 

Michael said:

Natively this would be great. In the meantime, we are currently using relative paths by using expressions on the Connection String property of the .dtsx package to set an absolute path based on where the package started.

May 5, 2008 18:51
 

Merrill Aldrich said:

Yes please! This would be great, even if it worked only under dtexec/BIDS and not when deploying to SQL Server / MSDB.

May 5, 2008 19:01
 

Sachin said:

How would the System::PackageLocation variable get set?  Should it be a deployment action (setting the variable appropriately) or should the executor of the ssis package be aware of this and set it accordingly every time it sets the package?

I'm not sure I like either of those options, to be honest.

May 6, 2008 00:36
 

Langston said:

I went ahead and voted for it. But I'm not sure this adds that much value. Once you code your packages using Jamie's insightful "common folder structure" technique, the developer would then use that as a template for all future packages (i.e, it wouldn't be something that a developer would have to code every time a new package is created).

However, there maybe other reasons for "System::PackageLocation" other than deployment, which is why I voted for it ultimately.

May 6, 2008 01:10
 

jamie.thomson said:

John,

I guess it should contain "msdb". I don't care to be honest seeing as, in that scenario, the variable wouldn't be used anyway (I doubt it anyway).

Matthias,

I think your experience shows why this is required.

Sachin,

The point about this is that the execution engine sets it for you. its not something that the user has to set. Remember that system variables are read-only (which is why they are perfect for this) - they contain informaiton pertinent to the package execution.

Thanks all for the comments.

-Jamie

May 6, 2008 09:55
 

Sachin said:

Makes sense, I suppose.  It covertly also defines a usage pattern for all of us, which can only be a good thing.  The more we do by convention, the less we have to worry about.

May 9, 2008 01:11
 

Log Buffer #96: a Carnival of the Vanities for DBAs said:

PingBack from http://www.pythian.com/blogs/996/log-buffer-96-a-carnival-of-the-vanities-for-dbas

May 9, 2008 18:52
 

SSIS Junkie said:

In the past I have advocated (and still do advocate) the use of a user variable in SSIS packages to store

May 19, 2008 14:13
 

jamie.thomson said:

"It covertly also defines a usage pattern for all of us"

ABSOLUETLY Sachin. I sometimes think that there are too many ways to do things in SSIS. If there were fewer ways of accomplishing things then there's more chance that we'd do stuff the same way, right?

"Convention over configuration" I think its called in some circles!

"SSSIS should have less knobs to tweak so as to ensure more uniformity across packages". That would be an interesting discussion to have! Anyway, I'm digressing!

-Jamie

May 19, 2008 14:18
 

joun said:

Hi all,

I am fairly new to SSIS and very new to "blogin". But I wrestling with an SSIS issue. In the past "SSIS junkie" has been most resourceful, so I signed up. I have an SSIS package that connects to flat files and I have been running tests for the past month correctly until today the package no longer sees the flatfiles. EVERY flat file connection simply stops working. Error: Must provide a valid filename. I have tried to create a new connection from scratch, tested all (13) my ff connections, I tried different files to no avail. It was working well, the last modification I made was to a script component to reproduce cursor behavior in SSIS. Pauth is determined by a variable and files are on a USB drive.

Can someone help please.

Thx in advance.

June 17, 2008 07:11
 

Bill said:

Another thought, what about the .dtsConfig files?  As it stands today, in the IDE if you don't provide a physical path it will work (i.e. if the XXXX.dtsConfig file lives in the same directory as the .dtsx file and the connection manager just has XXX.dtsConfig in it, everthing works fine).  When you deploy (which we do using NANT to the file system) you have to put the physical path to the .dtsConfig file in the package.  We have a utility that modifies the Configurations collection in the package as it is checked out from source control, then verified, then copied to the production directory.  It's a bit of a nasty hack, but it works...it would be nice if the path to the dtsConfig file was relative too.

I don't believe that putting it in a system variable will handle this type of scenario....the configuration appears to happen BEFORE the variables get set or the package starts executing, and there isn't a way to attach an expression to a dtsConfig path that I've found....

July 8, 2008 23:42
 

jamie.thomson said:

Bill,

"the configuration appears to happen BEFORE the variables get set or the package starts executing, and there isn't a way to attach an expression to a dtsConfig path that I've found"

Correct on both counts. Davide Mauri provides some extra background: http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx

Threr must be mystical forces at work because the email that appeared in my inbox immediately before the one telling me about your post here was about this very same subject. Someone I know uses the same technique that you describe in SSIS2005 to give the illusion of relative paths for configuration files. The email I read this morning described a late breaking change in SSIS2008 that means this technique doesn't work anymore - basically the designer changes a relative path to an absolute path and there's nothing you can do about it. I guess if you use undocumented "features" you run the risk of getting burned.

Your utility might be useful to him and others. Care to share?

cheers

Jamie

July 9, 2008 09:55
 

Greg Galloway said:

We have chosen to use relative paths in Integration Services on several projects now, and since it has worked well, it is time to share this tip with the community. The whole purpose of using relati ...

July 13, 2008 22:39
 

Sumting said:

I voted my 5 stars too!  It made to my Top 3 pains of the week!

August 14, 2008 22:36
New Comments to this post are disabled

This Blog

Syndication
EMC2, EMC, and where information lives are registered trademarks of EMC Corporation.
All other trademarks used herein are the property of their respective owners.                  
© Copyright 2009 EMC Corporation. All rights reserved.                                                    
Powered by Community Server (Personal Edition), by Telligent Systems