Move One or Two files from One Folder to an Archive Folder

  • 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

  • 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.

  • 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

  • Maybe this? http://stackoverflow.com/questions/6698075/how-do-i-move-files-to-an-archive-folder-after-the-files-have-been-processed

    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:

  • 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

  • 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 🙂

  • 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