October 15, 2014 at 5:21 pm
I have inherited a couple dozen SSIS package files (filename.dtsx).
We have a situation now where we need to replace all references from one network location to another.
So, if the current files are referencing:
\\oldlocation\sub1\sub2\filename.ext
We need to update them all to:
\\changedlocation\sub1\sub2\filename.ext
It seemed to me, the easiest way to do this would be to simply do a find/replace on the contents of the dtsx files themselves, since they are really just XML files. So we'd replace all instances of \\oldlocation with \\changedlocationlocation. Seems simple.
But the issue is, when I open the dtsx file in Visual Studio and pull up the Variables window, the Values I'm seeing there have nothing to do with the values I'm seeing in the dtsx file itself. And modifying the values in the dtsx file has no effect on the Variable Values I'm seeing when I open the modified file in Visual Studio.
What's going on?
October 15, 2014 at 6:06 pm
Just to confirm, you're rt-clicking the dtsx and opening it in, say, Notepad++, then moving down to the DTS:ConnectionManager with the "ObjectName" for your connection in the package, and looking at DTS:Property Expression DTS:Name="ConnectionString" tag?
You'll note that this section doesn't have a default value that you'd usually load into the window when you build the object. Here's an example from one of my packages that uses expressions for the packages (I use configurations heavily).
<DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property><DTS:PropertyExpression DTS:Name="ConnectionString">@[User::FilePath] + @[User::ClientFileName]</DTS:PropertyExpression><DTS:ObjectData><DTS:ConnectionManager>
However, for the one I'm looking at, if I scroll down a ways into the FlatFileColumn information, I find:
<DTS:Property DTS:Name="ConnectionString">\\SomeServer\Software Deployments\Stage\Dev\SSIS Jobs\SomeClient\Clients_</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>
I don't recommend working against the XML in the dtsx, personally, unless you're just trying to explore and see what's under the hood. Use Visual Studio.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 15, 2014 at 7:55 pm
Yes, I am right-clicking the dtsx file and opening in Notepad++.
Sorry if I'm not exactly following the rest of your reply. But we have flatfile connections, as well as loops that check certain folder locations for files, and then move those files to other locations after processing. Some of this information is stored in Variables.
I'm just not clear on how to edit the variable values via Notepad++. It won't be practical to do it via SSIS, since there are are so many variables and so many files, and some of these packages were saved with "encrypt sensitive with user key", so I don't even want to mess with them in SSIS unless absolutely necessary.
Does that make sense? Just trying to find a simple solution for the time being.
October 15, 2014 at 8:53 pm
IT sounds like the package is using SSIS configurations to get the server name for the file path. Open the package in Visual Studio and from the SSIS menu, you should see an item called "Configurations". Open this and you should see how the package is being configured. The values can be stored in a number of different locations depending on how the package was designed. Regardless of that, the values from these configurations overrides what you are seeing in Notepad - Visual Studio applies these values whenever the package is loaded and the same happens at runtime.
Your best option is to work our where the configuration values are actually being stored and update the server name there.
October 16, 2014 at 10:08 am
Okay, thank you, now I understand what is going on. These values appear to be stored in a database table, which (if so) will make this change easy to do. 🙂
It does seem a bit strange to store connection information in a database, which itself requires connection information... But I'll try to figure out that whole chicken-egg paradox on my own. 🙂
UPDATE: Apparently the package is loading the initial connection to the database via an "Environment Variable", which I gather is a hard-coded value that has been set in Windows:
System Properties>Advanced Tab>Environment Variables Button>System Variables.
So as long as that value is good, I will hopefully be able to modify the values in the corresponding database table. Hooray for learnings! 🙂
Thanks as always guys!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply