SQL 2008 - File System Task and ForEach Loop not playing well

• Brandie Tarvin

  SSC Guru

  Points: 173105

  More actions

  October 13, 2011 at 6:19 am

  #390612

  I have a package with a ForEach Loop Container that looks for file ABC *.txt. The * is a wildcard to cover for a date (each day's file comes with date as part of the name). The package renames the file to ABC.txt, massages the file, does its magic, then has a File System Task at the end that moves the file to another folder.

  The server is a 64 bit server and we use an Excel connection in the container that requires us to use the 32 bit runtime for SSIS in the job.

  The package works fine in BIDS. When it runs in a job, however, it both succeeds & fails. It runs through the container once, doing what it's supposed to, then fails on the Rename File task because it can't find the filename. This is odd to me because there is only one file available for these test runs and it should have been moved out of our Inbound folder by the time the package searches for a new file.

  I tried executing the package in SSMS directly, and it also succeeds without a failure. (But oddly, it ignored the config file I pointed it to and used my local desktop copy to get the email addresses).

  Has anyone seen this issue before with File System Tasks and ForEach Loop containers?

  I'm being told the only solution is to use Script Tasks to move the files, but we haven't seen this problem before yesterday. Both in Production and in Test, the package worked fine in the job until yesterday. No errors. Now, every time we run it in test, we're having this issue.

  Thoughts?

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• Brandie Tarvin

  SSC Guru

  Points: 173105

  More actions

  October 17, 2011 at 6:39 am

  #1395274

  Thread reposted in SQL 2008 General Discussion forum. Do not reply here.

  Please send all replies to the following link http://www.sqlservercentral.com/Forums/Topic1191269-391-1.aspx

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.