[et_pb_section][et_pb_row][et_pb_column type=”1_4″][et_pb_sidebar admin_label=”Sidebar” orientation=”left” area=”et_pb_widget_area_1″ background_layout=”light” /][/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

 

Incorporating Custom DDL and SSIS into the Dimodelo Data Warehouse Studio project

 

[/et_pb_text][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]

Dimodelo Data Warehouse Studio can manage deploy and execute custom DDL (Data Definition Language T-SQL for creating and managing the Data Warehouse and Staging databases) and SSIS packages.

In the ‘Step 1 Set up the Solution Folder Structure’ section we recommended a folder structure that included a Custom folder. When DA deploys the DDL and SSIS packages it will also deploy code contained in this folder or sub folders of this folder. Place any custom DDL (with a file extension of .sql) or SSIS packages (with a file extension of .dtsx) into this folder.

The name of the folder containing the custom DDL is important. You will notice that in the Database folder, each folder is prefixed with a number. The number determines the order in which the contained .sql files are executed. To have the custom DDL deploy (be executed} in a particular place in the order of execution, name the folder with the appropriate prefix number.

The batch workflow is determined by the workflow file. As long as the custom SSIS packages conforms to a naming standard that the workflow file understands as a file it should execute, it will be executed as part of the ETL batch.

It is possible to have Dimodelo Data Warehouse Studio manage a Staging, Dimension or Fact table, and have a custom SSIS package to implement its ETL. Simply define the Staging, Dimension or Fact table in DA, but don’t define an Extract or Transform for it.

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]