Re: Best way to integrate all filenames in a directory with SQL Recordset?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:ufive1pbqdc6v36d6ertfs2k5dg32ba6jp@xxxxxxxxxx
>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


.

Relevant Pages

  • Re: BTS 2006 FTP Adapter spooling permissions
    ... Did you say you have 2 FTP send port pointing to the same FTP location? ... folder, not to mention same output file. ... when move from"spooling folder to destination folder", ... TemporaryFolder: destfolder/tmp ...
    (microsoft.public.biztalk.general)
  • File compare script running slowly.
    ... ' This procedure replicates between the source and the destination ... ' directories at the folder level. ... Dim aFolderArraySource ...
    (microsoft.public.scripting.vbscript)
  • Re: Checking a folder again after it has been emptied
    ... that while loop. ... allowing additional files in that folder to be moved and now with your ... #sleep timer and number of files ... closedir PRV_SRC_1; ...
    (perl.beginners)
  • Re: Outlook 2003 vba macro
    ... Don't move or delete items in a For Each loop! ... 'Move the email Item to the backup folder ... Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers ... > Dim objApp As Application ...
    (microsoft.public.outlook.program_vba)
  • Re: Help with a long running process?
    ... Before the loop enters its initial cicle, it will first have to grab ... all the files from all sub folders off the base path you specified. ... String)) or return it as an array. ... consider loading the files folder by folder: ...
    (microsoft.public.dotnet.languages.vb)