带有 Excel 表格的 SSIS [英] SSIS with Excel sheets

问题描述

我已经创建了 ssis 包来将 loda 数据从 excel 传输到数据库.我的问题是在我手动删除密码的那一刻，那些用密码保护的 excel 文件(我知道密码).有没有办法在 ssis 中实现自动化，因为我有 200 个 excel 文件.

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.

推荐答案

这可能会或可能不会根据您公司的安全策略(或其监控)起作用，但您可以编写一个快速宏来打开所有 200 个文件并保存它们在没有密码的临时不受保护"目录中.然后运行您的 SSIS 脚本，完成后，删除所有 200 个文件(使用安全删除程序，例如 Eraser)

This may or may not work based on your company's security policy (or monitoring thereof), but you could write a quick macro that opens all 200 files and saves them in a temp "unprotected" directory without a password. Then run your SSIS script, and when that completes, delete all 200 files (with a secure deleter such as Eraser)

Sub ManagePWords()

Dim f(1 To 200) As Variant
Dim i As Integer
Dim origpath As String, temppath As String
Dim wb As Excel.Workbook

   origpath = "c:\where_files_are_now\"
   temppath = "c:\where_files_are_now\unprotected\"

   f(1) = Array("filename1", "password1")
   f(2) = Array("filename2", "password2")
   'keep going for all 200 files

   For i = 1 To UBound(f)
      Set wb = Application.Workbooks.Open(origpath & f(i)(0), , , , f(i)(1))
      wb.SaveAs temppath & f(i)(0) & ".xlsx", , ""
      wb.Close
   Next i

End Sub

这篇关于带有 Excel 表格的 SSIS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！