SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul Clancy / Real World Foreach Loop Container example / Latest Posts

RE: Real World Foreach Loop Container example

phouse — Fri, 08 Apr 2011 16:56:05 GMT

I would like to note that as of 4.8.2011, the images in the article are not viewable and give broken link errors. Thanks.

RE: Real World Foreach Loop Container example

saritha.ijjurouthu — Wed, 31 Mar 2010 13:56:23 GMT

Hiveru use full information. I have another type of question. I have used Foreach loop to import multiple files. But if one file has some problem whole package is erroring out, Instead of going for another file it stops.How do I solve this problem ? Saritha

RE: Real World Foreach Loop Container example

mmaddox 39987 — Fri, 13 Nov 2009 13:40:44 GMT

[quote][b]mona.togatorop (5/21/2008)[/b][hr]I'm new to SSIS so this might be a very basis error but I'd appreciate somehelp...I've got error in Delete Old log File (File system Task) :[quote]Variable VarDeleteFileSName is used as a source or destination and is empty[/quote]Please help me.. Thank you[/quote]I know this is six months late, but I resovled this exact scenario by placing a default value for varDeleteFileSName when declaring the global variable in the package. After that, the File System Task ran as expected. I supposed it needed to know there was a value before it executes. And because the script had not yet assigned a value to the variable, the File System Task would not start. Mark Maddox.

RE: Real World Foreach Loop Container example

mmaddox 39987 — Fri, 13 Nov 2009 13:37:13 GMT

RE: Real World Foreach Loop Container example

alen teplitsky — Fri, 13 Nov 2009 12:22:02 GMT

great articlesome time ago i played with something similar to import csv files with performance data but never got it to work in importing only the files that haven't been imported before. going to look at this again using a slightly modified version of your procedure

RE: Real World Foreach Loop Container example

thierry.vandurme — Fri, 13 Nov 2009 03:20:43 GMT

Great article, thanks! Will help me to get startedBut at the end you wrote: "And that's it! Running the package deletes all backup files that haven't been restored except the newest sixteen".You do mean "that HAVE been restored", right? :)

RE: Real World Foreach Loop Container example

sam arteaga — Wed, 29 Jul 2009 18:05:42 GMT

Hi Paul,I read your article and it helped me a lot. One question I have is how can I add the file creation date to my table?Thanks a lot Sam

RE: Real World Foreach Loop Container example

oleg yuriev-460662 — Sun, 26 Jul 2009 23:34:08 GMT

Just wonder how to set a folder (directory) using a global variable? When i use my variable Directory user:: , the SSIS finishes with message - "no files in the specified directory" (or something like that, no failure though). Yet , files are there and when i "hard-code" the path and execute the SSIS again, everything works. What am i missing?

RE: Real World Foreach Loop Container example

Paul Clancy — Sat, 14 Feb 2009 14:11:32 GMT

My pleasure, thank you very much for your kind comments.

RE: Real World Foreach Loop Container example

to.bobs — Sat, 14 Feb 2009 09:30:45 GMT

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 --

RE: Real World Foreach Loop Container example

Paul Clancy — Wed, 21 May 2008 07:55:17 GMT

You'll need to do a bit of debugging. The first place I'd look is in the SQL statement that populates the [b]varDeleteFileList [/b]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.

RE: Real World Foreach Loop Container example

mona.togatorop — Wed, 21 May 2008 03:04:13 GMT

I'm new to SSIS so this might be a very basis error but I'd appreciate somehelp...I've got error in Delete Old log File (File system Task) :[quote]Variable VarDeleteFileSName is used as a source or destination and is empty[/quote]Please help me.. Thank you

RE: Real World Foreach Loop Container example

laura garcia — Thu, 20 Mar 2008 11:42:32 GMT

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

RE: Real World Foreach Loop Container example

Paul Clancy — Fri, 29 Feb 2008 03:38:37 GMT

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.

RE: Real World Foreach Loop Container example

tran008 — Thu, 28 Feb 2008 15:03:41 GMT

thanks...I finally figure this out after long search. OleDB...connection.INSERT INTO tempTlogFileList (FileName)VALUES (?)instead of INSERT INTO tempTlogFileList (FileName)VALUES (@FileName)

RE: Real World Foreach Loop Container example

TeeGee — Wed, 27 Feb 2008 17:09:14 GMT

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.

RE: Real World Foreach Loop Container example

TeeGee — Wed, 27 Feb 2008 16:24:42 GMT

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.

RE: Real World Foreach Loop Container example

tran008 — Wed, 27 Feb 2008 10:22:51 GMT

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: TrueThe Parameter Mapping is same above as you mention, exceptfor 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

RE: Real World Foreach Loop Container example

Paul Clancy — Wed, 27 Feb 2008 09:39:36 GMT

Right-click your "Execute SQL Task". In the "Execute SQL Task Editor", go to Parameter Mapping. Add a parameter as follows:Variable Name - User::varTLogFileNameDirection - InputData Type - StringParameter Name - @FileNameParameter Size: -1 Hope this is now clear. Best of luck!

RE: Real World Foreach Loop Container example

tran008 — Wed, 27 Feb 2008 09:14:14 GMT

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

RE: Real World Foreach Loop Container example

pclancy — Tue, 26 Feb 2008 00:39:45 GMT

Sorry ;)

RE: Real World Foreach Loop Container example

TeeGee — Mon, 25 Feb 2008 15:26:52 GMT

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!)

RE: Real World Foreach Loop Container example

J.D. Gonzalez — Mon, 25 Feb 2008 09:48:11 GMT

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

Real World Foreach Loop Container example

Paul Clancy — Sat, 23 Feb 2008 13:44:34 GMT

Comments posted to this topic are about the item [B]Real World Foreach Loop Container example[/B]