SSIS Tips: How to Generate a CREATE TABLE Script from Source Metadata in SSIS

SSIS Tips
, ,
1

Introduction

In this article, we will demonstrate how to generate a CREATE TABLE script from source metadata in SSIS (SQL Server Integration Services). This tutorial focuses on using a source component to read data and the ZappySys Upsert Destination component to automatically generate a CREATE TABLE script based on the metadata from the source.

By leveraging ZappySys SSIS PowerPack, you can automate the process of creating table structures dynamically from the source data, which is particularly useful for ETL workflows where the schema needs to be inferred or managed automatically.

Prerequisites

  • Download and install the ZappySys SSIS PowerPack.
  • Access to a SQL database.

Steps

Step 1: Configure the source component

  1. In the Control Flow, drag a Data Flow Task into the workspace, then double-click it to open the Data Flow tab.

  2. Drag a source component from the SSIS Toolbox into the Data Flow workspace. In this example, we will use the ZappySys JSON Source component.

  3. Double-click the JSON Source to configure it.

  4. Select the fourth example from the component and uncheck the option to include parents.

  5. Preview the data and press OK to save the configuration.

    JSON source configuration
    JSON source configuration912×747 56 KB

Step 2: Use the Upsert Destination to generate a CREATE TABLE script

  1. Drag the Upsert Destination component from the SSIS Toolbox and connect it to the JSON Source.

  2. Double-click the Upsert Destination to configure it.

  3. In the Target connection field, choose or create a connection to your database. You can use PostgreSQL, AWS Redshift, or a SQL Server connection where you want the table to be created.

  4. In the Target table field, click New, which will instruct the Upsert Destination to automatically generate a CREATE TABLE script based on the metadata from the source. Press OK to generate the new table.

    Generate script
    Generate script960×683 51 KB

  5. After generating the table, continue configuring the component, including insert/update options, and map the source fields to the corresponding columns in the destination.

  6. Click OK to save the configuration.

    Upsert destination configuration
    Upsert destination configuration960×647 42.2 KB

Step 3: Run the package

  1. Save the SSIS package.

  2. Click Start or Debug to run the package.

  3. The data will be imported and/or updated into the new table in your database.

    Final result
    Final result354×285 11.2 KB

Conclusion

By using ZappySys SSIS PowerPack components such as the Upsert Destination, you can automate the process of generating a CREATE TABLE script directly from source metadata. This solution significantly simplifies the creation of tables for ETL workflows, especially when working with dynamic or changing data sources.

Visit our official page to explore more connectors, powerful automation features, and real-time data integration tools. Start building smarter, faster, and more scalable solutions today with ZappySys SSIS PowerPack.

References

Contact us

If you encounter any issues or have specific questions, reach out to our support team via live chat or support ticket using our email support@zappysys.com.