SSIS Maitenance Question - You may know the answer
-
Folks:
Just checking whether there is a way to do this....Take a look at the picture attached. We have a ton of SSIS packages. Is there a way that we could have a scripts that can be run to change the SERVER NAME of all the SISIS packages. I mean the server name in the Connection String ( in the picture I have colored them just to hide the info... But you know what i mean )
-
This is much more practical when you make use of a configuration database, so that all such connection string information is stored in the configuration database and read from it on execution. You just keep an appropriate copy of the config database in each environment, and you are good to go.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Agreed, but here is a problem. You would have to have a separate SERVERfor the config database ?
Note: Each SSIS package ( whether it is in the DEV or TEST or PROD environment ( I mean server ) ) will have to access this DB.
So then all of the packages will have to have accesses to this DB.
Question: So have a config table in a separate server ? -
BTW- Some time back someone gave me this script - WORKED fine! It exports all the DTSX files into a given location. Cool.
if someone out there cam tweak this ... I am sure it can be done!
<#
.SYNOPSIS
Export folders and projects from SSIS catalog to local file system.
.DESCRIPTION
Export SSIS projects from the catalog to $ProjectFilePath. Folders in the
catalog will be exported as folders in the file system, and projects will
be exported as *.ispac files.
Environments will not be exported.
.EXAMPLE
.\CatalogExport
#># Variables
$ProjectFilePath = "C:\Users\YYYYYY\Desktop\SSISDB\XXXXXXX"# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"Write-Host "Connecting to server ..."
# Create a connection to the server
$sqlConnectionString = "Data Source=XXXX;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnectionif ($integrationServices.Catalogs.Count -gt 0)
{
$catalog = $integrationServices.Catalogs["SSISDB"]write-host "Enumerating all folders..."
$folders = $catalog.Folders
if ($folders.Count -gt 0)
{
foreach ($folder in $folders)
{
$foldername = $folder.Name
Write-Host "Exporting Folder " $foldername " ..."# Create a new file folder
mkdir $ProjectFilePath"\"$foldername# Export all projects
$projects = $folder.Projects
if ($projects.Count -gt 0)
{
foreach($project in $projects)
{
$fullpath = $ProjectFilePath + "\" + $foldername + "\" + $project.Name + ".zip"
Write-Host "Exporting to " $fullpath " ..."
[System.IO.File]::WriteAllBytes($fullpath, $project.GetProjectBytes())
}
}
}
}
}Write-Host "All done."
- mw_sql_developer - Monday, April 30, 2018 12:06 PM
Typically this database is copied from the DEV environment in which it is created, and then updated in each environment where it exists, and the connection string to the config database in each environment is then an environment variable on each server. It gets a lot more complicated trying to share one database across multiple environments, and such would not work very well either, and largely defeat the purpose of it's design.
EDIT: There is absolutely NO need for a separate server for an SSIS configuration database. They are small and rather inconspicuous in most cases.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - mw_sql_developer - Monday, April 30, 2018 9:52 AMFolks:
Just checking whether there is a way to do this....Take a look at the picture attached. We have a ton of SSIS packages. Is there a way that we could have a scripts that can be run to change the SERVER NAME of all the SISIS packages. I mean the server name in the Connection String ( in the picture I have colored them just to hide the info... But you know what i mean )
Are you hoping to change the name in the source DTSX files, or merely to control the server name based on the environment in which the packages exist?
Are the packages deployed to SSISDB? If so, have you investigated using SSISDB environments to control such things?
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 - Phil Parkin - Monday, April 30, 2018 12:32 PMmw_sql_developer - Monday, April 30, 2018 9:52 AMFolks:
Just checking whether there is a way to do this....Take a look at the picture attached. We have a ton of SSIS packages. Is there a way that we could have a scripts that can be run to change the SERVER NAME of all the SISIS packages. I mean the server name in the Connection String ( in the picture I have colored them just to hide the info... But you know what i mean )
Are you hoping to change the name in the source DTSX files, or merely to control the server name based on the environment in which the packages exist?
Are the packages deployed to SSISDB? If so, have you investigated using SSISDB environments to control such things?
No no! No changing the source DTSx files! I just added that script so that someone can modify it to change the DB name and SERVER Name ( I mean the part that I have highlighted in the picture ) The script is just a starting point showing how to iterate through the ssis catalog.
- sgmunson - Monday, April 30, 2018 12:10 PM
Thank you sgmunson! You answered the question. Aha! Environment variable is the KEY here..... Now I wonder if you could guide me how to set one and assign a value. Hey that works!
- mw_sql_developer - Monday, April 30, 2018 12:37 PM
OK, but what about my second question?
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 -
Phil, Yes they are deployed!
RE: If so, have you investigated using SSISDB environments to control such things
Do you mean environment variables ? Like sgmunson mentioned ?Ok my next question: How do I configure a SQl Task to get a a value from a environment variable. I need some help here. I am not sure how to or where to declare a environment variable ?
- mw_sql_developer - Monday, April 30, 2018 2:06 PM
Not like what SGMunson suggested. These environment variables are wholly contained in SSISDB.
Please have a read here to start learning about them. If you have any questions about them afterwards, please post back.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 -
Thismmight even be something that can be done with project Variables as well, as opposed to environments. It depends if you have your dev and production database on the same server or not. If not, then project Variables may be easier to configure.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Ok figured out!
Question:
Is there a way for us to have an variable at a higher level ( I mean still we have to go one by one and create this variable for each project )
Next Question: When a package is deployed from one environment to another, do those variables also get transferred automatically. - mw_sql_developer - Monday, April 30, 2018 2:59 PMOk figured out!
Question:
Is there a way for us to have an variable at a higher level ( I mean still we have to go one by one and create this variable for each project )
Next Question: When a package is deployed from one environment to another, do those variables also get transferred automatically.There is a much better way of organising things than this.
Create a new folder called Environments (or whatever) and within that, create an environment called Connections (or whatever). Put all of your connections in there.You can then configure any of your other SSIS projects to reference this environment.
Meaning that you only have to change connection details in one place.
To answer your second question, unfortunately the answer is no.
Moving environments from server to server is not as simple as it should be. Though this can be scripted (and you can change 'SERVER_A' to 'SERVER_B' in the process).
More difficult still is scripting the moving of the associations between environment variables and project parameters.
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 -
Phil
Thanks, Take a look at the attached pic. Am i missing something here. I did it the way you mentioned and now it doe snot allow me to select a environment variable. As you can see the part that I have circled in RED is greyed out. Is this because I do not have permissions ?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply