May 27, 2011 at 3:31 pm
I have a couple issues seemingly related to a point regarding outputting a dynamic filename.
Issue #1
I have a package variable varFileLocation. The value of this is c:\myfolder\Data_
I have a foreachloop container that has a script task, in it I have the code below to generate my flatfilename.
On the first iteration through my file name is created properly it comes out as Data_tstTableName_tstLocationCode_05272011053021.txt
on the following iteration thru the filename adds the appending names to it.
For instance the second and third files are named
Data_tstTableName_tstBaseCode_05272011053021.txttstLocationCode_05272011053021.txt
and the next one adds more
Data_tstTableName_tstBaseCode_05272011053021.txttstLocationCode_05272011053021.txttstLocationCode_05272011053021.txt
each iteration it runs it adds more to the filename than should be there. For some reason the variable is not being cleared.
I tried clearing the variable Dts.Variables("User::varFileLocation").Value="" at the end of the script but then it did not add the static part(c:\myfolder\Data_) of the variable back on the next time through and failed completely.
script task code
Public Sub Main()
Dts.Variables("User::varFileLocation").Value = Dts.Variables("User::varFileLocation").Value.ToString + "_" + "tstTableName_" _
+ "tstLocationCode" + "_" + CStr(Format(Now(), "MMddyyyyHHMMSS") + ".txt")
Dts.TaskResult = Dts.Results.Success
End Sub
-------------
Issue #2 in the same script above I actually want my filename to use 3 variables to make one filename varfilelocation + varTableName + varLocationcode
The varLocationcode comes from a object variable that the iteration of the forloop is based on. So for each location it passes that variable to sql parameter.
That part works I get the right amount of files generated for the number of locations I have,but I can't pass that same variable into my script. I tried adding varlocationcode as a readonly parameter and readwrite parameter and it failed both times.
I got an error saying that either the variable was locked or it did not exist. I used
Dts.Variables("User::varLocationCode").Value.ToString in my initial script
-----------------------------
Issue #3 along the same lines I want the tablename to be part of my filename as well.
Problem is all of my queries are stored procs.
I am wondering if I could add an output parameter in my stored proc and that would send the tablename to my parameters for my sqlconnection the same way
I added an input paramter to my stored proc in SSIS. Or if there is some other way to get table name into my filename??
Lastly the "tstTableName" and "tstLocationCode" are just place holders until I get the above issues worked out.
Thanks!!
May 30, 2011 at 12:01 am
Regarding issue #1: your code appends data to the variable for every iteration. So what did you expect?
Store the fixed part in another variable, and create each time the varFileLocation:
varFileLocation = varStatic + tstTableName + tstLocationCode + timestamp
At the end of the iteration, set varFileLocation back to the empty string.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2011 at 6:52 am
Koen Verbeeck (5/30/2011)
Regarding issue #1: your code appends data to the variable for every iteration. So what did you expect?Store the fixed part in another variable, and create each time the varFileLocation:
varFileLocation = varStatic + tstTableName + tstLocationCode + timestamp
At the end of the iteration, set varFileLocation back to the empty string.
That crossed my mind on the way home after I wrote this. I'll try that tomorrow.
Anyone else have an idea on the other two issues??
May 31, 2011 at 9:47 am
I resolved my first two issues, one with the extra names being appended to my filename. I also solved the issue with the Location Parameter not being set on my filename. That issue was I had that variable set to readwrite and not readonly. I only needed it to be readonly.
The final issue I can't seem to resolve is getting the tablename into my filename.
I added a @tablename output parameter to my stored procedure.
added that outparamter to my ole source my doing this
exec myprocname ?,? OUTPUT
mapped those parameters to my variables (the first param is input the second output).
Everything run fine, except I am getting a blank value back for the table name.
I did set @tablename='MyTable' in my stored procedure, but I think (guessing) my output parameter is being cleared before it is set to my varTableName.
So in my filename it actually add a blank space where my tablename variable is.
Any ideas?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply