Use script task to dynamically get Excel file as source file connection

  • Hi,

    I was little puzzled to figure out that having for each loop container, it has to be servername property need to be set instead of connection string or any other property 🙂

  • I have a similar issue I am trying to resolve. I have some sample code in vb6 to do this but not sure how to do this in SSIS. If someone can walk me through this please let me know. I have code that looks through a mapped networkrive in a given folder name. The code uses a filesystemobject to find the latest folder creation date using criteria of: beginning foldername isnumeric and the end of folder name = "BOMS" (bill of manufacturing statement). Then the sheet name it needs to import is wb.Worksheets("MBOM"). Then the code imports each excel file and skips any file with the name fullup.xls. Is there an easier way to do this? This process takes 3.25 hours to complete 14 workbooks with approx avg 22k records each!

    Set_Sys_Con_String

    Set_Con_String "Dash_Board"

    Dim rs As New ADODB.Recordset

    Dim sSQL As String

    Set rs = New ADODB.Recordset

    sSQL = "Delete from tbl_TCE_BOM"

    rs.Open sSQL, dbCN, adOpenDynamic, adLockOptimistic

    ' rs.Close

    Set rs = Nothing

    On Error GoTo Trap_err

    Set gxlApp = GetObject(, "Excel.Application")

    Call LookThruFolders

    Dim a1 As Object, af As Object

    Set a1 = CreateObject("Scripting.FileSystemObject")

    Set af = a1.GetFolder(gstrLatestBOMFolder)

    For Each a1 In af.Files

    If UCase(a1.Name) <> "FULLUP.XLS" Then

    get_Excel_Doc a1.Name

    End If

    Next a1

    Exit Sub

    Trap_err:

    If Err.Number = 429 Then

    Set gxlApp = CreateObject("Excel.Application")

    Resume

    Else

    MsgBox "Error: " + Err.Number + " " + Err.Description, vbCritical

    End If

    End If

    End Sub

    Private Sub get_Excel_Doc(strFileName As String)

    Dim a1, af, afiles, a As Integer

    Dim lRecCnt As Long

    'Dim xlapp As Excel.Application

    Dim wb As Workbook

    Dim ws As Worksheet

    Dim lCnt As Integer

    Dim lLinkID As String

    Dim sSQL As String

    Dim rs As New ADODB.Recordset

    Dim cn

    Dim lMaxRec As Long

    Dim sShip As String

    On Error GoTo ErrorTrap

    Status_Bar(0).Text = "Analyzing File " + gstrLatestBOMFolder + "\" + strFileName + " ..."

    Status_Bar(0).Refresh

    Status_Bar(0).Visible = True

    Status_Bar(1).Visible = False

    Set a1 = CreateObject("Scripting.FileSystemObject")

    Set wb = gxlApp.Workbooks.Open(gstrLatestBOMFolder + "\" + strFileName)

    Set ws = wb.Worksheets("MBOM")

    lCnt = 2

    Do While lCnt < 200000

    If Len(Trim$(ws.Cells(lCnt, 21).Value)) = 0 Then

    lMaxRec = lCnt

    Exit Do

    End If

    Me.Status_Bar(0).Text = gstrLatestBOMFolder + "\" + strFileName + " - Evaluating File Records: " & lCnt & " Please Wait ..."

    Me.Status_Bar(0).Refresh

    lCnt = lCnt + 1

    Loop

    ' check to see if XX is on the second column second row and if so start at 4th row for adding to db

    If ws.Cells(2, 2).Value = "XX" Then

    lCnt = 4

    Else

    lCnt = 2

    End If

    sShip = ws.Cells(lCnt, 21).Value

    Select Case Len(ws.Cells(lCnt, 21).Value)

    Case 2

    sShip = "000" & ws.Cells(lCnt, 21).Value

    Case 3

    sShip = "00" & ws.Cells(lCnt, 21).Value

    Case 4

    sShip = "0" & ws.Cells(lCnt, 21).Value

    Case 5

    sShip = ws.Cells(lCnt, 21).Value

    End Select

    Set rs = New ADODB.Recordset

    sSQL = "SELECT Indenture, Part, Rev, Status, FinSeq, InstanceName, InstanceNumber, BOMLine, Qty, RevMaturityLevel, " & _

    " InstanceMaturityLevel, SourceCD, SpecialSourceCD, ExtendedEffectivity, ICEffectivity, NexAssembly, NexAssemblyRev, NexAssemblyPlan, NexAssemblyPlanRev, " & _

    " ConfigurationRule, Ship" & _

    " From tbl_TCE_BOM"

    rs.Open sSQL, dbCN, adOpenDynamic, adLockOptimistic

    With rs

    Do While lCnt <= lMaxRec

    'If Len(ws.Cells(lCnt, 6).Value) = 0 Then Exit Do

    .AddNew

    .Fields("Indenture") = ws.Cells(lCnt, 1).Value & ""

    .Fields("Part") = ws.Cells(lCnt, 2).Value & ""

    .Fields("Rev") = ws.Cells(lCnt, 3).Value & ""

    .Fields("Status") = ws.Cells(lCnt, 4).Value & ""

    .Fields("FinSeq") = ws.Cells(lCnt, 5).Value & ""

    .Fields("InstanceName") = ws.Cells(lCnt, 6).Value & ""

    .Fields("InstanceNumber") = ws.Cells(lCnt, 7).Value & ""

    .Fields("BOMLine") = ws.Cells(lCnt, 8).Value & ""

    .Fields("Qty") = ws.Cells(lCnt, 9).Value & ""

    .Fields("RevMaturityLevel") = ws.Cells(lCnt, 10).Value & ""

    .Fields("InstanceMaturityLevel") = ws.Cells(lCnt, 11).Value & ""

    .Fields("SourceCD") = ws.Cells(lCnt, 12).Value & ""

    .Fields("SpecialSourceCD") = ws.Cells(lCnt, 13).Value & ""

    .Fields("ExtendedEffectivity") = ws.Cells(lCnt, 14).Value & ""

    .Fields("ICEffectivity") = ws.Cells(lCnt, 15).Value & ""

    .Fields("NexAssembly") = ws.Cells(lCnt, 16).Value & ""

    .Fields("NexAssemblyRev") = ws.Cells(lCnt, 17).Value & ""

    .Fields("NexAssemblyPlan") = ws.Cells(lCnt, 18).Value & ""

    .Fields("NexAssemblyPlanRev") = ws.Cells(lCnt, 19).Value & ""

    .Fields("ConfigurationRule") = ws.Cells(lCnt, 20).Value & ""

    .Fields("Ship") = sShip

    .Update

    Status_Bar(0).Text = gstrLatestBOMFolder + "\" + strFileName + " - Importing Records : " & lCnt & " of " & lMaxRec

    Status_Bar(0).Refresh

    lCnt = lCnt + 1

    Loop

    End With

    rs.Close

    Set rs = Nothing

    'InvalidWorkbook:

    ' wb.Close False

    ' Set wb = Nothing

    ' Status_Bar(0).Visible = False

    ' Status_Bar(1).Text = "Import Complete!"

    ' Status_Bar(1).Refresh

    ' Status_Bar(1).Visible = True

    Exit Sub

    ErrorTrap:

    If Err.Number = -2147217873 Then

    MsgBox "The BOM you are trying to import already exists. Please try again.", , "Duplicate Record"

    Exit Sub

    Else

    MsgBox "Error: " & Err.Description

    ' Stop

    Resume Next

    End If

    End Sub

    Sub LookThruFolders()

    Dim X As Integer

    Dim lCnt As Long

    Dim sSQL As String

    Dim rs As New ADODB.Recordset

    Dim sEng_NC_Rev As String

    Dim cn

    Dim fs, f, f1, filelist()

    Dim i As Integer, j As Integer

    Dim sFolderName As String

    Dim idatelen As Integer

    Dim LatestBOMFolderDate As Date

    Dim LatestBOMFolder As String

    Dim OldBOMFolderDate As Date

    Dim OldBOMFolder As String

    i = 0

    On Error Resume Next

    lCnt = 0

    i = 1

    j = 1

    Set fs = CreateObject("Scripting.FileSystemObject")

    Set f = fs.GetFolder("Z:\RALPH BOMS\")

    Me.Status_Bar(0).Text = "Searching through folder \" + f.Name + "\ for the latest files. Please wait..."

    Me.Refresh

    For Each f1 In f.SubFolders

    DoEvents

    sFolderName = Mid(f1, InStrRev(f1, "\") + 1)

    If UCase(Right(sFolderName, 4)) = "BOMS" Then

    If IsNumeric(Left(sFolderName, 1)) Then

    OldBOMFolderDate = f1.DateCreated

    If OldBOMFolderDate > LatestBOMFolderDate Then

    LatestBOMFolderDate = OldBOMFolderDate

    gstrLatestBOMFolder = "Z:\RALPH BOMS\" & f1.Name

    gstrLocalFolderName = "C:\Temp\" + f1.Name

    End If

    End If

    End If

    Next

    Me.Status_Bar(0).Text = "The most currect File Folder is: " + gstrLatestBOMFolder

    Dim fs1 As New Scripting.FileSystemObject

    On Error GoTo Trap_err:

    ' Check to see if folder exists if not create it.

    If Not fs1.FolderExists(gstrLocalFolderName) Then

    Me.Status_Bar(0).Text = "Creating local folder: " + gstrLocalFolderName

    fs1.CreateFolder (gstrLocalFolderName)

    End If

    Me.Status_Bar(0).Text = "Copying Network Folder contents to local harddrive for processing... "

    fs1.CopyFolder gstrLatestBOMFolder, gstrLocalFolderName, True

    gstrLatestBOMFolder = gstrLocalFolderName

    Me.Refresh

    Exit Sub

    Trap_err:

    If Err.Number = 76 Then

    fs1.CreateFolder gstrLocalFolderName

    Resume

    Else

    MsgBox "Error: " + Err.Number + Err.Description

    End If

    End Sub

  • Hi,

    Could you let please tell the version of DTS/SSIS you are using. If you are using SSIS 2005/2008. There lot more easier way to do this. You could avoid more of the scripts. There is one more thing, not sure if excel files are on the server, meaning local to system where SSIS/DTS are running will have little enhancement in execution time. For any case let me know version of ETL you are using.

    Regards,

    Raj

  • how could be the name of excel file is "c:\jenny\" in excel connection manager??? it should be something with the extension ".xls"???

  • mya (9/25/2008)


    Jenny,

    Look at this package. All you need to do is change your database connection,variables and its expression.

    1. change connection to your database

    2. change variables values : SourcePath,Dstpath and FileName(File name can be anything with .xls)

    ConnectionString for your excel file connection manage will be like this

    3. change excel connection manager to

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourcePath]+ @[User::FileName] +";Extended Properties=\"Excel 8.0;HDR=YES\";"

    4. For loop collection, choose name and extenion only.

    Good Luck.

    how could be the name of excel file is "c:\jenny\" in excel connection manager??? it should be something with the extension ".xls"???

  • "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourcePath]+ @[User::FileName] +";Extended Properties=\"Excel 8.0;HDR=YES\";"

    The excel connection string is overwritten. The above script is set in expressions.

  • ayemya (12/6/2011)


    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::SourcePath]+ @[User::FileName] +";Extended Properties=\"Excel 8.0;HDR=YES\";"

    The excel connection string is overwritten. The above script is set in expressions.

    ya but whn am configuring my excel source and excel connection manager with the above expression am getting an error when i reopen my excel source...

  • Hi Jack,

    I have the same scenario but im using .txt files, i have loaded the files into the table using the foreach loop and i have variable for the filename but im not able to understand how to pick it up and insert its value into the target table. can you give me some direction on that please!!

  • Add a derived column in your dataflow and set it to the value of the variable containing the file name.

    Then map the new column to your database column as usual.

    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

Viewing 9 posts - 46 through 53 (of 53 total)

You must be logged in to reply to this topic. Login to reply