May 27, 2009 at 11:19 am
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 🙂
November 25, 2009 at 6:39 am
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
November 30, 2009 at 4:39 am
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
December 6, 2011 at 12:47 pm
how could be the name of excel file is "c:\jenny\" in excel connection manager??? it should be something with the extension ".xls"???
December 6, 2011 at 12:48 pm
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"???
December 6, 2011 at 2:01 pm
"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.
December 6, 2011 at 2:12 pm
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...
December 19, 2011 at 7:40 am
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!!
December 19, 2011 at 7:45 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 46 through 53 (of 53 total)
You must be logged in to reply to this topic. Login to reply