Exporting Data to SQL Server

The process to export data to a SQL Server is described in the following sections:

Define the SQL Server Target Data Connection

The following are the steps to configure a Postgres data connection:

1. Navigate to the required domain and click the Target Data Connections icon.
2. Click Add Connection.
3. In the Add Data Connection page, provide the following connection details.
4. Click Save.

Create a Pipeline to Add SQL Server Node

Following are the steps to add a new pipeline to the domain:

1. Click the Domains menu and click the required domain from the list. You can also search for the required domain.
2. Click the Pipelines icon.
3. In the Pipelines page, click the New Pipeline button.
4. Enter the following details.
5. Click Save. The new pipeline will be added to the list of pipelines.

Design a Pipeline

Following are the steps to design a pipeline:

1. In the Pipelines page, click the new pipeline created. The Overview page is displayed with the pipeline version details and a blank design space.
2. Click Open Editor or click on the blank design space to open the editor page.
3. From the left panel, drag and drop the required Source tables from the Datalake Tables tab, and SQL Server Table node from the Transformations tab, to the pipeline editor and connect them to design a pipeline.

SQL Server Table Node Configuration

Following are the steps to configure SQL Server Table node in pipeline:

1. Double-click the SQL Server Table node. The Properties page is displayed.
2. Click Edit Properties.
3. Click Save.

NOTE Timestamp values upwards 'YYYY-MM-DD hh:mm:ss.xxx' are not supported.

Export the Data

Following are the steps to build a pipeline:

1. Navigate to the required pipeline and click the Build icon.
2. Select the Version and click Build Pipeline to build the pipeline version.
3. You can click the Build Target Metadata button to enable import of multiple, sequential pipelines with export configuration.
4. Click any of the existing builds under the Recent Builds section to view the detailed information of the job. You can view the summary. You can also download logs of the job.

Supported Datatype Mappings

Below is the list of supported source datatypes and their mapping to SQL datatypes:

SQL Server Pipeline Export Privileges Required

Option 1 - Infoworks Managed

Database level privileges:

• CREATE TABLE
• DROP TABLE
• TRUNCATE TABLE
• EXEC SP_RENAME

Object level privileges:

• SELECT
• INSERT
• UPDATE
• TRUNCATE
• DELETE
• ALTER (if Sync Table Schema is enabled)

Schema level:

if the check box for "Create a schema if it does not exist" is checked then the corresponding permissions would be needed for the SCHEMA.

Option 2 - Is Existing Table (User-Managed)

Is Existing Table checkbox. When checked, the existing table behavior works as an external table to Infoworks. The table will not be created/dropped or managed by Infoworks. The Is Existing Table option can be used if you do not want to grant create table and drop table statements.

Database level privileges:

• TRUNCATE TABLE

Object level privileges:

• SELECT
• INSERT
• UPDATE
• TRUNCATE
• DELETE
• ALTER (if Sync Table Schema is enabled)

Schema level:

If the check box for "Create a schema if it does not exist" is checked then the corresponding permissions would be needed for the SCHEMA.