Search code examples
ssisintegrationssis-2019

Pass variable to execute sql task for create table in ssis pakage

I pass variable to execute sql task for create table in ssis package but show error

enter image description here

I pass variable to execute sql task for create table in ssis package but show error. command= "CREATE TABLE ? (ID int , FirstName nvarchar(50) , LastName nvarchar(50))"

enter image description here

It's "Parameter Mapping" used in Execute SQL Task

enter image description here

Solution

  • The root problem is that you cannot parameterize a table (or column) name using the Parameter mapping as shown.

    Instead, the way to make a dynamic table creation work is to use Expressions on SSIS Variables. Good old fashioned string concatenation to build up your CREATE TABLE statement and then the SQLSourceType from DirectInput to from Variable

    Create a new SSIS Variable of type string. I'm going to call it QueryTableCreate In the Expression column, click the ellipses ... and in the popup window, use the following

    "CREATE TABLE " + $Package::db_name + " (ID int);"

    Clicking Evaluate Expression should yield

    CREATE TABLE dbo.Foo (ID int);

    If you are also building out the columns dynamically, I would strongly encourage the creation of an SSIS Variable ColumnDeclaration which would then make the original expression into

    "CREATE TABLE " + $Package::db_name + " ("+ User::[ColumnDeclaration] +");"

    The benefit as I see it of this approach is that you can easily test/verify the value of your dynamic statement outside of the component and easily swap pieces in and out as your queries get more complex.

    Not addressed here but things you should consider based on your use case

    • Wrapping schema/table/column with square brackets [] to ensure valid object names
    • If there is any chance of SQL Injection concerns, do not use this approach.