Solved

SSIS - Configuring Execute SQL Task

Posted on 2014-02-17
7
498 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 65

Accepted Solution

by:
Jim Horn earned 250 total points
Comment Utility
>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
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
Give us an example of what you mean by 'dynamic', preferably with mockup data.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now