CozyRoc's blog

We have just released 20 brand new script components and tasks:

• AS2 Send Task – Send electronic documents (EDI, XML) using Applicability Statement 2 (AS2) specification.
• Column Encryption Transformation – Data flow column encryption.
• Conversion Transformation – Convert one or more columns to another data flow type.
• Date Format Transformation – Transform date column(s) into text representation.
• Date Source – Provide date information.
• Dynamics CRM Attributes Source – Retrieve Dynamics CRM entity attributes metadata.
• Dynamics CRM Bulk Delete Task – Dynamics CRM bulk delete task.
• Dynamics CRM Entity Source – Retrieve Dynamics CRM entity metadata.
• Dynamics CRM Execute Workflow Destination – Execute Dynamics CRM workflow.
• Dynamics CRM Incident State Destination – Update Dynamics CRM incident entity state.
• Dynamics CRM Opportunity State Destination – Update Dynamics CRM opportunity entity state.
• Dynamics CRM Team Members Destination – Establish Dynamics CRM team members relationship.
• Mail Task – Email server management functions.
• NULL Transformation – NULL value handling.
• Salesforce Fields Source – Retrieve Salesforce object fields metadata.
• Salesforce Get Deleted Source – Retrieve Salesforce object deleted records identifiers.
• Salesforce Get Updated Source – Retrieve Salesforce object updated records identifiers.
• Salesforce Lookup Transformation – Data lookup against Salesforce service.
• Salesforce Object Source – Retrieve Salesforce objects metadata.
• Trim Transformation – Trim input values.

Released COZYROC SSIS+ 1.6 SR-1 (1.6.101.0). The release includes the following major changes:

• New: Sybase Destination component for bulk load in Sybase database.
• New: Query Transformation component.
• New: Redesigned Dynamics GP adapters for better support of composite data.
• New: Redesigned QuickBooks adapters for better support of composite data.
• New: Redesigned Sage adapters for better support of composite data.
• New: Redesigned NetSuite adapters for better support of composite data.
• New: Dynamics CRM Destination component now supports CRM 2011 bulk-load API.
• Fixed: Dynamics CRM Source component was inefficient when retrieving from activities in system with millions of activity party records (Thank you, Allen).
• New: A new Smart Refresh supported in all COZYROC source data flow components.
• New: Script Component Plus now supports dynamic data flow.
• Fixed: Numerous fixes and enhancements in Data Flow Task Plus (Thank you, Rushabh).
• Fixed: Dynamic data flow with Table Difference component was not working properly with arbitrary input columns order. The dynamic data flow will now order the input columns (Thank you, Jasmin).
• Fixed: Dynamic data flow with error output directed toward flat file failed to process (Thank you, Leona).
• Fixed: Dynamics CRM Destination component failed with error “The maximum message size quota for incoming messages (2097152) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.” when processing large number of columns in CRM 2011 web services (Thank you, Dave).
• New: A new ‘Get Partition‘ action in Database Partitions Task to retrieve the partition number of existing partition.
• New: A new CallerId parameter in Dynamics CRM Connection Manager for setting user impersonation.
• New: A new ExtendedRemoteFileInfo parameter in Amazon S3 Task to retrieve extended file information: size, last modified, etc.
• New: Dynamics CRM Connection Manager now supports CRM 2011 web services ActiveDirectory authentication.
• New: A new ‘Rename remote file‘ action in Amazon S3 Task.
• New: EDI Source now setups data flow outputs only for looping segments. The previous setup logic will stay the same for existing packages for backwards compatibility.
• Fixed: Numerous fixes and enhancements in QuickBooks adapters (Thank you, Richard).
• Fixed: Dynamics AX Destination component failed with error “The supplied method arguments are not valid.” when trying to process input data containing the percent (%) symbol (Thank you, Fabio).
• Fixed: Exchange Connection Manager failed with error “Value cannot be null. Parameter name: uniqueId” when retrieving list of email (Thank you, Carsten).
• New: A new InstanceName parameter in Dynamics NAV Connection Manager for setting instance.
• New: A new ‘Create remote directory‘ action in Amazon S3 Task.
• New: A new ‘Remove remote directory‘ action in Amazon S3 Task.
• New: A new parameter RemoteFilter in Amazon S3 Task to set remote file filter expression when using ‘Receive files’, ‘Remove remote files’, ‘Get remote files list’ actions.
• New: A new parameter LocalFilter in Amazon S3 Task to set local file filter expression when using ‘Send files’ action.
• New: A new parameter Headers in Send Mail Task to set additional mail headers.
• Fixed: Dynamics CRM Source component did not handle aliased values in CRM 2011 (Thank you, Yatik).
• New: A new parameter JobIdVariable in Salesforce Destination component to store bulk job identifier in variable.
• New: A new parameter ExistingData in Excel Destination Plus component specifying what to do with existing data – Clear, Overwrite or Append. The existing Clear parameter is deprecated.
• Fixed: NetSuite Connection Manager failed with error “Your connection has timed out. Please log in again.” when trying to connect.
• New: Dynamics CRM Connection Manager now supports Office 365 deployed systems with Federated (delegated) security.

We have just released 10 brand new script components and tasks:

• Data Record To Variable Task – Extract IDataRecord field information to SSIS variables.
• Dynamics CRM Lookup Transformation – Data lookup against Dynamics CRM service.
• Dynamics CRM Route Destination – Routes Dynamics CRM entity to a queue.
• Dynamics CRM User Access Destination – Create user access permissions to Dynamics CRM entities.
• File Info List Task – Basic actions for manipulation of IFileInfo remote file list.
• Join Column Transformation – Combine multiple rows into one row.
• Mail Info List Task – Basic actions for manipulation of IMailInfo mail list.
• PayPal Transactions Source – Retrieve PayPal transactions data.
• Row Repeat Transformation – Repeats input row number of times.
• Time Zone Transformation – Convert date/time columns from time zone to another.

Released COZYROC SSIS+ 1.5 SR-4 (1.5.104.0). The release includes the following major changes:

• Fixed: SFTP Task failed with error “System.OverflowException: Arithmetic operation resulted in an overflow” when trying to process Filter expression containing time span (Thank you, Julian).
• New: SharePoint Source and SharePoint Destination components now show user-friendlier names for columns containing spaces (Thank you, Michael).
• New: EDI Source component now provides better diagnostic when column processing fails.
• Fixed: SFTP Task incorrectly cut the first characters of downloaded file name in certain cases.
• Fixed: Script Component Plus 2008 failed with error when trying to add input.
• Fixed: Excel Destination Plus component failed to process input columns of DT_TEXT and DT_NTEXT (Thank you, Marcy).
• Fixed: Parallel Loop Task now provides better logging information (Thank you, Jonathan).
• New: Parallel Loop Task can now process parallel loops defined inside For Each Loop (Thank you, Clement).
• Fixed: Data Flow Task Plus failed to process Recordset mappings after the first iteration (Thank you, Joe).
• Fixed: Amazon S3 Task failed to process files containing space in name (Thank you, Steve).
• New: Dynamics AX adapters now support 4.0 version (Thank you, Taylor).
• Fixed: Zip Task now fails if specified source selection doesn’t contain any files (Thank you, Elena).
• New: Address Parse component now provides street pre-direction, suffix and post-direction.
• New: EDI Source component now supports removal of unused outputs and columns.
• New: Template Task now supports activation of template helper classes.
• Fixed: Zip Task failed to extract from Tar package containing files larger than 8gb (Thank you, Bob).
• Fixed: ODBC Destination component failed to load properly DECIMAL type columns in Microsoft Access (Thank you, Geert).
• New: A new parameter RemotePathSeparator in SFTP Task to set the used remote path separator character.
• Fixed: Amazon S3 Task ‘Get remote files list’ action returned only the first 1000 files in a bucket (Thank you, Bob).
• Fixed: ODBC Destination failed to properly close Microsoft Access database, when no input data was provided.
• Fixed: The library now works properly on FIPS-only systems.

COZYROC is introducing the COZYROC Excel Add-In for SAS. This add-in provides the user with the ability to open and save SAS data from within Excel. The add-in does not use SAS for any processing and is 100% standalone. Highlights:

• SAS datasets are read/written in binary and do not rely on any SAS software or require the SAS datasets to be in XML, CSV, or any format other than sas7bdat.
• Compatible with Excel 2003-2010.
• Datasets are written wherever the cursor is placed so insertion at any location within Excel is possible.

More details regarding the add-in can be found here. The Add-In is available for purchase immediately.

COZYROC SSIS+ 1.6 is out of beta. We can now say this is the most ambitious release of the SSIS+ library. The new release includes 15 new adapters, 8 new connection managers and many other enhancements like support for SQL 2012, dynamic data flow transformations, Dynamics CRM 2011 web services, Salesforce bulk-load API, SharePoint 365 and many others. In total it took 22 months of development to deliver this amazing package. We would like to Thank all our loyal customers for their support and assistance!

Summary of the products available immediately for purchase:

• SSIS+ Ultimate Subscription at $399.95/Year
• SSIS+ 1.6 perpetual license at $2,399.95
• SAS® Adapters license at $2,499.95
• USPS Address Database / One time purchase at $299.95
• USPS Address Database / Monthly Subscription at $2,399.95/Year
• SSIS+ Premium Support at $499.95/Year

For more information, check the purchase page.

Summary of the new / updated components

• Support for SQL 2012.
• Salesforce Destination – Support for bulk-load.
• Data Flow Task Plus – Support for dynamic data flow transformations.
• Dynamics CRM Connection Manager – Support for CRM 2011 web services (Office 365, Federation, Live).
• Dynamics CRM Destination – Support for Upsert action.
• QuickBooks Source – Load data from Intuit QuickBooks.
• QuickBooks Destination – Store data to Intuit QuickBooks.
• QuickBooks Connection Manager – Connections with Intuit QuickBooks.
• Teradata Destination – Bulk load adapter for Teradata database.
• Sage Source – Load data from Sage SData service.
• Sage Destination – Store data to Sage SData service.
• Sage Connection Manager – Connections with Sage SData service.
• Dynamics GP Source – Load data from Microsoft Dynamics GP service.
• Dynamics GP Destination – Store data to Microsoft Dynamics GP service.
• Dynamics GP Connection Manager – Connections with Microsoft Dynamics GP service.
• OpenAir Source – Load data from OpenAir service.
• OpenAir Destination – Store data to OpenAir service.
• OpenAir Connection Manager – Connections with OpenAir service.
• Template Transformation – Data flow transformation for generating output columns based on Apache Velocity template.
• NetSuite Source – Load data from NetSuite service.
• NetSuite Destination – Store data to NetSuite service.
• NetSuite Connection Manager – Connections with NetSuite service.
• Exchange Connection Manager – Connections with Microsoft Exchange email service.
• SugarCRM Source – Load data from SugarCRM service.
• SugarCRM Destination – Store data to SugarCRM service.
• SugarCRM Connection Manager – Connections with SugarCRM service.
• Dynamics NAV Source – Load data from Dynamics NAV service.
• Dynamics NAV Destination – Store data to Dynamics NAV service.
• Dynamics NAV Connection Manager – Connections with Dynamics NAV service.

Known Issues

• Table Difference component is not working properly under SQL 2012 official. There is a bug in the SQL 2012 serialization code, which is scheduled to be fixed in CU 3.
• Parallel Loop Task is not always working properly under SQL 2012. We are still trying to determine the root cause. Please contact us, if you experience issues.
• Lookup Plus component in existing packages is incompatible with SQL 2012. Make sure you check the documentation how to prepare your packages for upgrade to SQL 2012.

It took longer than expected, but SSIS+ 1.6 Beta 2 (1.6.2.0) is now official. The major features included in the new Beta are:

• Support for SharePoint Online/Office 365.
• Support for Upsert action in Dynamics CRM Destination component.
• Support for Dynamics CRM 2011 web services. As a result, COZYROC Dynamics CRM adapters now support Office 365 and Federation deployment.

Check below all other 1.6 Beta 2 – related updates:

• New: Database Partitions Task now supports ColumnStore index.
• New: OpenAir Source component now supports custom fields in criteria.
• Fixed: SQL 2012 compatibility fixes in Script Component Plus.
• New: Teradata Destination component now handles unlimited amount of columns.
• New: A new action Upsert in Dynamics CRM Destination component.
• New: OpenAir adapters are now better at setting up the SSIS column type.
• Fixed: Many fixes and enhancements in Teradata Destination component.
• Fixed: SQL 2012 compatibility fixes in Lookup Plus component. Also review the documentation for further information.
• New: Exchange Connection Manager now supports Office 365 deployment.
• New: SharePoint adapters now support Office 365 deployment.
• New: A new parameter in NetSuite Connection Manager for specifying server address.
• New: Dynamics CRM adapters now support CRM 2011 web services.

CozyRoc is happy to announce the beta release of SSIS+ 1.6 library. The new version includes support for many additional applications. We hope this new release will make easier the decision of many organizations to adopt SQL Server Integration Services (SSIS) platform as their primary integration facility. A brief list of the new features:

• Support for SQL 2012
• Support for Salesforce bulk-load
• Support for dynamic data flow transformations
• QuickBooks adapters
• Teradata bulk-load destination component
• Sage adapters
• Dynamics GP adapters
• OpenAir adapters
• Template Transformation component
• NetSuite adapters
• Exchange Connection Manager
• SugarCRM adapters
• Dynamics NAV adapters

Your feedback is greatly appreciated.

Released CozyRoc SSIS+ 1.5 SR-3 (1.5.103.0). The release includes the following major changes:

• Fixed: Salesforce Source component failed with “Failed to process …” error when processing input columns of type binary.
• New: A new option DetectDuplicate in Dynamics CRM Destination component to enable duplicate detection rules during create or update.
• Fixed: Excel Destination Plus component failed with “Unable to determine the identity of domain.” error, when trying to create larger xlsx file (Thank you, Cory).
• Fixed: Dynamics CRM Source component now handles better FetchXML statements containing linked entities.
• Fixed: Dynamics AX Destination component failed to update string fields containing quote (’) character.
• Fixed: Table Difference component incorrectly transformed NULL value input into empty string, when input column type was DT_TEXT or DT_NTEXT (Thank you, Michael).
• Fixed: Receive Mail Task failed to extract properly Unicode text attachments (Thank you, Patrick).
• Fixed: Template Task failed to generate result from data flow parameter in certain situations (Thank you, Federico).
• Fixed: Numerous fixes and enhancements in Data Flow Task Plus (Thank you, Eric).
• New: Salesforce Source component now supports aggregate SOQL statements (Thank you, Greg).
• Fixed: Executing child package containing Parallel Loop Task with inherited variable from parent package failed with error: “The object cannot be added because another object with the same name already exists in the collection. Use a different name to resolve this error.” (Thank you, Jacob).
• Fixed: Dynamics CRM Destination component was always reporting no error code (Thank you, Eric).
• New: New options Sender and Date in Receive Mail Task to extract email sender and origination date.
• New: Dynamics AX Destination component can now update array fields.
• Fixed: Zip Task created invalid packages when compressing empty files (Thank you, Cameron).
• Fixed: Table Difference component failed with “COM object that has been separated from its underlying RCW cannot be used.” error when processing input columns of type DT_TEXT or DT_NTEXT (Thank you, Michael).
• Fixed: ODBC Destination component truncated long text columns containing more than 65535 characters (Thank you, Doug).
• New: Dynamics CRM Destination component now supports adding and removing records in CampaignActivityItem and CampaignItem entities.

One of our great customers was kind enough to share their experience building an ETL solution with CozyRoc’s technology. You can check the first post from a series here. Watch out for more great material. Enjoy!