How to Extract the databasename from a Connection String in SSIS Using Expressions

Learn how to effectively extract the `databasename` from a connection string using SSIS expressions with a clear, step-by-step guide!
---

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Find databasename in connectionstring using SSIS expression

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Extracting the databasename from a Connection String in SSIS

When working with SQL Server Integration Services (SSIS) in version 2019, you may encounter a situation where you need to extract specific data from a project parameter containing a connection string. A common requirement is to get the databasename, especially if you are handling multiple connection strings dynamically. In this post, we’ll explore how to extract the databasename effectively using SSIS expressions.

The Challenge

Imagine you have a project parameter with the following connection string:

[[See Video to Reveal this Text or Code Snippet]]

Your task is to extract the CoolDatabase portion from the Initial Catalog segment without knowing its length beforehand. The challenge is particularly tricky if the database name changes or varies in length.

The Solution

To tackle this problem, we can use SSIS expressions to dynamically determine the desired portion of the connection string. Here’s a breakdown of how to achieve this:

Step 1: Extract Just the Database Name

If you only want the result to show as CoolDatabase, you can use the following expression:

[[See Video to Reveal this Text or Code Snippet]]

Explanation:

FINDSTRING(@ [$Project::DOELSERVER_ConnectionString], ";", 2): This part finds the second occurrence of the semicolon ;, marking the end of the Initial Catalog.

LEFT(...): This function returns everything to the left of that semicolon.

REVERSE(...): Reverses the string to locate the = character for the beginning of the database name.

RIGHT(...): Finally, it extracts the database name by pulling from the right side of the string.

Step 2: Extract the Full Section Including Initial Catalog

If you prefer to have the result as Initial Catalog=CoolDatabase, you would use this expression:

[[See Video to Reveal this Text or Code Snippet]]

Difference from Step 1:

The only distinction is that in this expression, the final RIGHT function stops when it encounters the ; instead of =. This means it includes the Initial Catalog= part along with the database name.

Conclusion

Utilizing SSIS expressions, you can dynamically extract the databasename from your connection strings efficiently. The approaches we've outlined allow you to handle variations in database name lengths while ensuring you retrieve precisely what you need for your ETL processes.

This flexibility is crucial for managing connections in various environments and workflows, enhancing the robustness of your data integration efforts.

By mastering these expressions, you'll be well-equipped to tackle similar challenges in your SSIS projects. Happy coding!