SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Real World Foreach Loop Container example


Real World Foreach Loop Container example

Author
Message
Paul Clancy
Paul Clancy
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 196
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
laura garcia
Valued Member
Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)Valued Member (66 reputation)

Group: General Forum Members
Points: 66 Visits: 33
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
mona.togatorop
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
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
Paul Clancy
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 196
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
to.bobs
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 10
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 --
Paul Clancy
Paul Clancy
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 196
My pleasure, thank you very much for your kind comments.
oleg yuriev-460662
oleg yuriev-460662
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 51
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?
sam arteaga
sam arteaga
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 86
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
thierry.vandurme
thierry.vandurme
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1667 Visits: 566
Great article, thanks! Will help me to get started

But 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? Smile
alen teplitsky
alen teplitsky
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11210 Visits: 4674
great article

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search