ForEach Loop with Variable not working

  • I've got a fairly simple SSIS package.

    Fore Each Loop container that has the Foreach File enumerator option selected with the following two expressions

    Directory @[User::FileLocation]

    Filespec "*.5TA7"

    I have confirmed that there are files in the directory I pass on the command line with the following command

    C:\Program Files\Microsoft SQL Server\100\DTS\Binn>/DTS "\MSDB\EpicScreenViewLoad" /SERVER "IHD-2000" /CHECKPOINTING OFF /SET "\Package.Variables[User::xsdlo

    cation].Properties[Value]";"\\\\internet-dev\\Apps\\5TA7\\5TA7.xsd" /SET "\Packa

    ge.Variables[User::FileLocation].Properties[Value]";"\\\\internet-dev\\Apps\\5TA

    7\\App_Data\ewtest" /SET "\Package.Variables[User::DeletePath].Properties[Valu

    e]";"\\\\internet-dev\\Apps\\5TA7\\App_Data\\Processed" /SET "\Package.Variables

    [User::ConnectionString].Properties[Value]";"\"Data Source=IHD-2000;Initial Cata

    log=EpicScreenInfo;Provider=SQLNCLI10.1;Integrated Security=SSPI;\"" /SET "\Pack

    age.Variables[User::intFileMaxAge].Properties[Value]";30 /REPORTING E

    When I run this package via command line, it tells me that the file enumerator is empty.

    I've confirmed that both the Integration services service and the SQL Agent both have access to the directory specified for FileLocation, and that the values are getting into the package (by creating a Data Flow task that sends them to a table).

    If I set the variables with values and run the package in BIDS, it works with no problems.

    I'm past confused here. Please help.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • It took a while, but I finally tracked it down.

    The command line to set all those parameters was apparently too long. Depending on what I set for values, I would either get an error about -dev\blah blah blah was an invalid parameter, or the system would just skip it all.

    Switched to a config file, and everything just works.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Do you think it was a dtexec limit? an OS shell limit? other limit?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/25/2011)


    Do you think it was a dtexec limit? an OS shell limit? other limit?

    One of those two... regardless switching to a config file and specifying everything there resolved the issue nice and simple. I'd say it was an OS shell limit more than anything. I was getting the same basic error running it as a job, or using dtexec, but the error was something you'd see with an invalid switch more than anything.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • My take-away is to use config files (or tables) for parameter data 😀 Thanks for posting back.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (4/26/2011)


    My take-away is to use config files (or tables) for parameter data 😀 Thanks for posting back.

    That's my take away too... I thought using parameters to set variables would be a quick and easy solution... instead it turned into 6 hours of headache... the config file not ony fixed the problem, but simplified it. My boss asked me to pick up another directory as a one time run, I just editted the config file and re-ran the job. While the job was running I then reset the file to the default config and saved it. The next time it ran it was back to normal operations and I was very happy with that outcome.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

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