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?
Developer
Jan 24 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.
Coordinator
Jan 29 at 8:27 PM
I'll try to get an updated version of that file uploaded tonight.
Developer
Jan 29 at 9:46 PM

Mike:

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

@shannonlowder

[email removed]

Coordinator
Jan 29 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.


Coordinator
Jan 30 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'
$sqlversions=12..9
$loadedAssemblies=@{}
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)"
      break;
   }
   catch{
      write-warning "Unable to Load $assembly version $sqlversion ($assemblyName)"
  }
}
if (-not $loadedAssemblies[$assembly]){
   write-warning "$assembly not available"
}
}
Developer
Jan 31 at 12:19 PM

Mike:

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

2005: 9.0.242.0

2008: 10.0.0.0

2012: 11.0.0.0

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?

Shannon

Developer
Jan 31 at 12:35 PM

Mike:

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!

Shannon

@shannonlowder

Skype:slowder

[email removed]

[email removed]