Real World Foreach Loop Container example

Question

Post reply

Real World Foreach Loop Container example

Paul Clancy

SSC Eights!

Points: 858
More actions
February 23, 2008 at 1:44 pm

#184235

Comments posted to this topic are about the item Real World Foreach Loop Container example

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

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

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 1

Great article. I was just about to send a very similar article for submission. Guess you beat me to it:)

TeeGee Say Hey Kid Points: 697 More actions · Answer 2

I second that. The for each loops are a great tool. I use one with variables and script for looping through database backups on my main server to grab the latest dated ones and FTP them over to a secondary backup server (no I'm not really paranoid about losing data!)

Best Regards
Terry

pclancy Grasshopper Points: 16 More actions · Answer 3

pclancy

Grasshopper

Points: 16

February 26, 2008 at 12:39 am

#781870

Sorry 😉

tran008 Ten Centuries Points: 1002 More actions · Answer 4

Just came across your article and fine it very useful tools for me to learn sqlis. I have a problem with Execute SQL Task in the loop container:

Insert into tmpTlogFileList(FileName)values(@FileName):

Execute SQL Task: Executing the query "insert into tmpTlogFileList (FileName) values (@FileName)" failed with the following error: "Must declare the scalar variable "@FileName".".

Must I declare @Filename as nvarchar(128) from the sql statement?

thanks

Paul Clancy SSC Eights! Points: 858 More actions · Answer 5

Right-click your "Execute SQL Task". In the "Execute SQL Task Editor", go to Parameter Mapping. Add a parameter as follows:

Variable Name - User::varTLogFileName

Direction - Input

Data Type - String

Parameter Name - @FileName

Parameter Size: -1

Hope this is now clear. Best of luck!

tran008 Ten Centuries Points: 1002 More actions · Answer 6

Paul,

I get the same error...Here are a bit more detail on execute sql task:

General tab:

resultset: None

connectionType: OLE DB

connection: UserServerConnection

sqlsourceType: Direct input

sqlstatement: insert into tmpTLogFileList(FileName) values (@FileName)

bypassPrepare: True

The Parameter Mapping is same above as you mention, except

for Data Type - Nvarchar, I can't fine the dropdown for String.

I appreciate all you help thanks....

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "insert into tmpTLogFileList(FileName) values (@FileName)" failed with the following error: "Must declare the scalar variable "@FileName".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

TeeGee Say Hey Kid Points: 697 More actions · Answer 7

tran008,

I think you need to provide a bit more basic information about what you are trying to do. Where are you getting the @FileName parameter from anyway? My thoughts are that it should be declared as a User::[varname] in the for each loop and then this mapped across to the parameter in the SQL Task, but without knowing what it is you are trying to do it is hard to be of more assistance.

Best Regards
Terry

TeeGee Say Hey Kid Points: 697 More actions · Answer 8

As an after thought - depending on what you wish to do of course, you may have to execute the sql statement as a variable. Out of curiosity I just put together a for each loop to save a series of .txt files into a table and this worked well by using a variable for the sql statement. Let me know if you want any further detail on this method.

Best Regards
Terry

tran008 Ten Centuries Points: 1002 More actions · Answer 9

thanks...I finally figure this out after long search.

OleDB...connection.

INSERT INTO tempTlogFileList

(FileName)

VALUES (?)

instead of

INSERT INTO tempTlogFileList

(FileName)

VALUES (@FileName)

Paul Clancy SSC Eights! Points: 858 More actions · Answer 10

Sorry I couldn't get back to you earlier on this and save you the long search, but the cause of your problem was the OLEDB connection. My example uses an ADO.NET connection for the Execute SQL Task (this wasn't explicitly mentioned in the article - my apologies), in which case you need to use the (@FileName) syntax as described on the article.

laura garcia Old Hand Points: 342 More actions · Answer 11

Oh, good, the (?) parameter works. You still have to do the Parameter Mapping in the Execute SQL task (the parameter name = 0).

Laura

mona.togatorop Newbie Points: 9 More actions · Answer 12

I'm new to SSIS so this might be a very basis error but I'd appreciate some

help...

I've got error in Delete Old log File (File system Task) :

Variable VarDeleteFileSName is used as a source or destination and is empty

Please help me..

Thank you

Paul Clancy SSC Eights! Points: 858 More actions · Answer 13

You'll need to do a bit of debugging. The first place I'd look is in the SQL statement that populates the varDeleteFileList variable; if you run this as a query in the Management Studio do you get a result set? If not, that's your problem.

If you do, try adding breakpoints in the script code just before the Dts.TaskResult line (for instance). That way you'll be able to see which (if any) values are being passed along.

to.bobs Grasshopper Points: 24 More actions · Answer 14

Paul - Just wanted to thank you for putting this "Real World" article up. I'm working on adapting an existing full-blown ETL/OLAP iterative process to use SSIS looping tasks to provide high-level control of detail low-level SP processing. The details of your Var defs, DeleteList DataFlow, and Convert Object variable script were just what I was looking for - thanks for taking the time to do this.

-- BobS --