2

We would like to use a SQL Server table as a source for all connection manager values within SSIS. We would prefer to do this at the project level since many of our connections are already at the project level. We would like to be able to easily switch which environments we are looking at based on an input value when the project opens. The reason we need to do this is because when creating our projects in dev we use different connections than when deploying them to prod, and we have hundreds of different connections being used. We don't want to have to switch anything in SSIS or in SQL Server Agent or the SSIS Catalog. We would love for these to be strictly maintained within a SQL Server table.

  1. How can we have these values fed from a SQL Server table into variables that feed the values or parameters? To reiterate, the end product would have 1 single table in SQL Server that contains all columns like [ConnectionString], [InitialCatalog], [UserName], [UserName], [Password], [HeaderRowsToSkip] etc. We would parameterize all connection managers that would have their values fed from this table. We need direction on how to accomplish this.

  2. I would like to be able to contain both PROD and DEV in the same table with an [Environment] column that has a value of 'Dev' or 'Prod', and if possible we would like to have a prompt open when the Project opens in SSIS that asks which [Environment] we would like (A fillable prompt) that would in turn filter the results from the SQL Table to use either Dev or Prod based on what we enter.

If 2) is not possible, we would just use separate tables that could be switched in the connect manager manually based on when we are developing or deploying.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Caleb Fortner
  • 57
  • 9
  • 2
    But environments in SSDT support having different servers/databases as for your connections. Why store these values (unsecurely) in SQL Server (which isn't dynamic?) when the functionality already exists in Visual Studio to do exactly what you're after? – Thom A Dec 26 '18 at 22:23
  • 1
    Also, any reason why you're using SQL Authentication rather than a Trusted connection for your packages? As you're using SSDT, you're clearly on Windows, so using a trusted connection seems like a better idea. – Thom A Dec 26 '18 at 22:24
  • So for a table based config, how do you tell the package which config to use at runtime? You are not reducing complexity here, you are adding to it. It's not a good idea. Have a think about what issues the normal SSIS environment approach gives you, and consider whether this custom database based approach actually solves any of these issues. I doubt it. What actual problem do you have with the normal environment based approach? Note that the database config approach was how it was done in the last iteration of SSIS and it really sucked. – Nick.Mc Dec 27 '18 at 10:06
  • @Larnu The reason environments may not be best is because we have about 20 different connections per project with about 50 different projects. This would be a substantial amount of overhead when switching between DEV and PROD environements. We are using SSO for windows, the passwords we are storing are to other data sources such as Oracle, FTP, DB2, etc. If we had a table with these data sources already mapped and could use a single parameter to change if DEV DB2 points to 'Serverabc' and PROD DB2 points to 'Server123' it would help. Please help if there is something I am missing. – Caleb Fortner Dec 27 '18 at 13:52
  • @Nick.McDermaid I would love to use the new approach. Could you help me understand, or better yet, rethink how to accomplish this. We may have roughly 30 different data sources we are connecting to. Between DEV and PROD, DEV server could be called 'Serverabc' and use one set of credentials, while PROD server is called 'Server123' with a different set of credentials. This x 50. Unless I am missunderstanding environments in SSIS, you have to manually switch each parameter to show which environment is being utilized. With the table approach you can have all of that set up in the table. – Caleb Fortner Dec 27 '18 at 13:55
  • So you would rather a SQL Task query in **every** package to get the relevant connection details for **every** connection manager, and not have any of those connection managers be at project level? That sounds like (a lot) more work to me than using environments. that involves changing every package, not just setting up the details at project level. – Thom A Dec 27 '18 at 13:56
  • 1
    I’m out for a while but I suggest you learn and experiment how environments work. I can’t see any advantage to rolling your own environment management system at all – Nick.Mc Dec 27 '18 at 14:02
  • @Larnu What I am trying to avoid is the amount of work it takes to change from DEV to PROD. I am very open to suggestions as this is all new to my team. Our desire is to have all connections be at a Project level and be parameterized with varriables being fed from a SQL table. This way we don't need to manually change them when switching from DEV to PROD. If there is a better way, please point me in that direction or open a dialog about it. – Caleb Fortner Dec 27 '18 at 14:22
  • 1
    What do you mean avoiding the amount of work to change from Dev to prod? After you've configured the environments in visual studio, is selecting prod/dev from a drop down in the toolbar really that much of a hardship? If so, there is nothing out there that will help you. – Thom A Dec 27 '18 at 14:32
  • 1
    @Larnu This really made me laugh. I honestly had no idea there was the 'Configuration Manager'. After you and Nick.McDermaid suggested environments I began to research them. I just found an article walking through this and it is exctly what we are looking for. Thank you both for your patience and help with this! Go ahead and put it into an answer and I'll mark you as answering this. – Caleb Fortner Dec 27 '18 at 14:39
  • 1
    Looks like you're on the right track, which is great! We've only pointed you, not given an answer here. If, however, you get stuck configuring them then please do ask another question. Feel free to mention me here if you wish to let me know about your other question and I'll try my best to put you back on track. – Thom A Dec 27 '18 at 14:41
  • @Larnu, one more question for you. After I create the configuration managers and apply the Project Parameters to DEV and PROD, how can I reuse this Config Manager in another solution? I would like to create one master configuration manager and apply it to all projects past, present and future. – Caleb Fortner Dec 27 '18 at 20:47
  • Environments are not defined in the Visual Studio solution, only in the SSIS catalog. You can script them out but it's clunky but certainly worth the effort. Here's one way. http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/135173/ once it's in a SQL script you can always put that into your solution / version management – Nick.Mc Dec 28 '18 at 11:02
  • @Nick.McDermaid That's rather misleading. Yes, you certainly do configure "environments" in Visual Studio, but they're called configurations. Then you can simply select the active solution configuration and any projects in that solution will use their related configuration. Configurations don't deploy to SSISDB though. From what the OP is describing, it seems far more likely the OP is after Configurations as well as environments, – Thom A Dec 28 '18 at 11:14
  • These configurations can be used to change the values of things like Project Parameters, which are in turn used to define a connection string. For example maybe you have a Project Parameter called "SQLServer" which in the Dev Configuration has the value "DevSQLSrv" and for the Live Configuration the value "SQL2017Srv". Then your OLEDB connection managers have the expression `@[$Project::SQLServer]` for `ServerURL`. Thus, when you change configuration the SQl Server the configured OLEDB Connection Managers point to changes too. – Thom A Dec 28 '18 at 11:18
  • I have a project myself which when the configuration is selected not only are the OLEDB connection managers affected, but also the location of the files (as Dev and UAT have a different location), related web services, TLS setting (Dev is using TLS1.2 where as UAT & Live haven't been updated yet), etc, etc. They can actually be very dynamic and be used to switch "environment" in Visual Studio seamlessly – Thom A Dec 28 '18 at 11:21
  • Well.... the environments I was talking about are environments, not configurations. I've only discovered configurations recently and they look pretty handy although I avoid running prod configs directly in SSDT - only dev configs – Nick.Mc Dec 28 '18 at 11:55
  • @Larnu Yes, you are correct. Prior to pushing to the SSIS Catalog. But we have multiple solutions. Is there anyway to copy a 'Master' configuration file along with the project parameters we have assigned to each configuration, into another solution? I am familiar with copying over the project parameters and modifying the 'ProjectConnectionParameters' tag in the dtproj file. I was hoping there is a OOB way to copy over a large 'Master' config file to each solution. We would use this similarly to how Pentaho uses the pentaho kettle properties file – Caleb Fortner Dec 28 '18 at 13:30
  • No, configurations are solution specific; and the values they change are at project level. If you have multiple solutions all with the same properties, perhaps the problem is that you have multiple solutions rather than several packages, or projects, in a single solution? – Thom A Dec 28 '18 at 13:34
  • @Larnu Thank you again for the clarification – Caleb Fortner Dec 28 '18 at 13:40

2 Answers2

1

It sounds exactly the same as what "Environment Variables in SSIS Catalog" does!

The "Environment" in SSIS Catalog works like a configuration file for parameters, in your case, you can create Prod/Dev Environment, and map the variables in it to the Project Level parameters, which are mapped to Project Level Connection Managers.

Reference: Setup Environment Variables in SQL Server Integration Services Creating a Robust SSIS Development Environment using the SSIS Catalog

Yang.Z
  • 101
  • 1
  • 14
0

Apart from the discussion about how much securely is (a password inside a SQL Server Table, really?), I had made this kind of request using a special combination of PowerShell and in some cases getting info from Servers from SQL Table, for this, you can use a SQL Script Task Editor:

SELECT instanceName,databaseName,DataSource
FROM meta.InfoSSIS
WHERE environmentName = ? --Input parameter corresponding to DEV, INT or PROD

The output of this Query should be feeding the object, for instance, User: DatabaseList which is basically a DataType: Object and which store the info for SSIS instance, DB name, and DataSource. Internally you can have a ForEach Loop object which allows to interact with the DatabaseList object and work on it.

Geovanny Hernandez
  • 239
  • 1
  • 10