May 9, 2019 at 10:11 am
In multiple projects I always want to document the variable. Ideally the name, the value and (if applicable) the expression behind the variable.
I can not install software so this would have to be done in code (script task)
The packages are stored in file system so not available via SSMS.
I have tried a script task (C#) to get a list from VariableDispenser but for that I would need to lock for read. To do that I would need to know the name of the variable.
Is it possible to lock for read on the whole collection
Or am I missing something obvious
Cheers
M
May 9, 2019 at 12:42 pm
this was an interesting request for me.
i was able to do to this via powershell and shredding the xml of each dtsx package.
the script below scans a whole folder and outputs to a single results file.
hope this helps.
$Results = "C:\Data\AllPackageVariables.txt"
$scriptPath = "C:\Data\SSIS\"
##--save our results here.
Set-Content -Path $Results -Value "PackageName|VariableName|PackagePath" #erase the file contents for the header to start of this run
#every dtsx under a directory or sub directory
foreach($f in Get-ChildItem -Path $scriptPath -Recurse -Include *.dtsx)
{
$FilePath = [System.IO.Path]::GetDirectoryName($f.FullName)
$JustTheFileName = [System.IO.Path]::GetFileName($f.FullName)
[xml] $pkg = Get-Content $f
# [xml] $pkg = Get-Content "C:\Data\SSIS\ConsolidatedDBAReports\ConsolidatedDBAReports\DBADailyTasksWithPowerShell.dtsx"
$allnodes = $pkg.GetElementsByTagName("DTS:Variable")
#$allnodes|Select-Object -Property ObjectName | Out-GridView ##preview the data
foreach($node in $allnodes)
{
#$node = $allnodes[0]
$sb = [System.Text.StringBuilder]::new()
$sb.Append($JustTheFileName) | Out-Null;$sb.Append("|") | Out-Null;
$sb.Append($node.Namespace)| Out-Null
$sb.Append("::")| Out-Null
$sb.Append($node.ObjectName) | Out-Null;$sb.Append("|") | Out-Null;
$sb.Append($FilePath) | Out-Null;
Out-File -LiteralPath $Results -InputObject $sb.ToString() -Append -Encoding ASCII -Width 99999
}
}
Lowell
May 9, 2019 at 12:49 pm
oops i missed the variable value request.
you need the data type and the value:
$node.VariableValue.DataType
$node.VariableValue.'#text'
Lowell
May 9, 2019 at 2:03 pm
Going back to the script task, you do not need to know variable names in order to iterate round the variables collection. Here's a chunk of code I copied from docs.microsoft.com which illustrates that:
Application app = new Application();
// Load a sample package that contains a variable that sets the file name.
Package pkg = app.LoadPackage(@"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx", null);
Variables pkgVars = pkg.Variables; // Loop over the collection using the foreach keyword.
foreach (Variable pkgVar in pkgVars)
{
// Print variables only from the User namespace.
if (pkgVar.Namespace == "User")
{
Console.WriteLine("Variable: {0}, {1}", pkgVar.Name, pkgVar.Value.ToString());
}
}
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2019 at 7:13 pm
Thanks peeps.
will have to look at Powershell. Will pick up the script task code as that seems to suit me better
Many Thanks
Mark
May 14, 2019 at 7:14 am
Used a message box to show the variables. Will later update to place in a file. Is there a way to create the variables you need in a package rather than typing them in manually?
In this update I will probably have some 10 extra variables to add. I think I am almost getting borderline close to creating individual templates.
Thanks.
Mark
May 14, 2019 at 1:46 pm
Are you saying that you have multiple packages to modify and each of them needs to have the same 10 variables added?
Can you provide some background on the requirement? There may be an easier way.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 14, 2019 at 1:56 pm
I have some 30 packages in my project. They break down into 3 styles of package
20 backup packages
5 that upload data
5 that download data
I should have been more assertive and kept them as three templates that are identical and flexible. However I was misled by what 'the project needs' and they are currently in different states.
I now need to add in around 10 variables to each package to hold configuration data (as that was not important to 'the project needs')
Looking at the XML behind the package it looks like the variables would need an ID. Apart from that I could copy and paste them in?
So a nice non manual way of adding variables would be awesome.
Cheers
Mark
May 14, 2019 at 2:07 pm
Config data should be held in parameters, not variables.
Assuming you are using the project deployment model, parameters can be added at the project level.
So you can do it all in one hit.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 14, 2019 at 2:20 pm
Without going into details. I am very constrained. Project deployment is not possible. They don't own the infrastructure and the DBAs insists that we cant use project deployment for some reason that I forget know. There are a number of other things I would like to use that are out. Development and production environments are on different domains and the jobs get to be run under different accounts. Add that I dont like the custom task I have to use to connect to a web source, the Proxy to get through the firewall (different for dev and prod)
Add to that an Agile environment that resembles make it up on the day and I would describe the current setup as chaotic or just plain anarchy.
I chose config files as the simple solution. I can place a file on the development laptop and I can do the same on the server. Therefore it is an option that will work and in my current world that is ample. Add to that it is expandable. The final thing is that I think the person who maintains this could possibly understand what is going on. Parameters and Project deployment would blow their mind.
Apologies for the rant but it is a challenging arena that I work in.
M
May 14, 2019 at 2:41 pm
Rant understood!
The only possible non-GUI way I can think of for adding these variables to multiple packages is as follows:
Note: this may not work, so ensure that you keep a copy of the unmodified packages.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply