SSISDB Integration Services Catalog

Parameters in SSIS.   In the past for SSIS we used the XML files.   We pointed to these by setting up a system variable. 

I used Integration Services Catalog because of security issues we had across two servers.   This process no longer used the System varibles and XML files.

I right click on:

Integration Service Catalog-My container name-projects-Packages container-my package container name-Packages-Name Of my DTSX

I choose Configure, a box opens.  There are two tabs in the center of the box.

1. Parameters
2.  Connection Manager

I choose connection manager.   There were 2 boxes.

1. Container Name
2. Properties

I modified the values one at a time by hitting the (...) the right of each one.   I copied the value from the use default value from Package.   I click on the "Edit Value", replace the corrected string
I hit ok to save these values twice. 

Now my SSIS was pointing at the correct server!

SQL SSISDB configuration

TITLE: Microsoft SQL Server Management Studio
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Management.IntegrationServices)

------------------------------
ADDITIONAL INFORMATION:

String or binary data would be truncated.
Invalid object name 'sys.trusted_assemblies'. (Microsoft SQL Server, Error: 8152)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.4100&EvtSrc=MSSQLServer&EvtID=8152&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Google or bing returned nothing of value.

I tried to create the Catalog in Sql service manager, from a remote computer.   I logged onto the server SQL was running on.   I ran it as Administrator.  I ran the same version of Sql server manager as the data base.   In this case 2012.   the version I tried remote was 2014.   This allowed me to create the SSISDB for SSIS.

n/software PGP , or Nsoftware PGP struggles

n/software PGP  , or Nsoftware  PGP struggles.  This is good software!

The following worked:

Putting this value in the "Other" box:

PublicKeyRingFile=C:\users\Biztalkdev\gpg\Healthequity.asc

Where Healthequity.asc is an encryption file in a file.  Notice the ".asc".   When i received it the file extention was ".pgp". 

I put the following in the PGPParams box:

homedir=C:\users\Biztalkdev\gpg%0d%0arecipient-userid=correlation@healthequity.com

Where homedir is the location of the encryption file.  I am not sure this is needed.
Where the "%0d%0a" is a carrige return the must separate parameters.
Where recipient-userid=correlation@healthequity.com,  this is the recipient-userid associated this this file.   I found this by loading the pgp file into semanatec pgp software.

Note:  putting a 1 in the PGPProviderType means the PGPProvider is ignored.

This encrypted the file.

Batching orchestration is Suspended.  It needs to be terminate administratively.

To fix this go to the main console and select Running Service Instance.

Hit run query.   You should see one of the orchestrations suspended.  Right click and choose start.

slash-n(/n) PGP pipeline notes

These are the notes I received from our deployment person.  I thought they might help someone else someday

<>

As good as the SFTP adapter is, I expected these PGP pipeline components to be pretty slick. They are not.

What works: 

1)      Simple passphrases – no spaces, no “special” characters.

2)      Keyringfiles in the “homedir” folder (wherever you care to specify that)

3)      Keyring file names cannot contain spaces.

4)      Passphrase has to be passed in PGPParams, but Keyrings should (if other than default values) should be in the ‘Other’ field.

5)      Multiple elements may be passed in a line, but they have to be separated by %0d%0a (carriage return/line feed)

6)      Outbound files require ‘recipient-userid=’ followed by either an email address associated with the recipient’s key, or the embedded key name.

7)      Inbound just requires the secretkeyringfile and the passphrase (passphrase belongs in PGPParams, keyringfile in ‘Other’

The largest failing in this product is the lack of support for arbitrary passphrases. (though perhaps, if we used SSO to store them, arbitrary passphrases would work OK – I don’t feel like testing that just now.)

<>

Misspelled BizTalk 64 bit configuration file cannot find artifacts in GAC

Some time it is the simple things in life.  I misspelled the name of the 64 bit BizTalk configuration file.  It should be "BTSNTSvc64.exe.config".  I was able to start all the host instances.  Essentially there was no configuration found.   The symptom was interesting.   BizTalk could find nothing in the GAC.

This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

Loading 3rd party adapters into BizTalk 2013 R2.   I received the following error every time the adapter tried to run.

This issue is associated with the dll not using the same version of  .Net framework as BizTalk. 

  

<>

The Messaging Engine failed to create the receive adapter "BizCryptoSFTP".

  InboundAssemblyPath: "C:\Program Files (x86)\EldoS\BizCrypto.BizTalk\Assemblies\NET_45\BizCrypto.BizTalk.Adapters.SFTP.dll"

  InboundTypeName: "BizCrypto.BizTalk.Adapters.SFTP.ReceiveAdapter"

  Exception Details: "Could not load file or assembly 'file:///C:\Program Files (x86)\EldoS\BizCrypto.BizTalk\Assemblies\NET_45\BizCrypto.BizTalk.Adapters.SFTP.dll'

or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded."

 <>

ANSWER SOLUTION:

 The host must have 32 bit checked.  This is a check box on the HOST.  To select it you must remove all the Host instance using this host(should be one).   Reload the hosts into the adapter.   I did find a MSDN blog that said that 64 bit will not work with some adapters.