November 2, 2005 at 4:40 am
I have been studying and attempting to use the sample code provided on SQLDTS.com for Looping, Importing, and Archiving (http://www.sqldts.com/default.aspx?246), but have been unable to figure out a couple of things:
1) The code refers to "gv_FileLocation" as the directory through which is looped. How do I set that to my actual directory (e.g. "C:/Data/")?
2) I don't understand the bit about the "Archive folder". Is this checking to make sure that the files I'm importing are backed-up somewhere? If so, do I need to set "gv_ArchiveLocation" to the backup directory (e.g. "C:/Data/Archive/")?
3) In the "Begin Loop" and "Loop Around" code, reference is made to "gv_FileFullName". I assume that for each iteration this is the Path & File Name of the file to be imported. However, I don't see this variable set to a value at any point during the process.
Your help is greatly appreciated!
November 2, 2005 at 8:02 am
If you don't have any objects highlighted, you can click on Package-->Properties. There is a Global Variables tab where you can set paths(gv_filelocation) and filenames, etc.
gv_filefullname seems to be set to one of the files in your list...so far, I'm not finding that it matters which one.
gv_archivelocation is where the package moves the file when it is done processing that file. The package only completes when there aren't any more files in the directory.
Michelle
November 2, 2005 at 12:56 pm
Michelle,
Thank you for your response. Thanks to you, I now have the routine mostly working. However, there is one lingering problem:
My understanding (and hope) is that this routine will result in the importation of each .txt into its own table in SQL Server (each newly created and populated table in SQL Server bearing the name of the .txt file (minus the ".txt")). However, in order for this to happen, the name of the .txt file must be passed to the "Transform Data Task" and appear in the "Table/Veiw:" field on the "Source" tab (I think). How can this be done?
Is it possible to set "Table/View:" to a Global Variable?
Thank you,
Ben.
November 2, 2005 at 1:07 pm
Hi Ben -
Unfortunately, I haven't had to use the loop in this way. I want all my files into one table. But I do want to know which file the data comes from, so I add a field(dataname) to my SQL table and use an ActiveX transformation to pull the gv_filefullname value into my table.
Function Main()
DTSDestination("dataname") = DTSGlobalVariables("gv_FileFullName").Value
Main = DTSTransformStat_OK
End Function
Would something like that help?
Michelle
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply