Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS - Configuring Execute SQL Task

Posted on 2014-02-17
7
Medium Priority
?
515 Views
Last Modified: 2016-02-10
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?
0
Comment
Question by:YZlat
  • 3
  • 2
  • 2
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 750 total points
ID: 39865942
>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
 
LVL 35

Author Comment

by:YZlat
ID: 39867959
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39867974
Give us an example of what you mean by 'dynamic', preferably with mockup data.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 750 total points
ID: 39869300
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
 
LVL 35

Author Comment

by:YZlat
ID: 39877627
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39878398
I showed you how to do it:  You concatenate the name.

You can also use sp_executesql Stored Procedure
0
 
LVL 35

Author Comment

by:YZlat
ID: 39886461
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question