February 17, 2010 at 12:56 pm
I have the following DTEXEC command and it works. My issue is that the Variable "VarFileName" is different everytime this BATCH file job is run. I've tried replacing "MyNewFileName.txt" with a DOS Variable name &&varname, but nothing works.
D:\>dtexec /SQL "\LoadIt_Package" /SERVER MyTestServer /MAXCONCURRENT " -1 " /SET \Package.Variables[VarFileName].Value;"MyNewFileName.txt"
/CHECKPOINTING OFF /REPORTING V > D:\log.txt
How do I run the above command in a BAT file and use a DOS Variable as the VarFileName SSIS variable?
February 17, 2010 at 1:43 pm
In a CMD file I often use %1 to handle that.. Let me show you:
ExecDT.cmd contents:
dtexec /SQL "\LoadIt_Package" /SERVER MyTestServer /MAXCONCURRENT " -1 " /SET \Package.Variables[VarFileName].Value;"%1" /CHECKPOINTING OFF /REPORTING V > D:\log.txt
Call to ExecDT.cmd
ExecDT.cmd filename.ext
OR
ExecDT.cmd "C:\File Path\filename.ext"
Should work.
CEWII
February 17, 2010 at 1:54 pm
Elliott, I'm sure it's no surprise to you that it worked!
I was hoping I could do ALL the processing in one BAT file. I guess I'll have to split things up.
thanks soooooo much!
February 17, 2010 at 2:09 pm
You may be able to do it in one..
Why don't you lay out the process with some detail and we can see.
CEWII
February 17, 2010 at 2:13 pm
I have a bat file that places a File-Name in a VARIABLE (OrgFileName)
set OrgFileName=
dir /B D:\test\test*.xls
echo %OrgFileName%
I then want to use the DOS variable %OrgFileName% in the DTEXEC SET Command.
Your solution works if I have a HARD-CODED name, but not when I try to pass it a VARIABLE
February 17, 2010 at 2:47 pm
This seems a little convoluted to me.. Based on what you showed you seem to be calling the SSIS package once for each XLS file. Is that right?
If so there is a MUCH better way to do this with a For-Each-Loop in an SSIS package.
Please enlighten me as to how your process works.
CEWII
February 17, 2010 at 2:55 pm
Sorry, Elliott.
I'm just learning SSIS and know very little so far.
My Main SSIS Package works fine.
But, I also need to update another table with the FileName, date/time etc of when the Main process started.
That's my issue now.
I think my real problem with that is my DOS code for setting the FileName I'm currently working on does not work. It's wrong.
Anyone know how to set a DOS VARIABLE with a filename?
I can do this command that will pipe the filename of the file I'm interested in, into another TEXT file.
dir /B D:\test\filename*.xls > D:\test\MyFileName.txt
I need this same info placed into a DOS Variable so that I can then pass that DOS Variable to my small SSIS (execute t-sql statement task) that will INSERT a record.
This process works fine when I hard-code the FILENAME, but not with variables.
February 17, 2010 at 3:10 pm
i believe i have it.
For /F "tokens=*" %%A in ('dir /B D:\test\filename*.xls') Do Set orgfilename=%%A
then when i call the CMD file that contains DTEXEC using %orgfilename% it works!
February 17, 2010 at 3:12 pm
So you have two SSIS packages. One does little more than write the filename with path into a table and the other does the real work.
Might I ask a silly question? What is the underlying rationale why you aren't doing both in the same package?
Also, it seems the DOS command is generating a complete list of XML files and trying to put the whole list in the environmental variable..
Your main package may be just fine but the whole process seems needlessly complex, which is why I'm asking the questions I am. What I want FOR you is a process that is easy to maintain with as few moving parts as possble. And right now I am getting Rube Goldberg contraption vibes..
CEWII
February 17, 2010 at 4:00 pm
The DOS command simply puts ONE filename into a DOS Variable.
Like I said, I'm very new to SSIS. When I learn further maybe I can combine the 2 processes.
February 17, 2010 at 7:43 pm
Ok, when you are ready. Let us know.
CEWII
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply