Create a Data Transfer package¶

A Data Transfer package moves rows from a source to a destination, with optional column mapping and merge behaviour. It is the only type in the Data movement area, and the most configurable type in the product.

Use it for the point-to-point movement that does not justify a full TDI pipeline: copying a lookup table between systems, landing a CSV into a table, pushing a small extract to a partner database.

Before you start¶

• A gateway, or Timextender Cloud: Use 'Timextender Cloud' for cloud-hosted execution.
• A source and a destination connection, unless an end is a script
• Write permission on the destination, and — if you use merge — permission to create the staging table

The five sections¶

The Definition tab holds a vertical rail rather than one long form:

Steps¶

1. Create the package¶

Orchestration → Packages → Create, then Data Transfer from the Data movement area.

2. Choose the execution connection¶

One connection covers the whole package — there is not one per side. It defaults to Timextender Cloud (default). Pick one of your own gateways if either end is inside your network.

3. Source¶

Pick a provider, then a schema and table — the table list loads once you have chosen a schema. Both accept a typed value as well as a selection:

The source schema (e.g. dbo). Loaded from the provider; you can also type one.

Or switch on custom SQL to read with a query instead:

Toggle on to read the source with a custom SQL query instead of picking a table.

Preview shows what the source returns — the fastest way to check a query before wiring up the rest. It is available for a table and for custom SQL, but not when the source is PowerShell, because there is no query to preview.

Pre- and post-execution statements run before and after the transfer: disabling an index, or stamping a control table when the load completes.

4. Destination¶

Pick a provider, schema and table — or use Create Table, which opens a dialog pre-filled from the source:

The schema the new table is created in. Pre-fills from the source table's schema.

The name of the new destination table. Pre-fills from the source table.

You can edit the column list, with types and a primary key, or supply your own CREATE TABLE statement.

There is no custom-SQL mode for a non-PowerShell destination, because a SQL destination is always a table.

5. Merge¶

Merge is on by default for a new package. Turn it off for a plain insert. A duplicated package inherits whatever the original had.

The product explains it on the tab itself:

Merge (upsert) writes incoming rows into a staging table, then updates existing destination rows and inserts new ones. It requires at least one Primary Key column, set in Column mapping.

With merge on, supply a Staging schema and Staging table name. Both pre-fill from the destination table name once one is chosen.

6. Column mapping¶

Load Columns reads the source and destination schemas and fills the grid. Auto Map matches by name. Reset Mapping clears everything.

Each row takes a Source column, or one of two special values — No mapping and Custom Value. With merge on, each row also gets:

• Action — Primary Key, Update, or Ignore
• Custom Value
• Collation, optional

Collation is available on any mapped column, not only on rows set to Update.

The grid is hidden entirely when the destination is PowerShell — there are no destination columns to map to.

7. Save¶

Saves as a Draft.

Things that clear your work¶

Changing the source or destination provider, schema or table clears the existing column mapping, because the old mapping no longer refers to anything real. Pre- and post-execution content is kept.

A duplicated merge package gets a suffix on its staging table name, so the copy does not collide with the original.

Best practices¶

Preview the source before configuring anything else. Most misconfiguration is a source query that does not return what you assumed, and preview catches it in seconds.

Prefer merge with a key over delete-and-insert for anything a report reads. Delete-insert leaves a window where the table is empty.

Keep pre- and post-execution statements small and reversible. A post-execution statement that does not run because the transfer failed should not leave the destination unusable.

Name the staging table for the package. A shared staging table across several transfers is a race waiting to happen.

Think twice before using a script end. It is powerful, but a Data Transfer containing PowerShell is easy to miss when auditing what code runs in the flow. A PowerShell package is more visible when the work is really a script.

Check the mapping after changing either end. A column added to the source is not automatically carried through.

Troubleshooting¶

The schema or table list is empty. The connection's credentials cannot read the database's metadata. You can type the schema and table names instead — both fields accept typed values.

The source preview errors but the query works in a SQL client. The connection's credentials differ from yours, or the query references something outside the connection's default database.

Merge fails with a permissions error. The staging table cannot be created. Grant the connection permission to create tables in the staging schema, or point it at a schema where it already can.

Rows transfer but columns are wrong or empty. The mapping. Check it explicitly rather than assuming names matched.

The transfer succeeds and the destination is empty. The source returned no rows. Preview it — a filter on a date that no longer matches anything is the usual cause.

Reference¶

Validation¶

The editor asks for: Please select an O&DQ Gateway, Please select a source data provider, Please select a source schema, Please enter a source table, Please enter the source custom SQL, Please enter a source PowerShell script, Please select a destination data provider, Please select a destination schema, Please enter a destination table, Please enter a destination PowerShell script, and for merge Please enter a staging schema for merge and Please enter a staging table name for merge.

Which apply depends on the shapes you chose at each end. Hover Save for the current list.

Where the work happens¶

Column mapping and merge behaviour are implemented in the ExDataSync component used by the execution engine, not in the TDP editor — so behaviour questions about how a merge resolves should be checked against that rather than assumed from the UI.

Related¶

• Packages overview
• Import a CSV to a database through a Data Transfer — a worked scenario from the desktop era
• Create a Data Provider Command package — for commanding a database rather than moving rows