Solved

SSIS - Configuring Execute SQL Task

Posted on 2014-02-17
7
508 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 250 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

729 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