Creating Db Tables with Foreach Loop

Question

Creating Db Tables with Foreach Loop

AJF

SSC Enthusiast

Points: 186
More actions
November 17, 2010 at 7:10 am

#241251

Hello.. im a bit of a newbie with SSIS so please go easy!
I've set up a foreach loop to go through worksheets in a excel workbook and post them into a SQL Db table.
Problem is, I want each worksheet to go into their own db table, using the worksheet name as the table name. I assume I have to use a 'Execute SQL Task' component with a 'create table' statement but how to I use the variable used to create the loop in the SQL statement?
Any help greatly appreciated!
a.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

tmitchelar Hall of Fame Points: 3391 More actions · Answer 1

The name of the file will be stored in a variable that gets set in your Foreach Loop. Create a second variable that is a substringed version of that variable(to get rid of the file extension or at least replace the period with an underscore) within your create table statement in your Execute SQL Task.

AJF SSC Enthusiast Points: 186 More actions · Answer 2

What you're saying makes perfect sense but I'm struggling to figure out how to do it!

How do you select a substring of the variable and what sql syntax do I use to call that variable into the query.

Thanks for your help so far!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

On the parameter mapping of your Execute SQL Task, you can specify variables that will be used in the task.

Just add your variable there, with direction input, the correct data type and as parametername choose 0.

In your Execute SQL task, add the following code:

DECLARE @myVariable NVARCHAR(50); --whatever data type you need of course

SET @myVariable = ?;-- here you assign the value of the variable declared in the parameter mapping

-- to the variable created in the SQL script.

-- The question mark serves as a placeholder. The parameters are assigned by ordinal position, starting with 0.

-- In other words, the first question mark will be replaced with the parameter with name 0.

Now you can use the SQL function SUBSTRING to edit your variable.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AJF SSC Enthusiast Points: 186 More actions · Answer 4

Thanks for your reply... i've spent sometime looking up using dynamic sql to create tables and have it working ok in management studio but can't seem to get it working with the variable in BIDs...

I've mapped the variable as suggested but im getting an error about there being "incorrect syntax near 0"... The sql im using is below, i think i might be missing the point a bit.. sorry!

Declare @sql VarChar(1000)

DECLARE @WorksheetName VarChar(1000)

SET @WorksheetName = 0

SELECT @sql = 'Create Table ' + SUBSTRING(@WorksheetName, 1,6) + '('

SELECT @sql = @sql + 'ID int NOT NULL Primary Key, FieldName VarChar(10))'

Exec (@SQL)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

You shouldn't use 0, but a question mark.

A question mark tells the parser that a parameter is expected. In the parameter mapping, you specify those parameters. The names are the ordinal positions of the different question marks in the code.

For example:

declare @variable1 int;

declare @variable2 int;

set @variable1 = ?;

set @variable2 = ?;

Now, in your parameter mapping you'll specify 2 parameters. One for variable1 and one for variable2. You call the first one 0 and the second one 1.

So 0 maps with the first question mark and 1 maps with the second question mark (you start counting from 0).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AJF SSC Enthusiast Points: 186 More actions · Answer 6

That was one of the first thing I tried but all those boxes are still coming up red!

Its a different error message when replacing the '0' with a '?' in the code above.

"Syntax error, permission violation, or other nonspecific error".

Any other suggestions? I'm starting to think i've bitten off more than I can chew!!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Can you give us some screenshots (you can attach them to your post)? Blank out any sensitive information if necessary.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AJF SSC Enthusiast Points: 186 More actions · Answer 8

As requested... if any other boxes would be useful.. let me know.

Thanks!

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

Allright. At first sight nothing wrong there. Can you give a screenshot of the parameter mapping?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

AJF SSC Enthusiast Points: 186 More actions · Answer 10

AJF

SSC Enthusiast

Points: 186

November 23, 2010 at 6:12 am

#1254387

here you go...

AJF SSC Enthusiast Points: 186 More actions · Answer 11

AJF

SSC Enthusiast

Points: 186

November 23, 2010 at 6:13 am

#1254389

here you go...

spongemagnet SSCrazy Points: 2812 More actions · Answer 12

Looks like that should all work, but two things seem odd.

1) That error doesn't sound familiar. I was curious enough to try to build one of these in BIDS and try different things to get that same error, and it doesn't seem to be a result of a null variable, -1 length for parameter, no create table privileges, or invalid table name (only tried starting with $ or a number) - all of which gave more meaningful errors

2) There's no "Parameter Size" in the Parameter Mapping on the Execute SQL Task pic - not sure what that means.

If you put a breakpoint on Pre-Execute on the "Execute SQL Task" and find your variable used for the input parameter, does it have the expected value?

-Ken

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 13

spongemagnet (11/23/2010)
Looks like that should all work

I was thinking the same thing 🙂

spongemagnet (11/23/2010)
1) That error doesn't sound familiar. I was curious enough to try to build one of these in BIDS and try different things to get that same error, and it doesn't seem to be a result of a null variable, -1 length for parameter, no create table privileges, or invalid table name (only tried starting with $ or a number) - all of which gave more meaningful errors

That error SSIS gives is a real general one. It could mean anything... (most of the time there is something wrong with the parameters)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246961 More actions · Answer 14

Maybe it's worth trying this with an Expression instead and seeing what happens.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin