SSIS ADO.NET Destination table using variable?

0
votes

I am using SSDT and working on a simple SSIS package.

The Control flow: 1. A Foreach Loop Container and seek a folder exist a "importdata{}.csv" file or not. 2. If found, a script task will set variables: - User::FullPath = (e.g C:\importdata{}.csv) - User::varFileNameNoExt = (importdata{}) without extension. The {} is possible in "toy","game","food". 3. Go to dataflow

The Data Flow: 1. Flat File Source with a flat file connection, the connection string is varible and mapped connection string expression. 2.ADO.NET Destination , insert data.

enter image description hereenter image description here

My question is how can i set the ADO.NET Destination [TableOrViewName] Property in variable? Assume the table : importdatatoy,importdatagame and importdatafood is created on SQL Server.

I try to set as "dbo"."[User::varFileNameNoExt]" ,but it cannot resolve the table name on runtime.

enter image description here

2
sql-serverssis

2 Answers

3
votes

ADO.NET Destination [TableOrViewName] parametrization can be done at Data flow level. In data flow properties, you can specify "ADO.NET Destination [TableOrViewName]".

Also specify the quotes while assigning value to variable Eg: varFileNameNoExt = "dbo"."tableName"

But first you will need to create mapping with an existent table.

1
votes

Can you post your error message? I'm thinking you won't be able to combine static text and a variable like that inside of the TableOrViewName field. Instead do the combination in a new [User::varTableName] SSIS variable and use the Advanced Properties Expression editor to set the TableOrViewName to this new SSIS variable. Have a look here.