How to load a new table with the value of a variable from SSIS package?

Question

Hi, i have two variables in a SSIS package Var1 and Var2. Both of these variables have values. is there any way i can put the values of these two variables in a new table? e.g In New table col1 having value of Var1 and col2 having value of Var2.

Thanks

Answer 1

SELECT @Var1 AS COL1, @Var2 AS COL2
INTO #MyTempTable

I haven't tried this. However, I think it should work.

Answer 2

There are a couple of ways to do this.

One is to create a data flow with a Derived Column Task. Then you can pull in both variables and send it to a OLE DB or SQL Server Destination.

Another is to use a Execute SQL Task. The trick part of this is using the parameters correctly. The query would look like:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[VarLog]') AND type in (N'U'))
DROP TABLE dbo.[VarLog]

Create table dbo.varlog (
    [var1] [varchar](50) NULL,
    [var2] [varchar](50) NULL
) 

Insert into varlog (var1,var2) values(?, ?)

On the Parameter Mapping Screen add your two variables. Note that the Parameter Name is a 0 based counter that corresponds to the ?. See screen show for example. In my test I used the system variables for PackageName and StartTime.

Note: I opted to drop and replace the table for the example. Most likely you will want to have this static and maybe add a datetime to the table to track the vars over time.

Note2: I know start time isn't a varchar but it serves the purpose of the example.