CozyRocGetting 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.
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:
[HKEY_LOCAL_MACHINE\SOFTWARE\CozyRoc\SSIS]
I get one of these errors saying:
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.
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
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. Please 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, 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.
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. Please 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 Source Component
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.
