April 25, 2011 at 10:16 am
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.
April 25, 2011 at 12:19 pm
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.
April 25, 2011 at 2:06 pm
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
April 26, 2011 at 7:34 am
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.
April 26, 2011 at 9:39 am
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
April 26, 2011 at 9:49 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply