Error analyzing SSIS 2012 packages with project connection managers

[keif888]

Hi there.

I tried out analyzing some SSIS packages and tables in my SQL Server 2012 test environment and ended up with the following error:

Error occurred: 'The connection "{B1505881-47D0-4DE3-9B2A-77F02CD58D9F}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I analyzed the packages using file folders, not SSIS folders (I did not find out the right syntax for analyzing my SSISDB-deployed projects yet, any advise here?) and the connection is a project connection manager. When I convert it to a package connection manager everything works fine.

Am I doing something wrong or are project connection managers not supported by now (or only from SSISDB)?

Best regards and thank you for this great tool!

Boris

[keif888]

kmartin wrote Jan 30, 2013 at 8:34 AM At this stage SQL 2012 compliance is limited to SQL 2008 features... As the project connection managers are new in SQL 2012 they are not yet supported. wrote Feb 15, 2013 at 5:03 AM

wrote Jul 24, 2015 at 9:38 PM

fmoeller wrote Feb 2, 2016 at 3:25 AM yes this would be a very welcome enhancement. I ran into this problem today. fmoeller wrote Jul 29, 2016 at 5:06 AM I upgraded to Alpha19 today. It seems to successfully access SSISDB. However, project connection managers do not work.

One gets the following error: Loading SQL package 'xxx'... Error occurred: 'The connection "{54821CEC-6043-47D4-92A1-8A3F20F1DB56}" is not found. This error is thrown by Connections collection when the specific connection element is not found. ' WillSampson wrote Jan 12 at 7:20 AM I am having identical issue. The functionality of this application is amazing. Pulled SQL, SSRS, and SSAS, this is the last piece for us to deliver dependency tracking for our analysts and users. Hope this can be accomplished in next upgrade! Thanks,

Bill wrote Jan 21 at 7:22 AM

WillSampson wrote Jan 21 at 7:22 AM For anyone that is interested, I built a workaround for this. It is not elegant but it works. Instead of grapping connection via SMO, I am catching branching the connectionmanager when it encounters a guid value. I then shred the connection via an xml text reader to get the actual Conmgr file . I then shred the .conmgr file and build a connection object by supplying it with the extracted values.

Basically just added this; private ProjectConnectionData ProjectConnection(Package package, Guid ConnectionGuid) and tweaked EnumerateSqlTask()

[fmms]

Will Sampson seems to have patched it locally. Sadly I cannot find his mail address to add him here.

[fmms]

I just tested release v0.20.0.0 and it still complains about every project connection manager.

Loading file package 'C:\Users\xxx\AppData\Local\Temp\SSISMD90bd48f5-091f-426a-a772-203323216f42\project\package.dtsx'... Error occurred: 'The connection "{xxxxxxx-310B-4503-B78A-xxxxx}" is not found. This error is thrown by Connections collection when the specific connection element is not found.

[keif888]

Packages that are deployed into SSISDB will now work correctly.

Packages that are stored on the file system, and which use Project Deployment will not work correctly. (I haven't worked out how to handle these without using an ugly solution as suggested by WillSampson yet.)

[fmms]

@keif888 thanks very much, I am so happy. I get an exeception, but I solved that in #13 and it has nothing to do with project deployment.

[fmms]

Hi @keif888 for the file system project deployment modell you could just build the SSIS project from scratch. Have a look at https://github.com/sabinio/SSISMSBuild/blob/master/SSIS2012Tasks/DeploymentFileCompilerTask.cs#L131 . Could that work?

[keif888]

The issue is working out if a .dtsx file is in project or package deployment mode, without any other input. My current thinking (not coded yet), is to scan the folder for .params or .conmgr files, and if any found, assume all dtsx files are project deployment. In that case for each .dtsx file, create a project, and load the .params, .conmgr and .dtsx content into the project, and then scan it. Logically this will work, and I'll give it a go next. The link above should help me build the project, thanks.

[keif888]

Latest commit (https://github.com/keif888/SQLServerMetadata/commit/c56b747d84833e72c854d94b251aa9bad600f1e8) has the capability of handling .ispac files, or detecting that it needs to build them, and utilise the .dtproj file that should be there. Assumption is that if there isn't an ispac file, then there will be a .dtproj file, as you are scanning a source location. More testing of this is required, to ensure that it isn't double scanning, and that the locations utilised are appropriate. Once testing is complete I will do another release.

[keif888]

File system .ispac and file system .dtproj (detected by .conmgr and .params files) is now in V0.22.0.0 release.