Importing multiple text files using same package

• J Bagwell

  Mr or Mrs. 500

  Points: 514

  More actions

  October 20, 2004 at 2:02 pm

  #63460

  I have to generate a quarterly report that is based on 12 different data sources.  Eventually, it will become part of a data warehouse, but right now, it is simply a report. 

  For several of these data sources, I get weekly or monthly files.  So, I have DTS packages that import each particular file format. 

  But I'd like to be able to run a DTS package for every .txt file in a particular folder without having to manually change the file name in the connection properties every time. 

  Is there a more elegant way to do this?

  Thanks for your help.

  Jana

  ---

  J. Bagwell

  UVA Health System
  

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  October 20, 2004 at 6:20 pm

  #527385

  Hi Jana

  If the filenames are always the same, why not just create 12 separate imports that run one after the other as part of the same DTS package and use workflow to decide what happens if any of the imports fails?

  Regards

  Phil

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• philcart

  SSC-Forever

  Points: 47796

  More actions

  October 21, 2004 at 1:22 am

  #527409

  Using seperate packages can be very useful. It allows a greater flexibility in whats run and when. It can also be much easier to track down problems.

  Alternatively, if you're stuck on the single package idea, take a look at this article http://www.sqldts.com/default.aspx?246

  --------------------
  Colt 45 - the original point and click interface

• ZenDada

  SSCarpal Tunnel

  Points: 4637

  More actions

  October 21, 2004 at 10:27 am

  #527495

  You can use the File System Object in an ActiveX script to loop through all files in a folder.  You can capture the file names and assign them to global variables that include the full path.  Then use the Dynamic Properties Task to assign these globals to the Data Source properties of your connections.

  [font="Courier New"]ZenDada[/font]

• fperkins

  Ten Centuries

  Points: 1029

  More actions

  October 21, 2004 at 2:01 pm

  #527538

  Sample script in my reply to

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=142802

  + the dynamic task as suggested by Jules. I use a branch package to determine which file type and which process to call to import it.

• sasa acimov

  Newbie

  Points: 3

  More actions

  September 26, 2005 at 2:42 pm

  #593135

  Read your sample code - just curious, how would you handle if you had 3 subfolders within the master folder and each subfolder contained numerous files with different names but all names ended in 1 of 3 name endins eg. nameEnd1, nameEnd2 and nameEnd3?

  I've set up a vbsript that loops within one DTS package that has 9 different text source connections and 9 different transformations to use as templates for the 9 different types of files (each subfolder has 3 types of name endings) I wish to import on regular basis.  The piece I haven't set up is passing the various file names to the appropriate template (1 to 9) based on the file name end (eg. nameEnd3), - do you have any ideas?

  Thanks.

• fperkins

  Ten Centuries

  Points: 1029

  More actions

  September 26, 2005 at 4:49 pm

  #593150

  -- set the root and subfolders variables

  ' Get the from (download) directory

  Set sRootFolder = oFso.GetFolder(DTSGlobalVariables("FromDirectory").Value)

  ' Then get the list of SubFolders beneath this root folder

  Set sSubFolders = sRootFolder.SubFolders

  -- the for each returns each file in each subfolder

  ' Loop through each subfolder

    For Each sSubFolders in sSubFolders

            ' Get the list of files in this subfolder

            Set sFiles =  sSubFolders.Files

            move  sFiles.Path, sFiles.Name 

    Next

   

  -- our move process moves the files from the download area to a workarea directory. this is done to limit overwrites and multiple processes attempting to work with the same file

  -- part of the move is writing an entry into a log file

   ' write an entry into ImportExportLog

       objCommand.CommandText = "Insert Into ImportExportLog ( OrigFileLocation)"

       objCommand.CommandText = objCommand.CommandText & "Values ( '" & outFileName & "')"

       objCommand.Execute

  -- then we do a loop reading the unprocessed files calling the correct package used to do the import for the specific file

  rs.Open "SELECT Spec_Name FROM ImportExportLog WHERE DateProcessed IS NULL ORDER BY OrigFileLocation ", cnDB, 1, 3

   if not rs.eof and not rs.bof then

      Do while not rs.eof

     Select Case UCase(Trim(rs("Spec_Name")))

      Case "HOST"