SSIS tip: Generating `CREATE TABLE` SQL script from source

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 using the ZappySys SSIS PowerPack, you can automate the process of creating table structures dynamically from the source data, which is helpful 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.

  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. Double-click to configure it.

  2. In the Target connection field, choose or create a connection to your database. You can use PostgreSQL, AWS Redshift, or a SQL Server connection.

  3. 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

  4. After generating the table, continue configuring the component, e.g. set Insert or Update options, and map the source fields to the corresponding columns in the destination.

  5. Click OK to save the configuration.

    Upsert destination configuration
    Upsert destination configuration960×647 42.2 KB

Step 3: Run the package

  1. Save and run the SSIS package.

  2. 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 the 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 approach simplifies the creation of tables for ETL workflows, especially when working with dynamic or changing data sources.

Download and install to start building smarter, faster, and more scalable solutions today! Also don’t forget to visit SSIS PowerPack landing page to explore more connectors, powerful automation features, and real-time data integration tools.

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.