Re: Best way to integrate all filenames in a directory with SQL Recordset?
- From: Peter A. Schott <pschott@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 02 Aug 2005 16:46:36 -0500
Ugh. So I'm taking it I can't pump those names into a RowSet GV or
similar....
In any case, the Constants can be read from SQL as well - I'm getting them
from a query in the first place. They just don't change much. The big
changes are the file names.
Actual filename value transform from old to new is more like Filename.1 ->
Filename.001. I've got that covered in a separate ActiveX script.
Basic steps in my proc:
* Get all new items in my table to upload. Put in Recordset GV.
* For each item, copy all associated files from original directory to a
"Process" directory.
* During copy, rename each item as above: Filename.1 -> Filename.001, etc.
* Generate list from database of all filenames with the various attributes
such as folder, filename, ItemID, etc. and output to file.
^
This is where the process breaks down. Our 3rd party app doesn't always store
those filename values correctly. I end up getting incomplete lists or
mismatch lists if something goes wrong. e.g. I'll have file.52 deleted and
replaced with file.65. Table still shows 52 as active (or doesn't show it at
all). I completely miss file.65.
* Loop back to next ItemID and begin again.
* If no more records found, exit process gracefully.
I have more than 4 attributes, but this gives a general idea of how the
process works right now.
I will see what I can do to add another loop mid-stream to catch all file
names, but I am hoping there's a better way with just ActiveX scripting or
similar. (So wish we had ability to use SSIS right now....)
TIA.
-Pete
"Allan Mitchell" <allan@xxxxxxxxxxxxxxxxxx> wrote:
> So you want to go through a folder which you know so will be held in a GV
> You want to loop through the files in that directory
> You want to add 2 * constants
>
> Your destination I can only presume has 4 attributes
>
> Here's an idea
>
> Loop over the files in the directory
> read the name of the file into a global variable.
> using an ExecuteSQL task load the GV into a table
>
> Now for the sending to the destination
>
> Create a datapump between the "file name" table and your destination. That
> will leave 3 attributes on the destination to be populated.
> In the transformations tab select those three destination columns and create
> a new active script transform. In there it will look something like
>
> DTSDestination("Column 1") = DTSGlobalVariables("Name of file").Value
> DTSDestination("Column 2") = "Constant 1"
> DTSDestination("Column 3") = "Constant 2"
.
- References:
- Best way to integrate all filenames in a directory with SQL Recordset?
- From: Peter A . Schott
- Re: Best way to integrate all filenames in a directory with SQL Recordset?
- From: Allan Mitchell
- Best way to integrate all filenames in a directory with SQL Recordset?
- Prev by Date: Re: Renaming all DTS pacakges on a server
- Next by Date: DTS.Task
- Previous by thread: Re: Best way to integrate all filenames in a directory with SQL Recordset?
- Next by thread: DTS Windows Scheduling error only when logged off
- Index(es):
Relevant Pages
|
