January 13, 2020 at 5:46 pm
Hi all,
I'm newbie in SSIS, though my package works fine. Still, there is additional requirement from customer to create configuration file(s) for datasource(s).
The idea is that target database can be changed. I expect that it will be still SQL Server, but nobody knows exactly. So, mainly I would like to have connection string (server name, server port, database name, username and password / or windows auth type) configured. Where should I start? And yes, I started with googling this, though this time it didn't really help me.
Please advise.
January 13, 2020 at 7:02 pm
Starting with SQL Server 2012 - we now have the Integration Services Catalog. Deployment of SSIS should be converted to project deployment and deployed to the catalog.
I would also recommend making your data source a project data source instead of a package data source, but either will be exposed in the catalog and can be changed at the project/package/job level as needed.
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2020 at 8:04 pm
Hi Jeffrey Williams,
I believe your reply is helpful indeed in general, though this is enterprise customer with a lot of peculiar policies and it doesn't work in this case just like that. This should be done using configuration files... We'll put them into version control then, qa team will do their job, performance officer and security officer will do their ones as well. Only then it will appear in their Integration Services Catalogs. And yes, I'm frustrated a bit also.
Any help with configuration files for datasources is appreciated.
P.S.: I can play with Integration Services Catalogs locally though. Still, this should be configuration file(s) anyway in the end.
January 13, 2020 at 9:54 pm
I think you do need to dig further and explain to them that a SSIS Catalog Environment equates to a xmlconfig file on the filesystem.
this makes it possible to have environment configuration for each environment exactly the same way as a xmlconfig would work.
Not only that but if parameters are masked as sensitive they aren't even visible to anyone except the DBA that has added the environment and even this only when he/she runs the script. (assuming that your company requires a DBA to run these and there isn't already an automation tool doing it without DBA intervention)
but if not mistake even with SSIS catalog it may be possible to use a configuration file - just doesn't work exactly as the others
January 14, 2020 at 11:57 am
Hi frederico_fonseca, thank you for your reply. Actually I can explain this to my management and they would probably accept it as useful information. I believe your advise is great for small companies, but huge enterprise companies work differently at all. I was waiting literally a week to get simple database access, plus I wrote a dozen of emails to gain that. Can't imagine how long it would take to explain everything to all levels of management, security etc so that we all switch to SSIS Catalog. Worth trying anyway, though I would stick to configuration files in scope of this thread if you don't mind.
So, I followed this guide: https://www.tutorialgateway.org/ssis-package-configuration-using-xml-configuration-file/
(besides a bit different menu in modern version, it is helpful)
But... it works for me only if I have two connections already and want to switch between them.
So I have connection details in XmlConfig.dtsConfig :
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[ConnectByProxy]" ValueType="Boolean">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=DESKTOP-L12T1N1\SQLEXPRESS;Initial Catalog=TestDataManagement3;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[ConnectUsingManagedIdentity]" ValueType="Boolean">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[Description]" ValueType="String">
<ConfiguredValue/>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[InitialCatalog]" ValueType="String">
<ConfiguredValue>TestDataManagement3</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[Name]" ValueType="String">
<ConfiguredValue>DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[Password]" ValueType="String">
<ConfiguredValue/>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[ProtectionLevel]" ValueType="Int32">
<ConfiguredValue>1</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[RetainSameConnection]" ValueType="Boolean">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[Scope]" ValueType="Int32">
<ConfiguredValue>0</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[ServerName]" ValueType="String">
<ConfiguredValue>DESKTOP-L12T1N1\SQLEXPRESS</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3].Properties[UserName]" ValueType="String">
<ConfiguredValue/>
</Configuration>
Just to avoid repeating the same block, imagine that I have also the same block with TestDataManagement4 (instead of TestDataManagement3).
Now I have below block where I can easily change from TestDataManagement3 to TestDataManagement4 (just for testing purpose everything else is the same, server name is the same and target table name exists also in both databases)
<Configuration ConfiguredType="Property" Path="\Package\Execute SQL Task.Properties[Connection]" ValueType="String">
<ConfiguredValue>DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement3</ConfiguredValue>
</Configuration>
Then I should go to menu Build -> Rebuild Solution and run again. The result appears in database that I set in previous step.
But now I try to replace all TestDataManagement4 keywords to TestDataManagement5. Just to let you know that TestDataManagement5 database does exist.
No error on Build -> Rebuild Solution.
Then I press start and receive a bunch of errors like:
Error at Package: The connection "DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement5" is not found. This error is thrown by Connections collection when the specific connection element is not found.
Error at Execute SQL Task [Execute SQL Task]: Connection manager "DESKTOP-L12T1N1\SQLEXPRESS.TestDataManagement5" does not exist.
(basically a lot of duplicated errors with the same text).
How do I set new connection using configuration file and is it possible at all?
January 14, 2020 at 5:10 pm
Hi frederico_fonseca, thank you for your reply. Actually I can explain this to my management and they would probably accept it as useful information. I believe your advise is great for small companies, but huge enterprise companies work differently at all.
Incorrect assumption - I work for a multinational enterprise company (30k users, 20k servers+) and we use SSIS catalog. It has all to do with knowing the technology and how to use it.
Using filesystem config files are another security risk that is precisely what big companies try to avoid, while it may be acceptable on a small company.
regarding your example of working on Visual Studio.
you CAN NOT change names of connections (which is what you tried to do) - what you can do is change the connection string parts or in full (full preferred) (server name, database name, username, password and a few other values)
As a word of advise - when creating connection managers name them according to their purpose - DO NOT accept the default name created which includes servername, instancename and database name.
use a clear name that easily identify what it is - on your case for example it could be "DataManagement" - does not matter if it is test, dev, prod - the connection string is what is doing to decide which server it connects to - not its name.
January 14, 2020 at 5:35 pm
frederico_fonseca, thanks again for your reply. My assumption regarding small companies was wrong.
what you can do is change the connection string parts or in full (full preferred) (server name, database name, username, password and a few other values)
...
use a clear name that easily identify what it is - on your case for example it could be "DataManagement"
If I understand this right, I should name my connection with meaningful name according to its purpose, for example projectX. Then I believe I will see in *.dtsconfig file the name of connection, projectX, and also it's credentials, for example database name. I named it wrong and thus I can see it's name including database name. I can't change the name.
I'll try to name connection right and will update.
Thanks.
January 16, 2020 at 9:31 am
Ok, now additional requirements from customer. They need to set connection string in .conmgr file and put variable there instead of hardcoded value, so that they are able to set it in SSIS UI. How do I achieve this? Please advise.
January 16, 2020 at 1:20 pm
you need to get back them and tell them if they wish to use the .conmgr file that means using SSIS Catalog and Environments and Configurations within the catalog as .conmgr as part of Project Based deployment
Package deployment uses dtsconfig files where the connection strings are defined within and these are normally deployed to the filesystem.
by all the questions you are asking neither you or your customer knows how SSIS works and most likely you need to hire someone that can teach you guys and guide you on best practices.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply