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. 

BizTalk 2013 R2 and EDI correlations sets.

"Failed to initialize the correlation property name"

More detail:

Inner exception: Failed to initialize the correlation property name: ISA06 namespace: http://schemas.microsoft.com/Edi/PropertySchema from message: MessageFinal271V1.

The following correlation failed:

EDI.ST01, EDI.ISA06, EDI.ISA08

This correlation set succeeded.
 
EDI.ST01, EDIOverride.ISA06, EDIOverride.ISA08

I was doing this is a BizTalk Expression:


MessageFinal277V1(EdiOverride.ISA06) =  MessageIn276V1(EDI.ISA08);
MessageFinal277V1(EdiOverride.ISA08) =  MessageIn276V1(EDI.ISA06);



This solved the problem.

The external credentials in the SSO database are more recent.

I got the following error when adding Trading Partner information in the BizTalk console.

Software I was using:

BizTalk 2013 R2
SQL 2014
 
SSO AUDIT
 Function: SetConfigInfo
 Tracking ID: 7e95dcb7-088f-4b8a-aa6a-57a3141132f0
 Client Computer: local (mmc.exe:6916)
 Client User:
 Application Name: {6d186731-31d0-45d6-a773-7a7f9f516138}
 Error Code: 0xC0002A40, The external credentials in the SSO database are more recent.

Or

SSO AUDIT
 Function: GetConfigInfo ({5DDE9C84-BF89-47FF-8F19-4862FE3F0FA8})
 Tracking ID: 51cb5a98-a60a-4481-9631-c47f6d6ea68f
 Client Computer: local (BTSNTSvc.exe:14568)
 Client User: BiztalkDev
 Application Name: {56B82475-ED73-456B-9F68-1F4599F11E01}
 Error Code: 0x80090005, Bad Data.


Local server name removed.






replacing the stored procedure in the SSO database ([dbo].[ssox_spStoreXp2]) seem to solve this problem.  I got this SQL stored procedure from MS.  See download:


Download:

https://sites.google.com/site/biztalkcsharpandsql/SSOPatch.sql?attredirects=0&d=1

IsGeneratedAck="true" in X12 999(X12_5010_999).  


This property appeared in my 999 XML from the EDISend port IsGeneratedAck="true"

I had a SOAP message that contained a EDI 270.  In the orchestration when I sent it out I got a 999 that I needed to put back in the SOAP message to return to sender.  This property appeared on the XML root node.

<ns0:X12_999_Root IsGeneratedAck="true">

<ns0:ST>

 <ST01>999</ST01>

 <ST02>0065</ST02>

 <ST03>005010X231A1</ST03>

 </ns0:ST>

<AK1>

 

<>

Hey some days you gotta cheat.  I converted the 999 XML to a string and removed the property that was giving us grief.

String2 = MQXMLRec.OuterXml;

String2 = String2.Replace("IsGeneratedAck=\"true\"","");

MQXMLRec3 = new System.Xml.XmlDocument();

MQXMLRec3.LoadXml(String2);

the IsGeneratedAck="true" Was not in any schema I could find.

This solved the problem and the XML loaded to X12_5010_999 without an issue.

Good Coding
Kent

BizTalk Pipeline Components with code projects 

Examples

Rename files

Archive Files

Zip files

To download use the link below each project.
Code my be less than pretty

Recently I have been writing some BizTalk pipeline components.  One I modified some other person's code.  I will try to make them Zipped files that you can download.

1.  KNTrename - This component will rename files.  It works on send and receive locations.  It has a filter to rename files that match the filter.  The filter can be ignored.  The project has a lot of macros and you can add your own.  KNT stands for "Kent Named This".

https://sites.google.com/site/biztalkcsharpandsql/KNT.BizTalk.Pipelines.Components.Rename.zip?attredirects=0&d=1

2. SAB - This is the archiving software from codeplex, I did not write it.  However I fixed the dates to include the zero in from the month and day "01" instead of  "1".

https://sites.google.com/site/biztalkcsharpandsql/SAB1.zip?attredirects=0&d=1

3. Stm.ZipEncoder - this will ZIP large files - I rewrote this component to use ZipArchive in .NetFramework 4.5.  It must be compiled in Visual Studio 2013, I did not try it in 2012, I know it does not work in VS 2010 because you cannot use 4.5 in 2010.  On this one be sure to add the reference to WindowsBase located at "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5\WindowsBase.dll"

https://sites.google.com/site/biztalkcsharpandsql/Stm.ZipEncoder.zip?attredirects=0&d=1

 Documentation:

https://sites.google.com/site/biztalkcsharpandsql/KNTrenamePipelineDocumentation.docx?attredirects=0&d=1
 
 https://sites.google.com/site/biztalkcsharpandsql/StmZipEncoder.docx?attredirects=0&d=1

I hope someone finds this useful.  Let me know at val1329@gmail.com

Shareing BizTalk Applications artifacts.  Especially pipeline.  When I go to redeploy I get the following error.

   

The DELETE statement conflicted with the REFERENCE constraint
"adm_ReceiveLocation_fk_Pipeline". The conflict occurred in database
"BizTalkMgmtDb", table "dbo.adm_ReceiveLocation", column
'ReceivePipelineId'.
The statement has been terminated."

To fix this you can delete and the associated BizTalk applications.  Load the MSI and then reload the other applications.   I will copy the needed artifacts , in the other application and give them a different name.