SSIS - Configuring Execute SQL Task

In my SSIS package I am using Execute SQL Task that checks if a table exists, and if it does, truncates it, and if it does not, creates the table. What I want is to make a table's name dynimic, i.e configurab;le via configuration file. Is that possible? How can I accomplish that?
LVL 35
YZlatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What I want is to make a table's name dynimic, i.e configurab;le via configuration file.
Feel free to spell out what you're trying to accomplish here, but my knee-jerk reaction is this is not a good road to go down.  SSIS requires a 'contract' between a source of data and its controls that consume them, and it is not optimized to handle 'dynamic' data sources.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
YZlatAuthor Commented:
Actually data sources are dynamic. I made them dynamic via config file. What I want to make dynamic is Execute SQL task. I want to be able to pass table names as parameters
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give us an example of what you mean by 'dynamic', preferably with mockup data.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony PerkinsCommented:
You will have to use Dynamic SQL for that.  As in:
DECLARE @TableName sysname = 'MyTableName'
EXEC ('CREATE TABLE ' + @TableName + '(Col1 integer)')

Open in new window

But I agree with Jim this is a solution looking for a problem.
0
YZlatAuthor Commented:
Here is what my SQL looks like:

IF  NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'Table_1' AND type = 'U')

BEGIN


CREATE TABLE [dbo].[Table_1](
	
[Field1] [varchar](15) NULL,
	
[Field2] [varchar](10) NULL,
	
[Field3] [varchar](10) NULL
)  ON [PRIMARY]

END

ELSE

BEGIN
TRUNCATE TABLE Table_1;

END

IF  NOT EXISTS (SELECT * FROM sys.tables WHERE name = N'Table_2' AND type = 'U')

BEGIN


CREATE TABLE [dbo].[Table_2](
	
[Field4] [varchar](15) NULL,
	
[Field5] [varchar](10) NULL,
	
[Field6] [varchar](10) NUL,
[Field7] [varchar](14) NULL,

)  ON [PRIMARY]

END

ELSE

BEGIN
TRUNCATE TABLE Table_2;

END

Open in new window


Anthony, how do I pass a parameter to an Execute SQL task?
0
Anthony PerkinsCommented:
I showed you how to do it:  You concatenate the name.

You can also use sp_executesql Stored Procedure
0
YZlatAuthor Commented:
Anthony, you didn't understand the question. I meant that my Execute SQl task is a part of my SSIS package, the first task in the flow, so I will call my SSIS package with a parameter but how does that parameter gerts passed to the Execute SQL task?

What you posted is below:

DECLARE @TableName sysname = 'MyTableName'
EXEC ('CREATE TABLE ' + @TableName + '(Col1 integer)')

What i want to know is how does 'MyTableName' gets to that point?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.