Importing multiple text files using same package
-
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
-
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
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
-
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]
-
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.
-
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.
-
-- 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"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply