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

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 please explain 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. You can read more about the issue here: Sql Server Integration Service (SSIS) Execute SQL Task ODBC Result Set Bug/Workaround. 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. For more information, please check the following article.

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

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.

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\SDK\Assemblies
    • Microsoft SQL Server\90\SDK\PipelineComponents

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 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. Please make sure you can successfully telnet your server. You should get back text starting with "SSH-2.0".

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, please 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. Please manually copy CozyRoc.SSISPlus.dll to your Microsoft.NET\Framework folder and try again.

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. Please check this article how to repair your libraries.