Getting Started with COZYROC SSIS+

  • You can 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 all supported versions of SQL Server.

  • 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


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.


Script Task

    • Go to Toolbox and open the Choose Toolbox Items ... dialog. Press the SSIS Control Flow Items tab and select Script Task Plus. Add Reference
    • Drag Script Task Plus into your control flow package and open the Design Script IDE.
    • Add a reference in the script project to CozyRoc.SSISPlus Add Reference
    • Include Imports CozyRoc.SqlServer.SSIS.Attributes in the script. It is also recommended that you include Imports System.Drawing.
    • Now you are ready to define your first Script Task Parameter.
  • In order for this process to work, you must have both SQL 2005 and 2008 installed side-by-side on the same machine.

    Most of the scripts published in the public script repository are for SQL 2005. However they can be easily upgraded to SQL 2008 by following these 10 steps:

    1. Create the SSIS 2005 package and insert COZYROC Script Task Plus.
    2. Load a script in the task and break the link to the script. Save the package.
    3. Go to the SSIS 2008 solution. Right-click on the solution and select 'Add Existing Package'.
    4. Select the package you have created in step 1. SSIS 2008 will do an automatic upgrade of the package and show some warnings.
    5. Open the imported package.
    6. Double-click on the COZYROC Script Task Plus and enter the script IDE.
    7. Right-click on the script project and select Properties.
    8. Go to the References tab and add a reference to CozyRoc.SSISPlus.2008 from the "Microsoft SQL Server\100\SDK\Assemblies" folder.
    9. Save and close the project settings. Make sure the script compiles.
    10. Close the script IDE and export the script.
    • Category and description are provided with standard System.ComponentModel attributes Category and Description.
    • To sort parameters according to a custom defined order, specify the SortProperties attribute on the parameters class.
    • To filter parameters based on the current state, specify the FilterProperties attribute on the parameters class. Specify the method name as attribute parameter. The specified method is dynamically called every time the class parameters are refreshed.

    You may also check the sample script Http Upload/Download Task

    • To validate parameters, specify ValidateProperties attribute on the parameters class. Specify method name as attribute parameter. The specified method will be called when the user presses OK in the dialog.

    You may also check the sample script Send Mail Task

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


OpenPGP Task

  • Creating a signed and encrypted package is a 2-step process:

    • Insert the OpenPGP Task with the action "Create file signature". Set IncludeData option set to true.
    • Insert OpenPGP Task with the action "Encrypt file". As a source file, select the target file created in the "Create file signature" step. Set the IsSourceSigned option to true.
  • 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.


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.


SSH Connection

  • 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

SharePoint Connection

  • We recommend using the Fiddler tracing application. Make sure you enable HTTPS tracing option before you start your trace.

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


Dynamics CRM Connection

  • We recommend using the Fiddler tracing application. Make sure you enable HTTPS tracing option before you start your trace.

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


Salesforce Source

  • The Object, ObjectFilter or Statement parameters are available for setup in the data flow properties list. Follow these steps to set up an expression:

    • Right-click on the data flow canvas and select Properties from the menu.
    • Scroll-down and find a property named like [Salesforce Source].[Statement]. This is the property containing the statement.
    • Scroll-down and find the Expressions property. Set up an expression to modify the statement dynamically.
  • The Salesforce service only returns fields containing data. If there is no data for a specific field, no data is returned. Therefore, it is impossible to set up the appropriate field and field type. There are 3 options to include the missing columns:

    Option 1
    This option will work fine for SOQL statements without relationships. For statements with relationships, see the other options below.

    1. Set the **InputType** to Object. Select the object from which you want to retrieve data.
    2. Go to the 'Column Mappings' tab and press 'Refresh' to reload the object's columns.
    3. Set the **InputType** to ‘Statement’ and specify your statement. **Do not refresh** the columns in the 'Column Mappings' tab.
    4. If you want to remove columns you don't need, go to the 'Column Mappings' tab and remove the mappings you don't need.

    Option 2

    1. Set up a temporary Salesforce record and the required relationship data.
    2. Open the 'Salesforce Source' component setup dialog and specify the SOQL statement retrieving the temporary record data.
    3. Refresh the 'Column Mappings' tab. All needed column metadata should now be included.
    4. Go back to the Salesforce setup window and remove the temporary record and all other unneeded data.

    Option 3
    This option requires manual definition of the missing columns.

    1. Go to the 'Input and Output Properties' tab.
    2. Go to 'Salesforce Source Output' -> 'External Columns'.
    3. Press 'Add Column'.
    4. Specify the column name and type. Make sure the name is specified properly with the appropriate structured definition.

  • Uploaded files are located in the Attachment object. Use the standard "Export Column" transformation to export the Body field content into a file.

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


Dynamics CRM Source

  • The source entity or query are available in the data flow properties list. Follow these steps to set up an expression:

    • Right-click on the data flow canvas and select the Properties menu.
    • Scroll down until you find the property named [Dynamics CRM Source].[FetchXML] or [Dynamics CRM Source].[Entity]. This is the property containing the query.
    • Scroll down and find the Expressions property. Set up an expression to dynamically populate the property containing the query.
  • 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.


Oracle Destination

  • The destination table is available in the data flow properties list. Follow these steps to set up an expression:

    • Right-click on the data flow canvas and select the Properties menu.
    • Scroll down and find the property named like [Oracle Destination].[DestinationTable]. This is the property containing the destination table.
    • Scroll down and find the Expressions property. Set up an expression to modify the statement dynamically.
  • 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.


Salesforce Connection

  • We recommend using the Fiddler tracing application. Make sure you enable HTTPS tracing option before you start your trace.

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

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


Dynamics CRM Destination

  • The destination entity is available in the data flow properties list. Follow these steps to set up an expression:

    • Right-click on the data flow canvas and select the Properties menu.
    • Scroll down and find the property named [Dynamics CRM Destination].[DestinationEntity]. This is the property containing the destination entity.
    • Scroll down and find the Expressions property. Set up an expression to modify the destination table dynamically.
  • The identifier of the created entity record is provided in the component's error output, in the Id column. Connect the error output to a destination where you want to store the identifiers. See the demo video for details about setting up a destination in which to write the new IDs.

  • The activity party list can be set up either with the party's email or using a party-unique identifier. Multiple party elements are separated with a semicolon (;):

    • To use email addresses to identify the parties: email1;email2;...
    • To use the GUIDs assigned to the records by Dynamics CRM: <entity name>:{<guid1>};<entity name>:{<guid2>};...
    See the demo video for details about setting up an activity party list.
  • The component's Error Output is used for providing both error information and the record identifier information for new records. Check the ErrorCode column:

    ErrorCode Description
    -1 Not an error record.
    >0 Error record. Check the ErrorDescription column for more details.

    You can set up the standard Conditional Split component to filter non-error records.

    See the demo video for details about setting up a destination in which to write errors.
  • The attachments are stored in the annotation entity. Use the standard Import Column transformation to load the file content into memory and then direct into documentbody attribute.

    See the demo video for details.
  • 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.


File Transfer Task

  • If you use this task to stream data, set the RetainSameConnection parameter to TRUE on the connection manager. Otherwise, the connection will be closed before all the data is transferred.


License

  • The instructions below are for SSIS+ 1.3 SR-4 and newer. The license key deployment process has been simplified. Make sure you download and install the latest version.

    The SSIS+ library must be licensed on every production server. The library installation contains the license key deployment application. The application is located in the Start menu:

    Start -> All Programs -> CozyRoc SSIS+ -> License
    

    IMPORTANT: The application must be run on your target production server, where the SSIS+ library is installed.

    Make sure you execute the License application in administrative mode (Run As ...). When you run the License application, it will show your machine identifier and two different options for requesting your license key, as follows:

    • Licensee - depending on what type of purchase you made, provide one of the following:
      • Subscription Number - if you purchased an SSIS+ Ultimate Subscription.
      • Receipt Number - if you purchased an SSIS+ Perpetual License.
      • Custom licensee identifier - provided by the COZYROC staff.
    • Email and Payment - use the email you used to make the purchase and the amount you paid.

    After you enter the licensee, press the button on the right to request your license key. If the license key is deployed successfully the application will change accordingly, showing the licensed version and expiration date (if you purchased the Utlimate Subscription). If there are issues, the application will show errors on the bottom.

    If your target server is running behind a Firewall or cannot access the COZYROC License Server for some other reason, then follow the instructions described in the section entitled My server sits behind a firewall and I'm unable to contact the COZYROC license server. How can I get my license key?

  • The SSIS+ Ultimate Subscription has time-limited license keys. After the expiration period has passed, the library will try to automatically contact the COZYROC License Server and retrieve the license key for the next subscription period. No action is required on your part to renew your license if your server has access to the COZYROC License Server at http://www.cozyroc.com and your subscription fee has been paid for the next year. If you would like to take precautionary measures to ensure your service is not interrupted, take the following steps:

    • Verify that your SSIS server and the SQL Job Agent have permission to communicate on port 80 with the COZYROC License Server at: http://www.cozyroc.com
    • Execute an SSIS package under the SQL Job Agent to test its ability to communicate with the License Server. To accomplish this, follow these steps:
      1. Go to the COZYROC registry key.
      2. There is a DWORD value called ContactLicenseServer. Set this special flag temporarily to 1, to always contact the COZYROC License Server when executing SSIS packages
      3. Run one of your production SSIS packages under the SQL Job Agent manually. After it finishes executing, inspect your SQL Job Agent execution log. If you don't see errors or warnings, then everything is fine and your subscription renewal should complete successfully.
      4. Go to the registry and reset the ContactLicenseServer value to back to 0.

    If your production server is running behind a Firewall or cannot access the COZYROC License Server for some other reason, then follow the instructions described in the section entitled My server sits behind a firewall and I'm unable to contact the COZYROC license server. How can I get my license key?

  • Make sure your SQL Job Agent user account has the following permissions:

    • COZYROC SSIS+ installation folder (READ and WRITE).
    • Windows temp folder (READ and WRITE). The default location is here: C:\Windows\TEMP
    • Permission to access COZYROC registry key.
    • Permission to access SQL Server registry key.
  • Yes. To request a trial license key, enter "TRIAL" in the Licensee input box in the License application.

  • It depends. For a 2-node cluster, running in the Active/Active configuration, two (2) production licenses are required. If your cluster node is running in the Active/Passive configuration, only one (1) license is required.

  • Make sure the SQL Server registry key is present. If it is not present, your SQL Server is not properly licensed or installed. To resolve this error, correct your SQL Server installation or get a valid SQL Server license.

  • Use the license request page to get your license key. After you download the license key file (License.dat), deploy it using the License application (1.4). If you are an SSIS+ 1.3 user, you must manually deploy it in the SSIS+ installation folder.

    • For 32bit, the default installation folder:
    \\Program Files\CozyRoc\SSIS
    
    • For 64bit, the default installation folder:
    \\Program Files (x86)\CozyRoc\SSIS