FAQ

Getting Started with COZYROC SSIS+

How do I start using 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.

Should I uninstall previous versions of the library?
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.

Can I run side-by-side SSIS+ library for SQL Server 2005 and 2008?
Yes, the library is designed to work side-by-side for SQL Server 2005 and 2008.

After installation I cannot find COZYROC License application
Disable UAC (User Account Control) before installation. If you still experience problems, contact support.

When I run SSIS package under SQL Job Agent with proxy account, I get error: Access Denied
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

I get one of these errors saying:

  • ... 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.

I get an error saying "Couldn't get process information from performance counter."
SSIS+ execution account has to have permission to access process information. Include execution account in the Performance Counter Users Group.

When I run SSIS package under SQL Job Agent, I get error: "Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application."
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.

After installation on 64bit Windows 7 or Windows Server 2008 R2 system, I'm no longer able to open a package in Visual Studio. I get error: Retrieving the COM class factory for component with CLSID {...} failed due to the following error: 80004005 (Microsoft.SqlServer.ManagedDTS)

1. Note: The issue is now automatically resolved in 1.4 SR-3.

2. Note: 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.

When I run SSIS package under SQL Job Agent, I get error: "Unable to generate a temporary class (result=1). error CS2001: Source file 'C:\Windows\TEMP\...' could not be found error CS2008: No inputs specified"
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

What are the advantages of 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.

Does the Database Partitions task support transactions?
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.

Can the Database Partitions task convert an existing table into partitioned table?
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.

Can you describe what 'Combine Partitions' action does?
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.

Why doesn't the Database Partitions task support the ODBC connection manager?
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

Why do I get the following error when executing a package under SQL Server Agent Job?
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

Why do I get the following error when executing a package under Win64?
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.

Note: 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

 

I want to use DSA keys with my SFTP server and I have generated a public-private keys pair with PuttyGen. When I test connection, I get an error "Auth Fail".
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

I get "invalid server's version String" error, when trying to connect SFTP server.
You may have firewall/proxy issue. Make sure you can successfully telnet your server. You should get back text starting with "SSH-2.0".

I have a key generated with PuttyGen. However when I try to use it, an error is returned saying the key is invalid.
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

Why can't I get the script samples published at COZYROC to work? I don't see any initialization parameters.
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.

Why can't I debug scripts? I set breakpoints and start the package in debugging mode, but it doesn't stop.
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

I have problem connecting to a Jabber server. I get the following error:
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

Why do I get the following error when executing a package under SQL Server Agent Job or DTEXEC?
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.

Why do I get the following error when executing a package under Business Intelligence Development Studio (BIDS)?
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.

Why do I get the following error during data flow execution?
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

Why do I get the following error when trying to connect?
UNSUPPORTED_CLIENT: TLS 1.0 has been disabled in this organization. Please use TLS 1.1 or higher when connecting to Salesforce using https

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

  • Install SSIS+ 1.6 SR-4 and later.
  • If you use SQL Server 2008R2 or earlier, you have to install a patch for .NET 3.5 provided by Microsoft. These are the last known links to the required patches:
  • Under SQL 2005, when I try to get list of Salesforce objects I get error: Object reference not set to an instance of an object.

    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.

    If I retrieve data using one Salesforce Connection, the package execution succeeds. However if I try to use 3 or more simultaneous Salesforce connections, I get error: The operation has timed out

    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:

    <configuration> 
       ...
       <system.net>
          <connectionManagement>
             <add address="*" maxconnection="100"/>
           </connectionManagement>
       </system.net>
    </configuration>

    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

    When I try to decrypt package with PGP 6.5.x, I get error: encrypted session key is bad.

    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

    When Dynamics CRM is setup in IFD (Internet Facing Deployment) authentication mode I get error: The request failed with HTTP status 401: Unauthorized. (System.Web.Services).

    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.

    When Dynamics CRM is setup with Ping Identity authentication system I get error: An error occurred when verifying security for the message.

    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

    When I try to select entity in Dynamics CRM Source component I get error: No connection could be made because the target machine actively refused it. Dynamics CRM Connection Manager Test Connection command works fine.

    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

    Creating or updating large number of records, sometimes result in error: Unable to connect to the remote server.

    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.

    When I try to upload larger files in Dynamics CRM I get error: HTTP status 404: Not Found.

    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

    When I try to use SharePoint 2010 server with mixed-mode authentication I get error: 403 forbidden

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