FAQ

The initialization and user interface for a data flow is defined by implementing properties on the script EntryPoint class. The documentation and samples of Script Task Plus for defining parameters is relevant to the Data Flow Task Plus initialization setup, so we will not repeat it here. Here you will find a description of the DataFlowSetting .NET attribute, used to apply values to specific data flow component parameters.

• Go to the Toolbox and open the Choose Toolbox Items ... dialog. Press the SSIS Control Flow Items tab and select Data Flow Task Plus.
• Drag Data Flow Task Plus into your control flow package and open the Design Script IDE.
• Add a reference in the script project to CozyRoc.SSISPlus
• 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 Data Flow Task Plus Parameter.

The DataFlowSetting .NET attribute defines a list of references to different component properties in the SSIS data flow. It defines the link between the initialization parameter and a specific SSIS data flow component parameter. You can apply one initialization parameter to multiple data flow components and properties. Each reference is a text string with separate sections. Each section is separated by a "." dot. Every reference starts with a component, followed by a "keyword" specifying the parameter type. Here is a list of sample DataFlowSetting .NET attribute reference types which are supported currently by the Data Flow Task Plus component:

• DataReader Source.Property.PreCompile - set property PreCompile.
• DataReader Source.Connection.IDbConnection - set connection IDbConnection.
• DataReader Source.Column.Input.DataReader Input.DateKey - set input column DateKey in DataReader Input input.
• DataReader Source.Column.Output.DataReader Output.DateKey - set output column DateKey in DataReader Output output.
• DataReader Source.Column.OutputExternal.DataReader Output.DateKey - set external output column DateKey in DataReader Output output.

where DataReader Source is a reference to a data flow component.

In SSIS, different data flow elements like components, properties, connections, and columns can be referenced either by name or ID. The DataFlowSetting .NET attribute reference supports both.

Sample script to set up the CommandTimeout property of the DataReader Source component.

Sample script to set up an IDbConnection connection of the DataReader Source component.

Sample script to set up a column with ID 3018 in DataReader Output output of DataReader Source component.

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.

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

• Right-click on data flow canvas and select the Properties menu.
• Scroll down and find the property named like [DB2 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.

You can use Dictionary Destination script component to insert items in cache.

Starting from 1.4 SR-3 the data flow can be completely dynamic - the need to have one common column across different iteration sets is no longer required. To have completely dynamic components, you have to remove all statically defined columns (both in the components and related connection managers). However there are components, which require at least one column to pass successfully validation. We have implemented a workaround, which is based on thunk (not used) column. If you encounter a component requiring at least one column, define a column named THUNK_COLUMN. This column will be maintained and ignored at runtime by the Data Flow Task Plus.

• To setup custom routine in the data flow script for additional setup of dynamically added data flow column, attribute your script main class with DataFlowColumnAdded attribute. Set as parameter the name of the script method. Check below the required handler method layout.

<DataFlowColumnAdded("OnColumnAdded")> _
Public Class ScriptMain
    Inherits UserComponent
...
    Public Sub OnColumnAdded(ByVal component As IDTSComponentMetaData90, ByVal isInput As Boolean, ByVal colName As String)
...

Trying to process input Unicode flat file fails with weird error:

The data type for "Flat File Source.Outputs[Flat File Source Error Output].Columns[Flat File Source Error Output Column]" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

We believe there is a validation bug in the Flat File Source component. The solution is to set ValidateExternalMetadata=True on the Flat File Source component and set DelayValidation=True on the Data Flow Task Plus.

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 [Dynamics AX Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up an expression to modify the destination table dynamically.

The input tables 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 and find the property named like [Dynamics AX Source].[InputTables]. This is the property containing the input tables. Every table is separated with a comma and line feed (,\n)
• Scroll down and find the Expressions property. Set up an expression to modify the input tables dynamically.

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.

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>};...

The component's Error Output is used for providing both error information and the record identifier information for new records. Check the ErrorCode column:

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

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.

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.

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.

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

When working with composite records, one input must be set up which contains the main objects (parents) and a separate input must be set up which contains the related composite objects (children). The order of the parent records and the children records is used to match children to parents. As each parent record is processed from one input, the associated children records are processed from the other input.

To mark the end of children associated with a parent, a blank row (all NULLs) much be present in the input. The processing of a parent record is only completed when a blank row is encountered at the end of the children for that parent. The final record in the children input must be a blank row in order to complete the processing. You can use Query transform to prepare child data with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The source object and criteria are available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [Dynamics GP Source].[ObjectCriteriaXML]. This is the property containing the criteria.
• Scroll-down and find Expressions property. Setup expression to modify the criteria dynamically.

The composite object output contains not only properties from the composite record, but also the parent record. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with blank row (all NULLs).

For additional information about modelling of composite records in SSIS+, please refer to this article.

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

The destination page 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 [Dynamics NAV Destination].[DestinationPage]. This is the property containing the destination page.
• Scroll down and find the Expressions property. Set up an expression to modify the destination page dynamically.

The component's Error Output is used for providing both error information and the record identifier information for new records. Check the ErrorCode column:

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

The source page or filter 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 [Dynamics NAV Source].[PageFilter]. This is the property containing the page filter.
• Scroll down and find the Expressions property. Set up an expression to modify the page filter dynamically.

To exclude an element from the data flow, prefix the xmltag attribute value with the IGNORE keyword like this:

<medi:field xmltag="IGNORE ISA01 I01 Authorization Information Qualifier" />

To start using the new EDI Connnection Manager you would need to create one and initialize it with the same XML EDI Configuration file. The old EDI Source needs to be recreated and reconfigured to use the connection manager.

The destination worksheet, start columns and rows 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 and find the property named like [Excel Destination Plus].[Worksheet]. This is the property containing the destination worksheet.
• Scroll down and find the property named like [Excel Destination Plus].[StartColumn] or [Excel Destination Plus].[StartRow]. These are the properties containing the destination column and row, respectively.
• Scroll down and find the Expressions property. Set up an expression to modify the worksheet or region dynamically.

The source worksheet and region 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 and find the property named like [Excel Source Plus].[Worksheet]. This is the property containing the worksheet.
• Scroll down and find the property named like [Excel Source Plus].[Region]. This is the property containing the source region. The property is a string combination containing values for start column, start row, width and height. Each value is separated with a comma (,).
• Scroll down and find the Expressions property. Set up an expression to modify the worksheet or region dynamically.

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

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.

Files on a remote server may be moved by using the Rename remote files Action. Simply include the name of the directory where you want to move the file(s) in the path using the NewName parameter.

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.

Files on a remote server may be moved by using the Rename remote files Action. Simply include the name of the directory where you want to move the file(s) in the path using the NewName parameter.

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 [Informix Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up your expression to modify the statement dynamically.

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.

In some cases, the Kafka customer fails to access the CA store. In this case, there are two options available:
1. Uncheck Check for certificate revocation checkbox in General.
2. Download cacert.pem. Then go to the Advanced tab and in Additional Options add ssl.keystore.location parameter specifying the path to cacert.pem (e.g. ssl.keystore.location=\cacert.pem).

The destination object 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 [LDAP Destination].[Object]. This is the property containing the destination object.
• Scroll down and find the Expressions property. Set up an expression to modify the destination object dynamically.

The source “object” and “filter” parameters 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 and find the property named like [LDAP Source].[Object]. This is the property containing the object.
• Scroll down and find the Expressions property. Set up an expression to modify the object dynamically.

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

The destination module 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 [SugarCRM Destination].[DestinationModule]. This is the property containing the destination module.
• Scroll down and find the Expressions property. Set up an expression to modify the destination module dynamically.

The component's Error Output is used for providing both error information and the record identifier information. Check the ErrorCode column:

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

To update an existing module record follow these steps:

• Map the columns you want updated.
• Map the module Id column to the updated record identifier.

The component cannot delete module records. However you can mark specific records as deleted. Follow these steps:

• Map the module Id column to the deleted record identifier.
• Set the module deleted column to True.

The source “module”, “query” and “order by” parameters 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 and find the property named like [SugarCRM Source].[Module]. This is the property containing the module.
• Scroll down and find the Expressions property. Set up an expression to modify the module dynamically.

You can match multiple columns using the Lookup Plus component. See the steps below for one possible solution:

1. Create a new data flow. This data flow will build a lookup reference dictionary and store it in a package variable.
2. Insert a data flow source component.
3. Insert the Checksum Transform script after the component from step 2 to create a unique CRC32 value for the match input columns.
4. Insert the Dictionary Destination script after the component from step 3. For the key, select the input column containing the CRC32 value. For the value, specify the appropriate reference column. Store the resulting dictionary object in a package variable.
5. Create another data flow, which will do the lookup and matching.
6. Insert the data flow source with the data that is to be matched.
7. Insert the Checksum Transform script after the component from step 6 to create a unique CRC32 value for the required input columns.
8. Insert the Lookup Plus component. As the key, specify the input column containing the CRC32 value. In the reference dialog, select the "From Variable" option. Set it to the variable you created in step 4.

Existing packages containing Lookup Plus component and designed with SSIS+ 1.5 or older version are incompatible with SQL 2012. To have successful upgrade follow these steps:

• Install SSIS+ 1.6 or newer on a machine where you have existing packages designed with SQL 2005 or 2008.
• Open the existing package containing Lookup Plus component and re-save.

The saved package will now contain SQL 2012 compatible data and the package is ready for upgrade.

A composite record models an array item in a MongoDB document. The nested document in such an array usually have the same set of fields, thus they are modeled by a separate input (named after the array element path).

For each parent record (e.g. a MongoDB document), the composite input would expect to receive a set of rows (with a row per array item). Those set of rows must be separated with blank row (all NULLs) to denote the end of the array items. The processing of the parent record will not finish until all composite record children have received blank row to indicate completion. Tip: You can use Query transform to prepare parent-child data with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The component's Error Output is used for providing both error information and information about the document ObjectId identifier. Check the ErrorCode column:

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

For each array in the document, regardless of the depth of nesting, there will be created a composite output that enables retrieving the field values of its items. This output contains not only the fields of the array items, but also the fields from all parent record(s).

During runtime, the output will be populated with sets of rows, one set for each parent record. Each set of rows is separated with a blank row (all NULLs). Those blank rows are used for recreating the parent-child relationships in all SSIS+ components that support composite inputs (e.g. MongoDB Destination ). Tip: If you don't need the blank rows, you can set up the standard Conditional Split component to filter them out.

For additional information about modelling of composite records in SSIS+, please refer to this article.

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

The composite record input accepts sets of rows, one set for each parent record. Each set of rows must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child records with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The component's Error Output is used for providing both error information and the processed record identifier. Check the ErrorCode column:

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

The input for list reference field is a string containing both list type identifier and record internal identifier. The input format is:

<type id>;<internal id>

Multiple list references are separated with semicolon (;).

The composite record output contains not only fields from the composite record, but also the parent records. The output will be populated with sets of rows, one set for each parent record. Each set of rows is separated with blank row (all NULLs).

For additional information about modelling of composite records in SSIS+, please refer to this article.

The source record or statement are available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [NetSuite Source].[SearchXML]. This is the property containing the record statement.
• Scroll-down and find Expressions property. Setup expression to modify the record statement dynamically.

The sales order is found under the general transactions record. You have to specify SearchXml statement to retrieve them. For sales order specifically try the following statement:

<TransactionSearchBasic>
  <type operator="anyOf">
    <searchValue>_salesOrder</searchValue>
  </type>
</TransactionSearchBasic>

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 [ODBC Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up an expression to modify the table dynamically.

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 Ultimate 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?. Please also review Server with Internet access explanation video.

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. Please also review 30-days trial key explanation video.

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). Please also review Server without Internet access explanation video.

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

The license key transfer is a manual process which requires assistance from COZYROC. To transfer a license follow these steps:

1. Send a copy of License.dat file from the decommissioned machine to COZYROC support team. Include description why do you want to transfer the license key.
2. Open the License application on the decommissioned machine and press the Remove button.
3. COZYROC support team will review the information you have sent and make the necessary adjustments to your license record. After this you will receive notification if you can proceed to acquire license key for your new machine.

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

The destination type is available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [OpenAir Destination].[DestinationType]. This is the property containing the destination type.
• Scroll-down and find Expressions property. Setup expression to modify the destination type dynamically.

The input type, filter and method criteria are available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [OpenAir Source].[InputType]. This is the property containing the record.
• Scroll-down and find Expressions property. Setup expression to modify the input type dynamically.

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.

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.

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.

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.

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.

To iterate and modify the variables used by a package, follow these steps:

1. Create a script task.
2. Reference CozyRoc.SSISPlus library.
3. In the script get reference to a package connection manager.
4. Get connection manager InnerObject property and cast it to PackageConnectionManager.
5. Get Variables property, which is generic Dictionary type. It contains variable name to variable value pairs. Modify this collection, to setup package connection variables.

Check the sample script below demonstrating how to access package connection variables.

Imports System.Collections.Generic
Imports CozyRoc.SqlServer.SSIS
...
Dim manager As ConnectionManager = Dts.Connections("YourPackageConnection")
Dim packageCM As PackageConnectionManager = CType(manager.InnerObject, PackageConnectionManager)
Dim packageVars As Dictionary(Of String, String) = packageCM.Variables

Follow the steps below to set up a dynamic iteration-variable dependent connection:

1. Create a variable with the package scope and use this variable in the connection manager to construct your expression.
2. Create a new variable with the same name and type, but with the loop scope.

The Parallel Loop Task will use the variable with the loop scope to evaluate the connection manager expression and build the dynamic connection.

It is mandatory to use an iteration variable scoped inside the loop container. Failure to do this will result in errors and/or concurrency issues.

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

The token setup process is organized as a wizard consisting of two steps

1. Step: Authorize COZYROC SSIS+ to connect to specific QuickBooks instance. If the token retrieve succeeds, the second wizard step will have the access token, secret and realm automatically populated in the second step.
2. Step: If the automatic token retrieve fails, you can manually specify the access token, secret and realm. If all required information is populated, you can specify the token file name and password to save it.

Tips for successful access token retrieve

• Disable the Windows Firewall.
• Explicitly Run As: Admin Visual Studio.
• Try running the token retrieve from a different machine.
• Manually get the access token using this Intuit page. Follow these steps:
  

  1. Enter the following parameters:

     • Consumer Key: qyprdCc0eW0p56eWWmzUBtc6rcvKfU
     • Consumer Secret: uiLUsLvwyPhStetxutjUrDcTFnmVCi5Tz0Ne6Yhz

     Press 'Get Request Token using Key and Secret' button.

  2. Press 'Authorize Request Token' and authorize access to the QuickBooks Online instance.
  3. Copy-and-paste the retrieved access token and secret in the second step of the token setup wizard.

To get connection key to your QuickBooks Online data, follow the steps below:

• Go to the following address: https://login.quickbooks.com/j/qbn/sdkapp/confirm?serviceid=2004&appid=APP_ID
  Make sure to replace APP_ID with the application identifier you used above.
• A wizard will appear, which will guide you thru the setup process. Grant the access rights you will need.
• IMPORTANT: Right before the end, the wizard will ask you for Login Security. Make sure you turn it off. Otherwise your SSIS package will not be able to execute in automated process.
• A connection key will be generated at the end of the setup process. Copy-and-paste the connection key in the QuickBooks Connection Manager setup dialog.

The component's Error Output is used for providing both error information and the record identifier of the processed records. Check the ErrorCode column:

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

The composite record input accepts sets of records, one set for each parent record. Each set of records must be separated with a blank row (all NULLs). The parent object will not finish processing its record until all composite object children have received a blank row to indicate completion of the composite records. You can use Query transform to prepare child data with the proper blank rows. See also the demonstration video for an example of creating records containing composite data.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The source object, query and parent 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 and find the property named like [QuickBooks Source].[ObjectFilter]. This is the property containing the query.
• Scroll down and find the Expressions property. Set up an expression to modify the query dynamically.

The composite record output contains not only fields from the composite record, but also the parent records. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with a blank row (all NULLs).

In order to retrieve the composite data for the Quickbooks Desktop version, you must include an element similar to the following in the ObjectFilter parameter:

<InvoiceQueryRqType>
  <IncludeLineItems>true</IncludeLineItems>
</InvoiceQueryRqType>

The example above will retrieve item objects associated with the invoice objects.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The token setup process is organized as a wizard consisting of three steps:

1. Step: Specify the REST service parameters required to initiate the authorization. If there are no parameters, check the next step.
2. Step: Authorize COZYROC SSIS+ to connect to the REST service. If the token retrieval succeeds, the next wizard step will have the access token, refresh token, etc automatically populated in the next step.
3. Step: If the automatic token retrieval fails, you can manually specify the access token. If all required information is populated, you can specify the token file name and password to save it.

Tips for successful token retrieve:

• Disable the Windows Firewall.
• Explicitly Run As: Admin Visual Studio.
• Try running the token retrieval from a different machine.

The custom authentication parameters are contained in the UserConfiguration property. The format is [name]=[value]. Each parameter is separated with newline (\r\n).

The DestinationResource, ExtendMetadata and Parameter 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 and find the property named like [REST Destination].[DestinationResource]. This is the property containing the destination resource.
• Scroll down and find the Expressions property. Set up an expression to modify the destination resource dynamically.

The component's Error Output is used for providing both error information and the record identifier information. Check the ErrorCode column:

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

The composite record input accepts sets of rows, one set for each parent record. Each set of rows must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child data with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The source resource and filters 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 and find the property named like [REST Source].[Resource]. This is the property containing the resource.
• Scroll down and find the property named like [REST Source].[ResourceParameter]. This is the property containing the resource parameters. The property is a string combination with format [name]=[value]. Each value is separated with a newline (\n).
• Scroll down and find the Expressions property. Set up an expression to modify the resource or parameters dynamically.

The composite record output contains not only fields from the composite record, but also the parent records. The output will be populated with sets of rows, one set for each parent record. Each set of rows is separated with blank row (all NULLs).

For additional information about modelling of composite records in SSIS+, please refer to this article.

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

The composite record input accepts sets of records, one set for each parent record. Each set of records must be separated with blank row (all NULLs). The parent record will not finish processing its record, until all composite record children have received blank row to indicate completion. You can use Query transform to prepare child data with the proper blank rows.

For additional information about modelling of composite records in SSIS+, please refer to this article.

The source entity and query parameters are available in the data flow properties list. Follow these steps to setup expression:

• Right-click on data flow canvas and select Properties menu.
• Scroll-down and find property named like [Sage].[QueryParameters]. This is the property containing the query parameters.
• Scroll-down and find Expressions property. Setup expression to modify the criteria dynamically.

The composite record output contains not only properties from the composite entity, but also the parent entities. The output will be populated with sets of records, one set for each parent record. Each set of records is separated with blank row (all NULLs).

For additional information about modelling of composite records in SSIS+, please refer to this article.

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:

• 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:
  • Windows 7 SP1 and Server 2008 R2 SP1
  • Windows 8.1 and Windows Server 2012 R2

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.

The destination object 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 [Salesforce Destination].[DestinationObject]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up an expression to modify the statement dynamically.

The component's Error Output is used for providing both error information and the processed record identifier. Check the ErrorCode column:

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

Uploaded files are located in the Attachment object. Use the standard "Import Column" transformation to import file content into the Body field. Check also the demo video above.

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.

NOTE: The concern elaborated in the below paragraphs doesn't apply to the latest SSIS+ releases. Since 1.9 we have implemented an extra handling for missing data in the responses, so if at least one result row is retrieved during design-time columns analysis, then all columns from the SOQL should be automatically created. Please note that still, in some rare cases, the data type of some the columns might not be extracted from Salesforce and WSTR 250 would be assumed for such columns.

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.

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 an SSIS 2005 package and insert the Data Flow Task. Go to data flow design and insert the COZYROC Script Component Plus.
2. Load a script in the component 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 Component 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.

• Go to Toolbox and open the Choose Toolbox Items ... dialog. Press the SSIS Control Flow Items tab and select Script Task Plus.
• 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
• 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.

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.

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.

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.

The SharePoint destination list 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 [SharePoint Destination].[DestinationList]. This is the property containing the destination list.
• Scroll down and find the Expressions property. Set up an expression to modify the statement dynamically.

The source list 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 and find the property named like [SharePoint Source].[InputList]. This is the property containing the input SharePoint list.
• Scroll down and find the property named like [SharePoint Source].[InputQuery]. This is the property containing the CAML query.
• Scroll-down and find the Expressions property. Set up an expression to modify the above properties dynamically.

This issue appears in environments using Symantec Mail Security. Disable Chunking parameter in the Advanced tab and try again to execute your package.

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

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

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 [Sybase 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.

Template Task have to be used when you would like to generate complex document, with input from multiple data flows. The Template component is recommended when you would like to generate result document, based only on one data flow input columns.

Use the $velocityCount variable reference to access the loop counter. The counter starts at 1.

If the $dt parameter contains the DateTime structure and you want to format the date as "year-month-day", use the DateTime.ToString method like this:

In the Template Editor dialog, check the 'Remove Newline' parameter. The Current object below implements the standard ADO.NET IDataReader interface.

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 [Teradata Destination].[DestinationTable]. This is the property containing the destination table.
• Scroll down and find the Expressions property. Set up an expression to modify the table dynamically.

The Table 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 the Properties menu.
• Scroll down and find the property named like [Teradata Source].[Table]. This is the property containing the source table.
• Scroll down and find the Expressions property. Set up an expression to modify the table dynamically.

To enable OAuth authentication in WordPress please folow these steps:
1. Go to the following page: https://[WordPress site]/wp-admin/users.php?page=rest-oauth1-apps
2. Press 'Add New'.
3. Set Consumer Name, Description.
4. Set Callback to http://www.cozyroc.com/oauth_callback/
5. Press 'Add Consumer'.

You can specify multiple source files for compression in two different ways:

• Specify source file(s) via File Connection Manager.
• Specify source file(s) via package variable.

The first option is a little bit tricky. If you provide an existing file with a wildcard specification, the dialog will not validate it. To make this work, create a File Connection Manager which points at the existing file. After you close the dialog, right-click on the just created connection manager and click the Properties menu. Set the ConnectionString property to the required wildcard specification. The Zip Task will now compress all files which match the specified wildcard.

Zip Task doesn't support appending files to existing archive. Use the workaround below to accomplish your goal:

• Setup Zip Task to extract the existing archive to a folder.
• Copy one or more files you want appended in the folder you have extracted.
• Compress the folder using Zip Task into a new archive.
• Overwrite the original archive with the new archive.