Getting Started with COZYROC SSIS+

Because COZYROC SSIS+ is tightly integrated with SQL Server 2005, it's easy to get started using these components to streamline your business intelligence projects.  Simply enable the COZYROC SSIS+ components in your Business Intelligence Development Studio (BIDS) by following these steps:

  1. Start BIDS
  2. Open any SSIS package
  3. Open your Toolbox
  4. Right-click with your mouse in it and select Choose Items... menu. Click on SSIS Control Flow Items or SSIS Data Flow Items tab in the dialog
  5. Find out from the list which COZYROC SSIS+ components you would like to use and select the checkbox next to it

Before installing a newer version of the library, it is mandatory to uninstall older versions of the library. Running different versions of the SSIS+ library side-by-side is not supported. This is a design decision to allow your SSIS packages run with newer versions of the library without changes.

Yes, the library is designed to work side-by-side for SQL Server 2005 and 2008.

Disable UAC (User Account Control) before installation. If you still experience problems, contact support.

Make sure your proxy account has permissions to access the SSIS+ installation folder and permissions to the following registry key:

32bit - HKEY_LOCAL_MACHINE\SOFTWARE\CozyRoc\SSIS
64bit - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\CozyRoc\SSIS
  • ... not registered for use on this computer.
  • The component could not be added to the Data Flow Task. Verify that this component is properly installed.
  • The connection manager ... is not properly installed on this computer.
  • The object invoked has disconnected from its clients. Do you want to terminate them instead?
  • The connection type ... specified for connection manager ... is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type.

Try to restart your SSIS service. If this doesn't help, restart your machine. If the issue persists, please contact us.

SSIS+ execution account has to have permission to access process information. Include execution account in the Performance Counter Users Group.

The license key verification failed. To find out the specific issue, modify the job step execution command line to include "/REP V" option and run your package again. Now the execution log will include verbose information.

1. The issue is now automatically resolved in 1.4 SR-3.
2. Microsoft has issued a fix. You can download from KB982110.

Windows 7 and or Windows Server 2008 R2 backward compatiblity is broken. The issue can be fixed manually by following these steps:

  • Open regedit and go to the following registry key for SQL 2005:
    [HKEY_CLASSES_ROOT\AppID\{A72E164E-B74C-44DA-9027-D3CE2E9BA282}]
    and for SQL 2008:
    [HKEY_CLASSES_ROOT\AppID\{6A3A708F-D5F0-4265-936A-A3241C57E736}]
  • If DtsLibrary registry value starts with: C:\Program Files\... this is incorrect. Change value to start with C:\Program Files (x86)\...
  • Restart your Visual Studio and try again to open a package.

Make sure your SQL Job Agent user account has permissions to Windows temp folder (READ and WRITE). The default location is here: C:\Windows\TEMP


Using the COZYROC Database Partitions Task

Although the partitioning support in SQL 2005 is powerful, many find it difficult to use since Microsoft has not provided an easy to use point-and-click interface. The COZYROC Database Partitions task makes the the process of implementing partitions much simpler to both set up and maintain.

The task is using the connection object provided by the connection manager (AcquireConnection). It doesn't instantiate a new connection object based on a connection string. We went thru all this hassle to make sure we DO support transactions properly.

No. You have to define your partitioning function and schema and create table based on it. The task will assist you, by manipulating needed partitioning ranges and switching in/out partitions and data.

We will explain it, by describing a typical scenario in a data warehouse implementation. We are talking in particular about SSIS workflow, which process data every day. In such case you usually have your table partitioned by date. Every time your run the SSIS process, it creates a new partition for the new date. By the end of the week you will have 7 partitions. By the end of the month you will have 30 partitions. For a whole year you will run into more than 360 partitions. With such an arrangement you will easily end up with hundreds of partitions to manage and we are not even talking about hitting the internal partitions number limit in SQL 2005. This is where this task action comes handy. It will let you control the granularity of the partitions, say having a partition per week of data or partition per month of data. This will also make your task easy rolling-off and backing up your old data.

Unfortunately, the current incarnation of SSIS 2005 has incomplete connections support. ODBC is one of these connections. The workaround doesn't work because the connection returned by the manager is not of type OdbcConnection.  We are working on possible solutions to be delivered in a future version.


Using the COZYROC SSH Connection Manager

The Execute method on the task returned error code 0x80004003 (Object reference not set to an instance of an object.). The Execute method must succeed, and indicate the result using an "out" parameter.
... and also:
Failed to decrypt protected XML node "ServerPassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.

The default package protection level: EncryptSensitiveWithUserKey, doesn't work very well with SQL Agent Jobs.

If you decide to setup package encryption with EncryptAllWithPassword or EncryptSensitiveWithPassword you have to make sure you provide the password to the command line for DTEXEC application. More information how to setup DTEXEC in SQL Server Job Agent is available here and information about DTEXEC command line options is available here.

You may also find useful the following articles:
An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step
SSIS and SQL Server Agent
How to run a SQL Server 2005 Integration Services package as a SQL Server Agent job step

Error: 0xC0014005 at : The connection type "SSH" specified for connection manager "SSH Connection Manager 1" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
Starting from SSIS+ 1.3 SR-2, this error is resolved. Use the 64bit installation.

By default SSIS+ is installed under the 32bit - Program Files (x86) folder. You have two options:

  • Set Run64BitRuntime project property to False to force SSIS to execute with the 32bit runtime.
  • or ... find CozyRoc.SSISPlus.dll library and manually copy it to the following 64bit folders:
    • Microsoft SQL Server\90\DTS\Connections
    • Microsoft SQL Server\90\DTS\Tasks
    • Microsoft SQL Server\90\DTS\PipelineComponents
    • Microsoft SQL Server\90\SDK\Assemblies

PuttyGen doesn't generate proper DSA keys, which can work with the SSH Connection Manager. You may use ssh-keygen application, which generates keys recognized by SSH Connection Manager. The application is part of the OpenSSH package. Use the following command to generate 1024 bit DSA keys:
ssh-keygen.exe -b 1024 -t dsa

You may have firewall/proxy issue. Make sure you can successfully telnet your server. You should get back text starting with "SSH-2.0".

The SSH Connection Manager doesn't support directly Putty Private Key (PPK) Files. You have to export your key in OpenSSH format. You can do this in PuttyGen application:

  • Open your .PPK file in PuttyGen.
  • Select Conversions -> Export OpenSSH key

Using COZYROC Script Task Plus

The most probable reason for not seeing the script initialization parameters is that the script code failed to compile. Do the following:

  • Break the link to the script task.
  • Go to Script tab and open script IDE.
  • Open the errors pane and check if there are errors.

If you see error stating CozyRoc.SSISPlus library cannot be found, open Add Reference dialog (from script IDE menu: Project -> Add Reference ...). If the dialog displays libraries only from your ... Microsoft.NET\Framework ... folder, you most probably didn't install SQL 2005 SP2. SP2 includes support for libraries found in Microsoft SQL Server\90\SDK\Assemblies folder and SSIS+ library is deployed there by default. Manually copy CozyRoc.SSISPlus.dll to your Microsoft.NET\Framework folder and try again.

There is an issue in the SSIS framework. It verifies if there are breakpoints set in the standard Script Task and only then enters debug mode. We have found a workaround to debug your scripts. Insert a standard Script Task and set breakpoint in it. Now you will be able to debug your scripts, when you start your package in debugging mode.


Using COZYROC Jabber Task

The attempted operation is not supported for the type of object referenced (10045)

We have encountered this error during testing and we have determined the problem is related to corrupted Winsock libraries. Check this article how to repair your libraries.


Using COZYROC Oracle Destination Component

System.IO.FileNotFoundException: Could not load file or assembly 'Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

We have seen this error when using Oracle Data Provider for .NET 11.1.0.6.20 on 64bit OS, executing in 64bit mode. The provider doesn't support 64bit execution. Please execute your Oracle bulk-load packages in 32bit mode.

System.IO.FileNotFoundException: Could not load file or assembly 'Oracle.DataAccess, Version=2.111.6.20, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

We have seen this error when using Oracle Data Provider for .NET 11.1.0.6.20 on 64bit OS. By default the SSIS project is setup to use the 64bit SSIS runtime for package debugging purposes. Set Run64BitRuntime project property to False to force SSIS to execute with the 32bit runtime.

Invalid parameter binding Parameter name: ...

An input data flow column type doesn't match output Oracle column type. Use the standard Data Conversion Transformation to setup the proper column type.


Using COZYROC Salesforce

Salesforce has disabled TLS 1.0 protocol support. To resolve you have to do the following:

Make sure you have at least SP2 installed. To determine current version open SSMS and enter the following query: select @@version
The reported version should be 9.00.3042.00 or above.

The returned error message is misleading. By default the .NET framework is configured to allow maximum of 2 simultaneous connections to the same web site. The default configuration can be modified, by including under the <configuration> element in your DTExec.exe.config and DtsDebugHost.exe.config files:

The upper configuration will increase the limit to 100 simultaneous connections. Set this limit according to your needs. If you use 64bit system, you have to include the same setting in both "Program Files" and "Program Files (x86)" folders.


Using COZYROC OpenPGP Task

PGP 6.5.x is based on the older OpenPGP specification RFC 2440. To create encrypted package compatible with the older format, in the OpenPGP Task set OldFormat parameter to True.


Using COZYROC Dynamics CRM Connection

You have to make two changes:

  • Enable Anonymous Authentication on MSCRMServices\2007\SPLA on every web front in your CRM farm.
  • In COZYROC Dynamics CRM Connection Manager select deployment type Hosted.

Modify your PingFederate configuration to include the parameter -Dorg.apache.xml.security.ignoreLineBreaks=true. For further information check the documentation page here.


Using COZYROC Dynamics CRM Source

If you have recently reinstalled your Dynamics CRM server and selected different access port, you have to make additional updates on your server. For more information review KB947423.


Using COZYROC Dynamics CRM Destination

This issue is related to IIS and to the way the connections are managed. If there are too many web service calls in a short amount of time, this may cause server sockets exhaustion. To overcome this issue, enable ConnectionSharing property on the Dynamics CRM Connection Manager.

If you are using IIS7, there is a limit of the size of file you can upload. You have to increase maxAllowedContentLength property.


Using COZYROC SharePoint Connection Manager

You have to configure the client machine accessing the server to use by default Windows authentication. For more information, check the following article.