I cannot load the SQL Server module.

Jun 18, 2013 at 3:11 PM
I cannot load the SQL Server module. I have SQL Server 2012 and installed Powershell 3.0.

Has anyone else had this issue?
Jan 24, 2014 at 8:43 PM
The problem has to do with how the module is loaded. Look at the top of the SQLServer.psm1 file. You'll see where it's loading the assembles for SQL Server. It tries to load the version 10, and 9...but never 11(IE SQL Server 2012). That's one of the things I want to tackle in a code change to SQL PSX.

Which way makes more sense to you, should I check from newest to oldest version, using try catches, or make the end user pass the version of the assembly they want to use.

Personally I say use the latest version, but that might get some users into trouble if they use the ssis features, since 2012 features could cause their packages to be updated to 2012 packages without prompting the user.
Jan 29, 2014 at 8:27 PM
I'll try to get an updated version of that file uploaded tonight.
Jan 29, 2014 at 9:46 PM


I exchanged an email with Chad Miller, and I expressed interest in helping contribute to the project. I’d be happy to help improve the code. In fact, I’ve been playing around with some code that lets me dig into the data-flow tasks in a package. It requires tapping into the .net controls, but it’s pretty neat. I’m not sure if that’s something you’d want to include in the SQLPSX core, but it’s interesting.

Let me know how I can help!

Shannon Lowder


[email removed]

Jan 29, 2014 at 9:51 PM
I can definitely add you as a contributor to the project. I'd also be interested in seeing what you're doing with data-flow.

Jan 30, 2014 at 2:47 PM
You've been added. Hopefully you got an email about it.

The code I've been toying around with looks like this (in the beginning of the SQLServer.psm1 file). I wrote it this way so that we don't have to keep adding more try/catch/if/etc. structure for each version of SQL Server.

$assemblies = 'Microsoft.SqlServer.ConnectionInfo','Microsoft.SqlServer.Smo','Microsoft.SqlServer.SMOExtended'
foreach($assembly in $assemblies){
foreach($sqlversion in $sqlversions){
   try {
      $AssemblyName="$assembly, Version=$sqlversion.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
      $loadedassemblies[$assembly]=add-type -AssemblyName $assemblyName -EA Stop -PassThru
      write-verbose "Loaded $assembly version $sqlversion ($assemblyName)"
      write-warning "Unable to Load $assembly version $sqlversion ($assemblyName)"
if (-not $loadedAssemblies[$assembly]){
   write-warning "$assembly not available"
Jan 31, 2014 at 12:19 PM


I did get the invitation, thanks! The change looks good, Based on that change, maybe we could implement a similar change for the ssis includes, except I think the versions there would be




But there are other versions 9.0.3042.0 for example. Maybe for SSIS we could to use a get-childitem –recurse to look for versions loaded on the client machine and then load the latest version first in case of multiple versions loaded.

And if we build anything on top of 2012 extensions, we can throw errors if they happen to load a version prior to 2012.

What do you think?

Should I push any code changes straight to the repo, or should I branch to make my code changes?


Jan 31, 2014 at 12:35 PM


Here’s what I’m doing with the data flow.

Load a package through get-ISPackage, then grab the executable.innerObject of a data flow task. Then you can create a wrapper through marshalling. I’m not exactly sure what this is doing, but it works and gives me access to the innards of the data flow.

$dataFlow = [System.Runtime.InteropServices.Marshal]::CreateWrapperOfType($innerExecutable.InnerObject, [Microsoft.SQLServer.DTS.pipeline.Wrapper.MainPipeClass])

Once I have this, I can loop through the components of the DFL task.

foreach($component in $dataFlow.componentMetaDataCollection) {


Send me an offline message to [email removed] and I will send you an invite to my private POSH repository so you can check out my work on a testing suite for SSIS packages.

I’m completely open to feedback on how I’m doing this. I’d like to release this test suite to the community as something they could use to test their packages before release, so they know the packages will be good to go in production!

Maybe we should skype some time to talk about your goals for SQLPSX and see where I can best contribute!




[email removed]

[email removed]

Sep 3, 2014 at 12:41 AM
I too am having trouble loading this PowerShell module (I'm running Powershell 5). Examining the GAC (%windir%\assembly) I see that I've got versions 11 and 12 of the relevant assemblies installed. In SQLServer.psm1, the fallback when loaing version 10 fails tries to load 'Microsoft.SqlServer.ConnectionInfo" without specifying a version. This generates a surprising error:
add-type : Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=, Culture=neutral, 
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
At C:\Users\bharris\Documents\WindowsPowerShell\Modules\SQLServer\SQLServer.psm1:17 char:8
+ catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}

Depending on the GAC to loading these SMO assembly from the GAC seems too fragile; it's DLL hell all over again. Id suggest a better way to do this would be to include the needed assemblies in the distribution of this module, that's why the SMO assemblies are redistributable...