Can't get component to run in SQL Server 2012

May 15, 2013 at 5:31 PM
Hi;

First off, let me thank you for creating such a great SSIS component. I used to have a query to suck my A/D stuff out and then had to handle the 1,000 Row page size limit manually and it was quite clunky. This component solves so many of my problems except one.

I can get it to run on our server.

It works perfectly fine on my development machine (VisualStudio 2010 and SQL Server 2012 Developer Edition). However I can’t get it to work on our server (Windows 2008R2, SQL Server 2012BI). My server guy followed your steps to the letter. The component shows up in the SSIS toolbox in SSDT just as it does on my dev box.

When I deploy the job, and schedule it to run, it fails with a bunch of errors, but the basic one says that the System can’t find the file (See below).

Yes – I have set Run64BitRuntime to False (Actually tried both just for fun)

Any idea what’s blocking me?
DFT - Get Available Data from Active Directory:Error: The managed pipeline component "SsisLdapSource.SsisLdapSource, SsisLdapSource, Version=1.4.0.5, Culture=neutral, PublicKeyToken=758d8a4b4299146a" could not be loaded.  The exception was: Could not load file or assembly 'SsisLdapSource, Version=1.4.0.5, Culture=neutral, PublicKeyToken=758d8a4b4299146a' or one of its dependencies. The system cannot find the file specified.
Any and all help would be much appreciated. I can't wait to get this great component into production.

_Cheers.
Ron..._
Coordinator
May 15, 2013 at 10:18 PM
Edited May 15, 2013 at 10:21 PM
Hi Ron,
Could you, please, gather some additional information.
  1. Open Windows Explorer and navigate to c:\windows\Microsoft.NET\assembly.
    Do you see a folder there named SsisLdapSource ?
  2. If yes, inside that folder you should find another folder named something like this v4.0_1.4.0....................
  3. Open Powershell (x86) console (it is important to use x86)
    type these commands (substitute the folder name with what you found in step 2) and send me the output
[System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft.NET\assembly\GAC_32\SsisLdapSource\v4.0_1.4.0.6__758d8a4b4299146a\SsisLdapSource.dll").ImageRuntimeVersion

[System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft.NET\assembly\GAC_32\SsisLdapSource\v4.0_1.4.0.6__758d8a4b4299146a\SsisLdapSource.dll").FullName
Do the same steps for DirectoryServicesExtensions.dll (it is also located in the c:\windows\Microsoft.NET\assembly)
[System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\SsisLdapSource.dll").ImageRuntimeVersion

[System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\SsisLdapSource.dll").FullName
Thanks
Alex.
May 16, 2013 at 1:24 PM
Alex - Thanks for getting back to me so quickly. It takes a really long time in my company to get permission to do stuff on the servers so we're trying a couple of things. Trying the PowerShell commandlet erroed out saying that the Term 'System.Reflection.Assembly::LoadFrom' is not recognized.

Our files are in the exact same location as you show in your sample. That's where they live on my Win-7 dev box as well.

We're going to try a repai install once we get permission and I'll let you know how that goes - Unless you have other info for us in the meantime.

Thanks.
Ron...
Coordinator
May 16, 2013 at 10:28 PM
Hi Ron,
I understand about getting access to production boxes (:

By the way, when you ran PowerShell command did you include brackets around the System.Reflection.Assembly
[System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft.NET\assembly\GAC_32\SsisLdapSource\v4.0_1.4.0.6__758d8a4b4299146a\SsisLdapSource.dll").ImageRuntimeVersion
Thanks
Alex.
May 21, 2013 at 5:35 PM
Hi Alex;

Finally got the guys to do something for me :)

Here's the output we've been waiting for:
PS C:\windows\Microsoft.NET\assembly\GAC_32\SsisLdapSource> [System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft
.NET\assembly\GAC_32\SsisLdapSource\v4.0_1.4.0.6__758d8a4b4299146a\SsisLdapSource.dll").ImageRuntimeVersion

v4.0.30319

PS C:\windows\Microsoft.NET\assembly\GAC_32\SsisLdapSource> [System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft
.NET\assembly\GAC_32\SsisLdapSource\v4.0_1.4.0.6__758d8a4b4299146a\SsisLdapSource.dll").FullName

SsisLdapSource, Version=1.4.0.6, Culture=neutral, PublicKeyToken=758d8a4b4299146a
Hope this helps.
Jun 3, 2013 at 5:05 PM
Hi Alex - Just wondering if you are making any progress with this.

Appreciate all your help, just trying to get it off my plate so i can move on tho other stuff.

Thanks
Ron...
Coordinator
Jun 3, 2013 at 6:34 PM
Hi Ron,
The output from PowerShell look right.
One other thing we could try is to make sure that Client Tools SDK is installed on the server.
To do this:
Run SQL Server Installation Center
Then select Tools/Installed SQL Server features discovery report
Make sure that Client SDK is installed.
Let me know.
Thanks
Alex.
Jun 4, 2013 at 12:59 PM
Hi Alex - Here we go...

Microsoft SQL Server 2012 Client Tools Connectivity 1033 Business Intelligence Edition 11.1.3000.0 No
Microsoft SQL Server 2012 Client Tools Backwards Compatibility 1033 Business Intelligence Edition 11.1.3000.0 No
Microsoft SQL Server 2012 Client Tools SDK 1033 Business Intelligence Edition 11.1.3000.0 No


Hope this helps.
Coordinator
Jun 5, 2013 at 11:45 PM
Hi Ron,
So it appears that the SDK is installed, so we can rule this out.

Just re-reading the thread and the information you got from your Server guy was not everything I asked for. There is another DLL that gets deployed - DirectoryServicesProtocolsExtensions.dll. Please, make sure it is also there.
[System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft.NET\assembly\GAC_32\DirectoryServicesProtocolsExtensions\v4.0_1.4.0.6__2d0af75324c3369e\DirectoryServicesProtocolsExtensions.dll").ImageRuntimeVersion
You mentioned that the component does show-up on the toolbar. Did you try running a simple task from the SQL Data Tools? In other words, does the error only happen when running a scheduled task?

Thanks
Alex.
Jun 10, 2013 at 11:59 AM
Hi Alex - Here the response we get when we run the command you provided (Errors-out)
PS C:\Users\c10860a> [System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft.NET\assembly\GAC_32\DirectoryServicesP
rotocolsExtensions\v4.0_1.4.0.6__2d0af75324c3369e\DirectoryServicesProtocolsExtensions.dll").ImageRuntimeVersion
Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'file:///C:\Windows\Microsoft.NET\a
ssembly\GAC_32\DirectoryServicesProtocolsExtensions\v4.0_1.4.0.6__2d0af75324c3369e\DirectoryServicesProtocolsExtensions
.dll' or one of its dependencies. An attempt was made to load a program with an incorrect format."
At line:1 char:1
+ [System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft.NET\assembly\GAC_32 ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : BadImageFormatException
Any ideas?

BTW - I'm working a theory here and I would like to get your take on it.
We operate in a crazy-secure environment and I was wondering if our problem with your component might be caused by our security constraints. Do you know what ports are used to go query A/D from your component? I'll get our network security guy check to see if the required ports are open.

Worth a shot right? Since the component works VERY WELL from my desktop.

Cheers.
Ron...
Coordinator
Jun 10, 2013 at 8:17 PM
Hi Ron,
The reason that PowerShell command returned that error is that it was executed from a 64bit (default) PowerShell Console, you can try this again from x86 console.
But even the error suggests that the dll is there, so it appears that the GAC contains all the components we need.

The only other place to check is C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents (this assumes the default installation location of SQL). If you company standard is to deploy into a different location (like drive D:) then you should check there. By default, the components' setup will place the SsisLdapSource.dll into C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents folder.
In other words, talk to your DBA and ask if the default location (on C drive) is changed during SQL deployment.

With respect to your theory on security - the error happens even before the component has a chance to talk to AD, during the load of the package.
Thanks
Alex.
Jun 11, 2013 at 11:34 AM
Hi Alex;

Yep - Everything is in the default location. ssisldapsource.dll is in the PipelineComponents folder as expected.

This is getting frustrating.

Wound uninstalling and reinstaling possibly help?

Cheers.
Ron...
Aug 7, 2013 at 10:54 AM
Hey there.
Got the same trouble on MS Windows Server 2008R2 x64 + MS SQL Server 2008R2 Developer Edition.
Any workarounds?