none
SSIS with Excel sheets RRS feed

  • Question

  • I have created ssis package to loda data from excel to database. My problem is Those excel files proteced with password( I know the password) at the minute i am removing password mannually. Is there any way to automate in ssis, Because i have 200 excel files.

    pulikondas

    Friday, October 26, 2012 12:31 PM

Answers

  • I have created ssis package to loda data from excel to database. My problem is Those excel files proteced with password( I know the password) at the minute i am removing password mannually. Is there any way to automate in ssis, Because i have 200 excel files.

    pulikondas

    Add a new Script Task and in there this code:

    Dim excelapp As Excel.Application
    Dim excelwrk As Excel.Workbook
    
    Sub test()
    Set excelapp = GetObject(, "Excel.Application")
    
    Set excelwrk = excelapp.Workbooks.Open("c:\test.xls")
    excelwrk.SaveAs "c:\test3.xls", , "passwordhere"
    
    End Sub 

    Obviously you need to replace the file name with a package variable filled in the the ForEach loop (for example, even though you have just one file, it is just a "cheap" method of initializing a package variable with a file name and if needed its path, too)


    Arthur My Blog

    • Marked as answer by Eileen Zhao Friday, November 2, 2012 2:02 AM
    Friday, October 26, 2012 6:00 PM
    Moderator

All replies

  • Take a look here for a couple work arounds

    http://www.sqlservercentral.com/Forums/Topic885800-148-1.aspx


    Chuck Pedretti | Magenic – North Region | magenic.com

    Friday, October 26, 2012 1:54 PM
  • I have created ssis package to loda data from excel to database. My problem is Those excel files proteced with password( I know the password) at the minute i am removing password mannually. Is there any way to automate in ssis, Because i have 200 excel files.

    pulikondas

    Add a new Script Task and in there this code:

    Dim excelapp As Excel.Application
    Dim excelwrk As Excel.Workbook
    
    Sub test()
    Set excelapp = GetObject(, "Excel.Application")
    
    Set excelwrk = excelapp.Workbooks.Open("c:\test.xls")
    excelwrk.SaveAs "c:\test3.xls", , "passwordhere"
    
    End Sub 

    Obviously you need to replace the file name with a package variable filled in the the ForEach loop (for example, even though you have just one file, it is just a "cheap" method of initializing a package variable with a file name and if needed its path, too)


    Arthur My Blog

    • Marked as answer by Eileen Zhao Friday, November 2, 2012 2:02 AM
    Friday, October 26, 2012 6:00 PM
    Moderator