May 21, 2013 at 9:15 am
Hi All,
I have a package that does the following:
1. Truncates the staging table (in db)
2. Data Flow Task that connects to a csv and transfers from csv to staging table
3. Execute SQL Task to update the staging table in order to transfer data from staging to real/actual table
It's pretty basic and I don't have any issues regarding those steps. However, there are two .xlsx files that are in a folder called Source_Original that need to be moved to a folder called Source_Archive. At the end of point 3 located above is where I am attempting the move using a ForEach Loop container and a File System Task. For the Loop Editor Variable Mappings, I use a blank Variable User::FileName index 0. In the Task Editor, the DestinationVariable is User::FileDestination and the Source Conncection is the variabel User::FileName. FileDestination is predefined according to our UNC Path.
At any given time in Source_Original folder there can be either one or two or both files called (and this is where I am lost):
1. NB_Can__US_Request_Annual.xlsx
2. NB_Can__US_Request_Qtly.xlsx
How can I set this up to move one, two, or both files? In the File System Task Editor, the SourceVariable cannot be explicitly defined as a connection and I need this part dynamic but am unsure how to go about this. Hopefully I am being clear.
Regards:
Mordred
Keep on Coding in the Free World
May 21, 2013 at 10:00 am
I'm just getting back into SSIS after a long hiatus, but can't you move the file you're working on as your step 2 finishes before you hit 3? Seems like you'd have the filename handy to use to move it while you're working with it.
again, shot in the dark, hth.
May 21, 2013 at 10:10 am
Hi Matthew,
I can't move the files after step 2 because the files aren't being referenced at this point. A csv is being connected to but not the Excel files. Before these steps start, the csv is populated via VBA from the two .xlsx workbooks in the folder.
At the moment, I am working on a "work-around" using VBA to move the files after the data has been transferred from the xlsx files into the csv.
Regards:
Mordred
Keep on Coding in the Free World
May 21, 2013 at 10:19 am
It's been a while and I'm just getting back into SSIS after a long hiatus, so if I'm misunderstanding what you're trying to do, please forgive me :w00t:
May 21, 2013 at 11:49 am
I decided to use VBA in Excel for each workbook who's data is being transferred into the csv. Thanks for the link and suggestions.
Regards:
Mordred
Keep on Coding in the Free World
May 21, 2013 at 11:58 am
No problem! It's been a year or so, but I found in the past that the best way of learning stuff is to jump in, so I thought getting into the forums would help.
If you can, can you post your solution? I'm sure someone else is going to hit this issue and need a workaround as well 🙂
May 21, 2013 at 12:13 pm
Well, I will provide my solution but like I've stated, it's VBA based but here it is:
Sub ProcessDataTransform()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''This process prepares a CSV file for database load.'''
'''Author - Mordred '''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'wbM - CBTransformForDBLoad.xlsm
'wbQ - NB_Can__US_Request_Qtly.xlsx
'wbA - NB_Can__US_Request_Annual.xlsx
Dim wbM As Workbook, wbO As Workbook
Dim shtM As Worksheet, shtO As Worksheet, shtA As Worksheet
Dim oC As Range, rngX As Range, dtRng As Range
Dim x As Long, y As Long, z As Long
Dim rCnt As Long, cCnt As Long
Dim sRow As Long
Dim dFol As String, nFName As String
'fPath - The file path
'sFile - The file to open
Dim fPath As String, sFile As String
''''''''''''''''''''''''''''''''''''''
'''newFileName - For Save As method'''
Dim newFileName As String
dFol = "\\EWPG-SERVICE-60\FA-BUSANALYEC\Economic Forecasts\Forecast_Source_Documents\Conference_Board\Source_Finalized\"
Set wbM = ThisWorkbook
Set shtM = wbM.Worksheets("Sheet1")
''''''''''''''''''''''''''''''''''''''''''''''''''
'''Clear the this workbook's Transform(T) sheet'''
shtM.Range(shtM.Cells(2, 1), shtM.Cells(shtM.Rows.Count, 5).End(xlUp).Offset(1, 0)).ClearContents
'''''''''''''''''''''''
'''Set the directory'''
fPath = ThisWorkbook.Path & "\"
sFile = Dir(fPath & "*.xlsx")
'''''''''''''''''''''''''
'''Stop screen flicker'''
Application.ScreenUpdating = False
Do While sFile <> ""
If sFile <> wbM.Name Then
Set wbO = Workbooks.Open(fPath & sFile)
'wbO.Windows(1).Visible = False
Set shtO = wbO.ActiveSheet
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Determine the starting row of the source workbook'''
If shtO.Cells(1, 1).Value = "Mnemonic:" Then
sRow = 2
Else
sRow = 3
End If
Set dtRng = shtO.Range(shtO.Cells(sRow, 1), shtO.Cells(shtO.Rows.Count, 1).End(xlUp))
cCnt = shtO.Cells(sRow - 1, shtO.Columns.Count).End(xlToLeft).Column - 1
rCnt = dtRng.Cells.Count
''''''''''''''''''''''''''''''''''''''''''''''
'''Place the date range in the wbM workbook'''
For x = 1 To cCnt
y = shtM.Cells(shtM.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Set rngX = shtM.Range(shtM.Cells(y, 1), shtM.Cells(y + rCnt - 1, 1))
rngX.Cells.Value = dtRng.Cells.Value
z = 0
For Each oC In rngX
If InStr(1, oC.Value, ".", vbTextCompare) > 0 Then
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Set the values from the .xlsx into the .csv file'''
oC.Offset(0, 1).Value = Right(oC.Value, InStr(oC.Value, ".") - 2)
oC.Offset(0, 1).Value = Replace(oC.Offset(0, 1).Value, ".", "")
oC.Value = Left(oC.Value, InStr(oC.Value, ".") - 1)
oC.Offset(0, 2).Value = shtO.Cells(sRow - 1, x + 1).Value
oC.Offset(0, 3).Value = shtO.Cells(sRow + z, x + 1).Value
With oC.Offset(0, 4)
.Value = Date
.NumberFormat = "yyyy/mm/dd"
End With
oC.Offset(0, 5).Value = oC.Offset(0, 2).Value & "Qtr" & oC.Offset(0, 4).Text
z = z + 1
Else
oC.Offset(0, 1).Value = 0
oC.Offset(0, 2).Value = shtO.Cells(sRow - 1, x + 1).Value
oC.Offset(0, 3).Value = shtO.Cells(sRow + z, x + 1).Value
With oC.Offset(0, 4)
.Value = Date
.NumberFormat = "yyyy/mm/dd"
End With
oC.Offset(0, 5).Value = oC.Offset(0, 2).Value & "Anl" & oC.Offset(0, 4).Text
z = z + 1
End If
Next oC
Next x
nFName = ""
''''''''''''''''''''''''''''''''''''''''''''
'''Set the new file name variable: nFName'''
nFName = DatePart("yyyy", Date) & "_" & DatePart("m", Date) & "_" & DatePart("d", Date) & "_" & wbO.Name
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Save the .xlsx files as a new name and in a different folder, '''
'''for archiving '''
wbO.SaveAs dFol & nFName
''''''''''''''''''''''''''''''''''''''''''''''''
'''Delete the .xlsx file in the source folder'''
Kill "\\EWPG-SERVICE-60\FA-BUSANALYEC\Economic Forecasts\Forecast_Source_Documents\Conference_Board\Source_Original\" & sFile
wbO.Windows(1).Visible = True
wbO.Windows(1).Close False
sFile = Dir
End If
Loop
For Each oC In shtM.Range(shtM.Cells(2, 4), shtM.Cells(shtM.Rows.Count, 4).End(xlUp))
If oC.Value = "" Then oC.Value = 0
Next oC
''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Set the new .csv file name variable: newFileName'''
newFileName = "CBETL"
''''''''''''''''''''''''''''''
'''Save as a .csv file type'''
wbM.SaveAs fPath & newFileName, xlCSV
wbM.Close False
Application.ScreenUpdating = True
End Subwhere the code that copies and deletes the file is:nFName = ""
''''''''''''''''''''''''''''''''''''''''''''
'''Set the new file name variable: nFName'''
nFName = DatePart("yyyy", Date) & "_" & DatePart("m", Date) & "_" & DatePart("d", Date) & "_" & wbO.Name
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''Save the .xlsx files as a new name and in a different folder, '''
'''for archiving '''
wbO.SaveAs dFol & nFName
''''''''''''''''''''''''''''''''''''''''''''''''
'''Delete the .xlsx file in the source folder'''
Kill "\\EWPG-SERVICE-60\FA-BUSANALYEC\Economic Forecasts\Forecast_Source_Documents\Conference_Board\Source_Original\" & sFile
wbO.Windows(1).Visible = True
wbO.Windows(1).Close False
sFile = Dir
Regards:
Mordred
Keep on Coding in the Free World
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply