Can't get component to run in SQL Server 2012

May 15, 2013 at 5:31 PM

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=, Culture=neutral, PublicKeyToken=758d8a4b4299146a" could not be loaded.  The exception was: Could not load file or assembly 'SsisLdapSource, Version=, 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.

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

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
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.

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
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


PS C:\windows\Microsoft.NET\assembly\GAC_32\SsisLdapSource> [System.Reflection.Assembly]::LoadFrom("C:\Windows\Microsoft

SsisLdapSource, Version=, 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.

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.
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.
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.
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?

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
Exception calling "LoadFrom" with "1" argument(s): "Could not load file or assembly 'file:///C:\Windows\Microsoft.NET\a
.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.

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.
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?

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?