CODEWITHSUNDEEP
stringssis
sriram L
sriram L

Reputation: 5

ssis string variables value should be used in exequte sql task

I am trying to create a table. In the table name id is unique (MARKET_id is the name of the table).

The id has leading zeros which needs to be preserved. So I declared id as a string variable.

Example: id=02161515. And in parameter mapping I mapped it as VARCHAR.

An error occurs when I'm using a sql statement CREATE TABLE MARKET_?;. It results in: CREATE TABLE MARKET_'02161515'; the quotes is unnecessary and throws an error found "'" in the sql statement.

Please help!

Upvotes: 0

Views: 46

Answers (1)

dim
dim

Reputation: 146

I guess you have columns added to your table, but are not posted?

One way to solve this would be to create two variables, one for the table name and one for the create table statement.

Example (for demonstration only):
First variable is TABLE_NAME and defined with the following expression for demonstration of dynamic naming:
"dmu.MY_TABLE_"+(DT_STR,30,1252)datepart("s",getdate()) + "( id int)"

Second variable is CREATE_TABLE_SQL and defined as follows:
"Create table "+ @[User::TABLE_NAME]

SSIS Varibles

Next change the Settings of your Execute_SQL_Task:
Set SQLSourceType to Variable and SourceVariable to User::CREATE_TABLE_SQL Execute_SQL_Task Settings
This will create the table in your database

Upvotes: 1

Related Questions