SSIS Dynamic Data Import Task

The goal of the Dynamic Data Import Task is to make it exceedingly simple to import diverse types of data. It's effectively an SSIS "wrapper" for the functionality offered by SQL-Hero's BULK_IMPORT template tag. You're able to set a data source reference (typically a URL), a target connection, and target table name. Data from the source is imported into the target table - either replacing current contents or appending to them. Hints related to data parsing can be provided, with the default behavior covering many scenarios with no additional effort.

Property Name Overview / Default

SQLHeroServerName

A URL reference to your SQL-Hero application server services. Typically this would be "http://servername:46837" where "servername" matches the server on which you installed the SQL-Hero services, with port 46837 being the default port number used by the SQL-Hero installer program.

Default: None (required)

TargetAlias

The SQL-Hero connection alias to use as the target database for the import operation. Either this must be specified or a connection manager connection name in the TargetConnection property.

Default: None (either TargetAlias or TargetConnection is required)

TargetConnection

The name of a connection manager connection which is used as the target database for the import operation. Either this must be specified or a SQL-Hero connection name in the TargetAlias property.

Default: None (either TargetAlias or TargetConnection is required)

SourceSpec

A reference to the source data to be queried. Can be a reference that is HTTP, HTTPS or FILE based. Can optionally be an expression that supports loading data across multiple requests, as described here.

Default: None (required)

TargetTableName

The table name that will receive the imported data, in the target database.

Default: None (required)

TransformDetails

Optional configuration data that helps define mapping, data detection hints, etc. This is expressed using XML and the task configuration screen helps build a valid document to describe your desired configuration. More documentation to follow.

Default: None (optional)

TargetAction

Identifies the action to take to import the data. Valid options include:

Setting Description

DropCreate

The target table is dropped and re-created on load. This implies the target schema could change if the source schema changes.

Truncate

The target table is truncated before load. (This will not work if foreign keys have been added, for example, in which case DeleteAll could be a better option.)

DeleteAll

A full DELETE is run against the target table before load.

Append

Data is appended to the target table. If unique keys have been added to the target, import could fail if the import would introduce duplicates.

Default: DropCreate (required)

TargetFormat

Identifies the format of the source data, or the inference rules used. Valid options include:

Setting Description

DynamicData

Use of DynamicData allows the task to create the target table if it does not already exist (or if it is being dropped and re-created). The dynamic nature comes from the target schema being built based on the source schema, meaning there is little change of a problem in importing the data since the schemas will match.

DynamicDataWithRID

Same as DynamicData, but an additional "RID" IDENTITY column is included in the target table.

DynamicStringData

Similar to DynamicData except target columns will always be the nvarchar type.

DynamicStringDataWithRID

Same as DynamicStringData, but an additional "RID" IDENTITY column is included in the target table.

FixedFormatCSV

The source data is known to be comma-separated text and the target table exists and is used to generate a format file to be used by the bulk import.

FixedFormatTSV

The source data is known to be tab-separated text and the target table exists and is used to generate a format file to be used by the bulk import.

FixedFormatCSVNoDefaults

The source data is known to be comma-separated text and the target table exists and is used to generate a format file to be used by the bulk import. Columns in the target that have SQL DEFAULT's are excluded from the import (i.e. default values are used).

FixedFormatTSVNoDefaults

The source data is known to be tab-separated text and the target table exists and is used to generate a format file to be used by the bulk import. Columns in the target that have SQL DEFAULT's are excluded from the import (i.e. default values are used).

LogFormat

The source data is treated as text with each line imported into a field called "Line" (varchar) in the target.

LogFormatWide

The source data is treated as text with each line imported into a field called "Line" (nvarchar) in the target.

LogFormatWithRID

The source data is treated as text with each line imported into a field called "Line" (varchar) in the target, along with an IDENTITY column ("RID") to provide sequencing.

LogFormatWideWithRID

The source data is treated as text with each line imported into a field called "Line" (nvarchar) in the target, along with an IDENTITY column ("RID") to provide sequencing.

Default: DynamicData (required)