• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

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?
0
YZlat
Asked:
YZlat
  • 3
  • 2
  • 2
2 Solutions
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now