Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 20, 2001

Import all files in a directory (Multi Step)

By Darren Green

This example shows how you can use a single DTS package to import multiple files from a given directory. For this, it is assumed that all files have the same format, and that all files are imported into the same table. Feel free to expand on this to fit your own solution.
(This example uses the Microsoft FileSystemObject)

The package has four tasks and two connections -
Package Objects

1: Create Table [master].[dbo].[ImportTest] Task (Execute SQL Task)
This task will drop an existing version of Import Test and recreate it.

2: Get First File (Active Script Task)
This reads the first file name from the directory and updates the DataSource property of the text file connection (Connection 1).

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()

    ' Declare FSO Related Variables
    Dim sFolder
    Dim fso
    Dim fsoFolder
    Dim fsoFile
    Dim sFileName


    ' Import Folder
    sFolder = DTSGlobalVariables("ImportFolder")

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(sFolder)
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(sFolder)

    For Each fsoFile in fsoFolder.Files
        ' Get first filenme
        sFileName = sFolder & fsoFile.Name
        Exit For
    Next

    'Declare Variables
    Dim oPKG
    Dim oConnection

    ' Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Get Source Connection Object
    Set oConnection = oPKG.Connections("Connection 1")

    ' Set new Filename
    oConnection.DataSource = sFileName

    Main = DTSTaskExecResult_Success
End Function

3: Connection 1 (Text File Source Connection)
Obviously the source connection.

4: Copy Data from ImportTest to [master].[dbo].[ImportTest] Task (Data Transformation Task)
This uses simple Copy Column transformations to import the data. One important point property is set under the tasks Workflow Properties, Options tab:
Workflow Properties - Execution
The "Close connection on completion" option must be checked. If not then each loop of the package imports the same file, as the connection will not automatically refresh itself after setting the new filename.

5: Connection 2 (SQL Server OLE DB Connection)
A simple connection to the local server, using trusted authentication

6: Get Next File Loop (Active Script Task)
This is the task where we get the name of the next file to import, and change the step ExecutionStatus property of the import Transformation Task back to waiting. This causes a loop in the package workflow, and imports the next file.

To reference the correct step in the steps collection you can either supply the ordinal position or the step name as below. To easily find the name of your step, right click the task and look at the Options sheet for Workflow Properties. The name is shown there.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Option Explicit

Function Main()
    ' Declare FSO Related Variables
    Dim sFolder
    Dim fso
    Dim fsoFolder
    Dim fsoFilesCollection
    Dim fsoFile
    Dim sFileName
    'Declare PKG Variables
    Dim oPKG
    Dim oConnection

    ' Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Get Source Connection Object
    Set oConnection = oPKG.Connections("Connection 1")

    ' Import Folder read from global variable
    sFolder = DTSGlobalVariables("ImportFolder")

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(sFolder)

    Dim bFound ' Used to exclude files previously exported
    bFound = False

    For Each fsoFile in fsoFolder.Files
        sFileName = sFolder & fsoFile.Name
        If oConnection.DataSource = sFileName Then
            bFound = True
        ElseIf bFound = True Then ' New file found
            oConnection.DataSource = sFileName
            ' Set Pump Step to waiting
            oPKG.Steps("Copy Data from ImportTest to [master].[dbo].[ImportTest] Step") _
				.ExecutionStatus = DTSStepExecStat_Waiting
            Exit For
        End If
    Next

    Main = DTSTaskExecResult_Success
End Function

For some more information about changing filenames and other connection properties see How to dynamically change...Connections.

The sample package can be downloaded below. To use -

  • Extract archive
  • Load package
  • Check the server name of Connection 2, currently set to local. If you change it reject the option to reset the transformations.
  • Create directory "C:\DemoImport\" and place in it "ImportTest.txt". Remember that DTS paths are local to the machine they are executed from, not the server.
  • Make multiple copies of "ImportTest.txt", all in "C:\DemoImport\"
  • Execute [DTSDirDemo-Step] package
A table called [ImportTest] will be created in master DB. There are ten records in "ImportTest.txt", so the record count of [ImportTest] will be 10 X Number of "ImportTest.txt" copies in "C:\DemoImport\".

Files contained in DTSDirDemoStep.zip -

  • DTSDirDemo-Step.dts
  • ImportTest.txt

    Download DTSDirDemoStep sample.



  • MS SQL Archives

    Comment and Contribute

     


    (Maximum characters: 1200). You have characters left.

     

     




    Latest Forum Threads
    MS SQL Forum
    Topic By Replies Updated
    SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
    Need help changing table contents nkawtg 1 August 17th, 03:02 AM
    SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
    SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















    Thanks for your registration, follow us on our social networks to keep up-to-date