Mapping data flow SQL query and Parameters failing

Question

In my mapping dataflow I have simplified this down to dimdate just for the test

My parameters are

The source even tells you exactly how to enter the select query if you are using parameters which is what I'm trying to achieve

Then I import but get two different errors for parameterizing a table`

SELECT * FROM {$df_TableName}

I get

This error from a select * or invidiual columns

I've tried just the WHERE clause (what I actually need) as a parameter but keep getting datatype mismatch errors

I then started testing multiple ways and it only allows the schema to be parameterised from my queries below

all of these other options seem to fail no matter what I do

SELECT * FROM [{$df_Schema}].[{$df_TableName}] Where [Period] = {$df_incomingPeriod}

SELECT * FROM [dbo].[DimDate] Where [Period] = {$df_incomingPeriod}

SELECT * FROM [dbo].[{$df_TableName}] Where [Period] = {$df_incomingPeriod}

SELECT * FROM [dbo].[DimDate] Where [Period] = 2106

I know there's an issue with the Integer datatype but don't know how to pass this to the query within the parameter without changing its type as the sql engine cannot run [period] as a string

Answer 1

Awesome, it worked like magic for me. I was struggling with parameterizing tables= names which I was passing through Array list. Created a data flow parameter and gave this value: @item().TABLE_NAME