Weird variables / expressions issue
-
We have an SSIS package that is totally hosed (got corrupted somehow) so I'm trying to rebuild it from scratch. This package was built several years ago by a vendor that we no longer have ties to. As we started looking into it, we found a lot of strange things and I'm wondering if any of you have seen this sort of thing before.
1) Native SQL OLE DB / ADO Connection managers have expressions attached to them on the ConnectionString that is the same exact information as the CM would contain if it didn't have an expression attached. For instance, a regular connection string in the properties might be "Data Source=MyServerDev;Initial Catalog=MyDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;". And then when I scroll down to Expressions, there's an expression attached to the ConnectionString property that is that exact same information verbatim.
2) Variables have expressions referencing themselves.
Has anyone ever seen coding like this before?
There are a lot of Script Component transformations in a group of Data Flow tasks (Given their position at the end of the precedence flow, I think they're Destination instead of Source or Transformation types). I'm not sure if that has anything to do with it. But I'm really weirded out by this. It seems to be a lot of ridiculous redundancy to my eyes.
Thoughts?
- Brandie Tarvin - Friday, February 23, 2018 7:52 AMWe have an SSIS package that is totally hosed (got corrupted somehow) so I'm trying to rebuild it from scratch. This package was built several years ago by a vendor that we no longer have ties to. As we started looking into it, we found a lot of strange things and I'm wondering if any of you have seen this sort of thing before.
1) Native SQL OLE DB / ADO Connection managers have expressions attached to them on the ConnectionString that is the same exact information as the CM would contain if it didn't have an expression attached. For instance, a regular connection string in the properties might be "Data Source=MyServerDev;Initial Catalog=MyDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;". And then when I scroll down to Expressions, there's an expression attached to the ConnectionString property that is that exact same information verbatim.
2) Variables have expressions referencing themselves.
Has anyone ever seen coding like this before?
There are a lot of Script Component transformations in a group of Data Flow tasks (Given their position at the end of the precedence flow, I think they're Destination instead of Source or Transformation types). I'm not sure if that has anything to do with it. But I'm really weirded out by this. It seems to be a lot of ridiculous redundancy to my eyes.
Thoughts?
If you understand exactly what the package was intended to do, it's probably a good idea that you build it to work the way you understand, to ensure ease of maintenance in future. You can always refer to it later if you find that your version does not work the same way as the old one.
I've seen enough weird spaghetti coding in SSIS packages to realise that different people's minds work in very different ways, so it's not necessarily anything to worry about.
I can't think of a sensible reason for things being the way you describe.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 -
Never seen this, but not a deep SSIS guy. I'll ask a few others.
-
It's a lemon...might as well make lemonade 🙂
-
Inheriting code is always enlightening 🙂
For the issue with the expression on the connection string, there's no real value in having it set up that way if the expression is just static text. Unless that connection string is being dynamically built in the expression, it's adding some complexity without any added value.
On the variable issue - That's got to be an oversight when it was first developed. That expression doesn't really do anything.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices -
The worst part about it is the people who were developing this code kept insisting on turning SQL tables into flat files and doing stupid stuff like that, no matter how many times we told them otherwise. I even told them they needed to put comments in their code so everyone could understand later, but usually the comments ended up being along the lines of the stored proc name with "select" or "insert" added.
So, yeah, we inherited a mess. I was going to try and assist the Devs in rebuilding the package, but I'm clueless on how this thing is working. So I guess I'm going to leave it to them to fix.
- Brandie Tarvin - Friday, February 23, 2018 7:52 AMWe have an SSIS package that is totally hosed (got corrupted somehow) so I'm trying to rebuild it from scratch. This package was built several years ago by a vendor that we no longer have ties to. As we started looking into it, we found a lot of strange things and I'm wondering if any of you have seen this sort of thing before.
1) Native SQL OLE DB / ADO Connection managers have expressions attached to them on the ConnectionString that is the same exact information as the CM would contain if it didn't have an expression attached. For instance, a regular connection string in the properties might be "Data Source=MyServerDev;Initial Catalog=MyDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;". And then when I scroll down to Expressions, there's an expression attached to the ConnectionString property that is that exact same information verbatim.
2) Variables have expressions referencing themselves.
Has anyone ever seen coding like this before?
There are a lot of Script Component transformations in a group of Data Flow tasks (Given their position at the end of the precedence flow, I think they're Destination instead of Source or Transformation types). I'm not sure if that has anything to do with it. But I'm really weirded out by this. It seems to be a lot of ridiculous redundancy to my eyes.
Thoughts?
The connection string expression may make sense if package reads from a parameter file to get the connection string and they have set a design default.
Variables referencing themselves is a No No! It will create a circular reference and send the package into a spin.
What version of SQL was the package originally designed in and how was it deployed? With project deployment for example used in SQL 2012 onward you can recover a lost project or package by recovering it from the SSISDB catalog. - tim.ffitch 25252 - Monday, February 26, 2018 9:35 AM
It was designed in SQL 2008. We've since upgraded to SQL 2012. It was deployed straight to IS via the GUI with the Import Package right-click option, but we have a history of the package in TFS.
The odd thing is, until recently the package worked fine.
-
The design-time values in an SSIS package can be overridden by package configuration, so be sure you check all the package configuration settings and understand which order they are applied in if there are conflicts. And all of these configurations could be overridden by command-line parameter settings in the SQL Agent job step (or whatever else invokes the package).
One use I can think of for the connection string expression is that if the connection manager is configured to point anywhere else when the package is loaded, the expression will override that configuration at runtime to set it back to the design value.
The self-referencing variable expression makes no sense, although you can configure the variable expression property so it is something else at runtime.
The only reason for either of these methods that I can think of is to confuse someone trying to reverse-engineer the package. The much more likely explanation is that the package authors were confused to begin with.
Never Attribute to Malice That Which Is Adequately Explained by Stupidity
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply