I have a need to use some pieces of a SQL Recordset and combine those with
each filename in a folder. I then need to output each of those combinations
to a file. I know I can do a FOR loop to get all filenames in a folder and I
can get the information from SQL without any issues. My lack of knowledge
here is in how to combine the two.

Anyone done this before? Any ideas on how to get started?



Examples:
C:\MyFolder\
1.jpg
2.jpg
3.jpg

Desired output recordset:
Folder, OriginalFileName, NewFileName, Constant1, Constant2
C:\MyFolder, 1.jpg, 001.jpg, 12345, 54321
C:\MyFolder, 2.jpg, 002.jpg, 12345, 54321
C:\MyFolder, 3.jpg, 003.jpg, 12345, 54321


TIA.

-Pete

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"




--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Peter A. Schott" <pschott (AT) no (DOT) spamm.hear.drivefinancial.com> wrote in
message news:ufive1pbqdc6v36d6ertfs2k5dg32ba6jp (AT) 4ax (DOT) com...

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 (AT) no-spam (DOT) sqldts.com> wrote: