zaronline
asked on
How can I create a SSIS package that reffreshes Excel document connected to SQL data. I need to then save the file to 2 locations using YYYYMM ending?
Hi,
I run monthly reporting. SPs generate the reports into tables in SQL 2000 server.
I have a linked workbook that contains links to the report table in SQL.
Everymonth a SSIS package runs the SPs to generate the reports in SQL server.
I then manually go to each report and refresh all sheets in the workbook.
Then I save the template and then save the file into 2 locations.
Is it possible to do this task using SSIS to avoid the need to manually do this.
the report structures are:
TEMPLATE: REPORT YYYYMM.Xls
Final report: G:\...\Report 200903.xls
F:\...\Report 200903.xls
PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
F:\...\Report 200903.pdf
I also save some of the reports as PDF sheets.
Can this also be done using SSIS ?
Thanks,
I run monthly reporting. SPs generate the reports into tables in SQL 2000 server.
I have a linked workbook that contains links to the report table in SQL.
Everymonth a SSIS package runs the SPs to generate the reports in SQL server.
I then manually go to each report and refresh all sheets in the workbook.
Then I save the template and then save the file into 2 locations.
Is it possible to do this task using SSIS to avoid the need to manually do this.
the report structures are:
TEMPLATE: REPORT YYYYMM.Xls
Final report: G:\...\Report 200903.xls
F:\...\Report 200903.xls
PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
F:\...\Report 200903.pdf
I also save some of the reports as PDF sheets.
Can this also be done using SSIS ?
Thanks,
ASKER
Hi,
1. SSIS package runs on desktop with Excel installed on it.
2. I have the Excel macro that refreshes all:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Ru ntime
Imports Microsoft.Office.Interop.E xcel
Public Class ScriptMain
Public Sub Main()
Dim excel As New Microsoft.Office.Interop.E xcel.Appli cation
Dim wb As Microsoft.Office.Interop.E xcel.Workb ook
wb = excel.Workbooks.Open("C:\D ata\Test.x ls")
wb.RefreshAll()
wb.Save()
wb.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
2A. I also need a way to save the file into 2 locations as below (with current YYYYMM in filename) :
TEMPLATE: REPORT YYYYMM.Xls
Final report: G:\...\Report 200903.xls
F:\...\Report 200903.xls
2B. I then also need to save the files as PDF
PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
F:\...\Report 200903.pdf
3. I do not know how to Convert that macro from VBA to .Net code. IS THERE A PROGRAM I can use to do this?
4. I tried to copy the macro directly to SSIS Script task and it did not work too well.
I did find a piece of code that was ment to run macros from SSIS:
'===================
strExcelFile = Replace(WScript.ScriptFull Name, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Applic ation")
Set objWB = objExcel.Workbooks.Open(st rExcelFile )
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
But I am unsure how I set this up in a SSIS package.
It would be greate if I could get a sample SSIS package that does these tasks.
1. SSIS package runs on desktop with Excel installed on it.
2. I have the Excel macro that refreshes all:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Ru
Imports Microsoft.Office.Interop.E
Public Class ScriptMain
Public Sub Main()
Dim excel As New Microsoft.Office.Interop.E
Dim wb As Microsoft.Office.Interop.E
wb = excel.Workbooks.Open("C:\D
wb.RefreshAll()
wb.Save()
wb.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
2A. I also need a way to save the file into 2 locations as below (with current YYYYMM in filename) :
TEMPLATE: REPORT YYYYMM.Xls
Final report: G:\...\Report 200903.xls
F:\...\Report 200903.xls
2B. I then also need to save the files as PDF
PDF FILE SAVED AS: (Using Excel 2007's SAVE AS PDF option)
F:\...\Report 200903.pdf
3. I do not know how to Convert that macro from VBA to .Net code. IS THERE A PROGRAM I can use to do this?
4. I tried to copy the macro directly to SSIS Script task and it did not work too well.
I did find a piece of code that was ment to run macros from SSIS:
'===================
strExcelFile = Replace(WScript.ScriptFull
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Applic
Set objWB = objExcel.Workbooks.Open(st
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
But I am unsure how I set this up in a SSIS package.
It would be greate if I could get a sample SSIS package that does these tasks.
So when you pasted that code in, what error did you get? It looks like it should have worked to me. Once you get it working we just add additional lines to do the extra saving etc.
ASKER
Hi Expert,
I am not sure how to setup the SSIS package script task paramaters to execute the Run macro in excel script.
Is there any settings I need to change ? Do I use the ActiveX Script or Script task to perform this task?
What is the VBA macro script I can use to save the files to different locations?
Thanks for you help.
If I send a copy of the SSIS package I have made so far could you correct the bugs in it for me?
I am not sure how to setup the SSIS package script task paramaters to execute the Run macro in excel script.
Is there any settings I need to change ? Do I use the ActiveX Script or Script task to perform this task?
What is the VBA macro script I can use to save the files to different locations?
Thanks for you help.
If I send a copy of the SSIS package I have made so far could you correct the bugs in it for me?
ASKER
Hi Expert,
I have attached the error I get when I try to use the SCRIPT Task in SSIS. and the SSIS package that I made to run an excel "save as" task.
I am not able to run the script below due to this error.
'===================
strExcelFile = Replace(WScript.ScriptFull Name, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Applic ation")
Set objWB = objExcel.Workbooks.Open(st rExcelFile )
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
Any help would be appreciated.
SSIS-Refresh-Script-Error.GIF
Excel-Refresh-n-SAVE-dtsx.txt
I have attached the error I get when I try to use the SCRIPT Task in SSIS. and the SSIS package that I made to run an excel "save as" task.
I am not able to run the script below due to this error.
'===================
strExcelFile = Replace(WScript.ScriptFull
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Applic
Set objWB = objExcel.Workbooks.Open(st
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
Any help would be appreciated.
SSIS-Refresh-Script-Error.GIF
Excel-Refresh-n-SAVE-dtsx.txt
The script that you posted above will only run in an ActiveX script task (not a Script Task). As ActiveX script tasks are only there for backwards compatability, I don't suggest you use it.
What happens when you run the other script (reproduced below) in a Script Task?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Ru ntime
Imports Microsoft.Office.Interop.E xcel
Public Class ScriptMain
Public Sub Main()
Dim excel As New Microsoft.Office.Interop.E xcel.Appli cation
Dim wb As Microsoft.Office.Interop.E xcel.Workb ook
wb = excel.Workbooks.Open("C:\D ata\Test.x ls")
wb.RefreshAll()
wb.Save()
wb.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
What happens when you run the other script (reproduced below) in a Script Task?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Ru
Imports Microsoft.Office.Interop.E
Public Class ScriptMain
Public Sub Main()
Dim excel As New Microsoft.Office.Interop.E
Dim wb As Microsoft.Office.Interop.E
wb = excel.Workbooks.Open("C:\D
wb.RefreshAll()
wb.Save()
wb.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
I tried the above code myself but my install can't access the interop assemblies (the Imports Microsoft.Office.Interop.E xcel part).
I am going to put in an admin comment to add this to the .Net zone as this is getting out of my area of expertise and I may not have to time to help properly with htis.
Suffice to say, if you just want to get this working, you can paste this bit of code into an ActiveX script task and it will work. Its not really recommended though as eventualy this task will be removed from SSIS.
'===================
strExcelFile = Replace(WScript.ScriptFull Name, WScript.ScriptName, "") & "test.xls"
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Applic ation")
Set objWB = objExcel.Workbooks.Open(st rExcelFile )
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
I am going to put in an admin comment to add this to the .Net zone as this is getting out of my area of expertise and I may not have to time to help properly with htis.
Suffice to say, if you just want to get this working, you can paste this bit of code into an ActiveX script task and it will work. Its not really recommended though as eventualy this task will be removed from SSIS.
'===================
strExcelFile = Replace(WScript.ScriptFull
strMacroName = "run_macro"
Set objExcel = CreateObject("Excel.Applic
Set objWB = objExcel.Workbooks.Open(st
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
ASKER
Hi Expert,
I am having no luck running the ActiveX script in SSIS. ive attached the error I recieve. Could I get a sample working version of this.
I have used the following SCRIPT TO RUN:
'===================
strExcelFile = "RCTI YYYYMM.xls"
strMacroName = "mymacro"
Set objExcel = CreateObject("Excel.Applic ation")
Set objWB = objExcel.Workbooks.Open(st rExcelFile )
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
ActiveXerror-SSIS.bmp
I am having no luck running the ActiveX script in SSIS. ive attached the error I recieve. Could I get a sample working version of this.
I have used the following SCRIPT TO RUN:
'===================
strExcelFile = "RCTI YYYYMM.xls"
strMacroName = "mymacro"
Set objExcel = CreateObject("Excel.Applic
Set objWB = objExcel.Workbooks.Open(st
objExcel.Visible = True
objWB.Application.Run strMacroName
objWB.Close False
objExcel.Quit
'===================
ActiveXerror-SSIS.bmp
Well, if we pursue the .Net solution just a little further (I am reluctant to support ActiveX in SSIS)
If you drop a Script Task on your page and go into the editor (Design Script)
Now go to Project/Add Reference
Do you see
Microsoft.Office.Interop.E xcel
in the list anywhere?
Did you try my suggestion at comment ID 24292454?
If you drop a Script Task on your page and go into the editor (Design Script)
Now go to Project/Add Reference
Do you see
Microsoft.Office.Interop.E
in the list anywhere?
Did you try my suggestion at comment ID 24292454?
ASKER
Hi Expert,
I an not able to find the Interop in the Add reference panel.
I have tried your suggestion in comment ID 24292454 however I get the Red X symbol with an error.
Any other way or is there a way to gain this reference added?
Thanks
I an not able to find the Interop in the Add reference panel.
I have tried your suggestion in comment ID 24292454 however I get the Red X symbol with an error.
Any other way or is there a way to gain this reference added?
Thanks
I have created this question:
http://www.experts-exchang e.com/Prog ramming/La nguages/.N ET/Q_24387 555.html
To try and work it out the Interop assembly thing but no one has been able to help so far.
How about my suggestion at id 24300087, did you try that? (in an ActiveX script task)
I think we can use the CreateObject function in .Net to get around that missing assembly. We'll basically need to convert your code and macro to .Net
http://www.experts-exchang
To try and work it out the Interop assembly thing but no one has been able to help so far.
How about my suggestion at id 24300087, did you try that? (in an ActiveX script task)
I think we can use the CreateObject function in .Net to get around that missing assembly. We'll basically need to convert your code and macro to .Net
ASKER
Hi Expert,
I have already tried that in Active X. Please refer to my comment on 24301535. I have attached the DTS package I created and error images.
Could you please have a look at the package and let me know if I am doing anyting incorrect?
Thanks,
I have already tried that in Active X. Please refer to my comment on 24301535. I have attached the DTS package I created and error images.
Could you please have a look at the package and let me know if I am doing anyting incorrect?
Thanks,
The attached code appears to work intermittently - paste it into a script task. (not ActiveX)
Because we are using late binding (CreateObject, in turn because we can't get the Interop to work), we need to set a tick box:
Inside the actual editor, Press Project / Properties. Under build, untick 'Option Strict On by default'
You'll need to edit the code to
1. Use the paths you want.
2. Add another SaveAs line to save another copy of the workbook
3. If an error occurs, it will show it in a messagebox. You may not want this when its finally working
I ran this a few times but it did not seem to let go of the Excel object.
So basically it is partially working, until I can get time to take a closer look.
Have a look and get back to me.
Because we are using late binding (CreateObject, in turn because we can't get the Interop to work), we need to set a tick box:
Inside the actual editor, Press Project / Properties. Under build, untick 'Option Strict On by default'
You'll need to edit the code to
1. Use the paths you want.
2. Add another SaveAs line to save another copy of the workbook
3. If an error occurs, it will show it in a messagebox. You may not want this when its finally working
I ran this a few times but it did not seem to let go of the Excel object.
So basically it is partially working, until I can get time to take a closer look.
Have a look and get back to me.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Public Class ScriptMain
Public Sub Main()
Dim sPeriod As String
Dim oExcel As Object
Dim oWB As Object
Dts.TaskResult = Dts.Results.Success
Try
oExcel = CreateObject("Excel.Application")
oWB = oExcel.Workbooks.Open("D:\KB\ExpertsExchange\Q_24357093\REPORT YYYYMM.xls")
sPeriod = (Format(DateTime.Today, "yyyyMM"))
Call oExcel.ActiveWorkbook.RefreshAll()
Call oExcel.ActiveWorkbook.SaveAs("D:\KB\ExpertsExchange\Q_24357093\REPORT " & sPeriod & ".xls")
Catch ex As Exception
MsgBox(ex.Message)
Dts.TaskResult = Dts.Results.Failure
End Try
Call oWB.Close(False)
oExcel.Quit()
oExcel = Nothing
End Sub
End Class
ASKER
Hi Expert,
Thank you for this. I have actually compiled an Excel macro that loops through the entire folder and refreshes and saves the data as required. I have over 50 reports, so that was the quickest solution.
I need SSIS to initiate the running of that macro. How can I modify your code to make it execute a macro called "RUN_ALL_MACRO"
Thank you for this. I have actually compiled an Excel macro that loops through the entire folder and refreshes and saves the data as required. I have over 50 reports, so that was the quickest solution.
I need SSIS to initiate the running of that macro. How can I modify your code to make it execute a macro called "RUN_ALL_MACRO"
Thats what I tried doing first but there were so many Excel security issues around macros, I figured this was the simplest way.
You can try the code below (untested). You should make sure that you untick 'Option Strict On by default' as described above.
Try it and see.
You can try the code below (untested). You should make sure that you untick 'Option Strict On by default' as described above.
Try it and see.
Imports System
Public Class ScriptMain
Public Sub Main()
Dim oExcel As Object
Dim oWB As Object
Dts.TaskResult = Dts.Results.Success
Try
oExcel = CreateObject("Excel.Application")
oWB = oExcel.Workbooks.Open("D:\KB\ExpertsExchange\Q_24357093\REPORT YYYYMM.xls")
oWB.Application.Run "mymacro"
Catch ex As Exception
MsgBox(ex.Message)
Dts.TaskResult = Dts.Results.Failure
End Try
Call oWB.Close(False)
oExcel.Quit()
oExcel = Nothing
End Sub
End Class
ASKER
Thank you for the update. I have been ill last few days. I will try the solution this week and leave feedback.
ASKER
Hi Expert,
I just tested this package. Still getting the same error message as in post 24291826.
Could some one provide me with a sample working SSIS package. I could check if there is anything I am doing wrong on my one.
Thanks
I just tested this package. Still getting the same error message as in post 24291826.
Could some one provide me with a sample working SSIS package. I could check if there is anything I am doing wrong on my one.
Thanks
Did you untick 'Option Strict On By Default'?. This will allow late binding, which in turn will allow your script to compile (assuming there are no other problems), which in turn will remove the error in comment 24291826.
I have attached a package where this option is unticked and the code compiles OK.
Once you have got around the compilation error, I still can't guarantee that it will work as there are a lot of annoying security lockdowns that stop macros running.
Package.txt
I have attached a package where this option is unticked and the code compiles OK.
Once you have got around the compilation error, I still can't guarantee that it will work as there are a lot of annoying security lockdowns that stop macros running.
Package.txt
ASKER
Hi Expert,
I was finally able to run the package. But again the process errored out.
I have attached the error message.
Something to do with Excel macros being disabled.
Any way to get around this issue?
ActiveXerror-SSIS.bmp
I was finally able to run the package. But again the process errored out.
I have attached the error message.
Something to do with Excel macros being disabled.
Any way to get around this issue?
ActiveXerror-SSIS.bmp
ASKER
Sorry attached the wrong error immage. Please find the error message for post 24552104.
Macro-Excel-Error.JPG
Macro-Excel-Error.JPG
Thats what I meant earlier on about all the macro security making things really difficult. Macros are heavily locked down and it could take you some time to work out how to unlock it.
It should be as easy as setting an 'enable' flag somewhere. Go into your Excel sheet and see if it warns you about macros being locked. If so, unlock them and save the sheet. You may need to do some kind of Excel wid or machine wide macro unlock thing, which of course makes your machine susceptible to macro attacks (which really are a thing of the past if you ask me)
What version of Excel do you have? If its 2007, do you want to post your Excel sheet to me and I can take a look.
If we can't unlock the macro's in the sheet then we need to convert the macro to VBScript (not too hard) and paste the macro into the script task.
It is not an ideal solution - you really are better off with the macro code in Excel - but it is an option.
It should be as easy as setting an 'enable' flag somewhere. Go into your Excel sheet and see if it warns you about macros being locked. If so, unlock them and save the sheet. You may need to do some kind of Excel wid or machine wide macro unlock thing, which of course makes your machine susceptible to macro attacks (which really are a thing of the past if you ask me)
What version of Excel do you have? If its 2007, do you want to post your Excel sheet to me and I can take a look.
If we can't unlock the macro's in the sheet then we need to convert the macro to VBScript (not too hard) and paste the macro into the script task.
It is not an ideal solution - you really are better off with the macro code in Excel - but it is an option.
This site
http://office.microsoft.co m/en-us/or k2003/HA01 1403071033 .aspx
indicates that for 2003, you can either set the Excel macro security to low, or digitally sign the macro. Why don't you set security to low and try your code again.
It is remotely possible that the macro doesn't exist (as per the message) but I assume you've already checked that, and I'm certain it is security related.
http://office.microsoft.co
indicates that for 2003, you can either set the Excel macro security to low, or digitally sign the macro. Why don't you set security to low and try your code again.
It is remotely possible that the macro doesn't exist (as per the message) but I assume you've already checked that, and I'm certain it is security related.
Use SSIS w/o Excel macros...
ASKER
Hi Expert,
Good news on one of these samples!!
I have tried ID: 24363094 and this seems to work ok for a simgle file. But there is an issue that the data is not yet refreshed and the file is attemption to save.
How can I make the query wait for the data to refresh before I save the file?
Good news on one of these samples!!
I have tried ID: 24363094 and this seems to work ok for a simgle file. But there is an issue that the data is not yet refreshed and the file is attemption to save.
How can I make the query wait for the data to refresh before I save the file?
ASKER
I am getting the following error when I try to correct this issue by switching the display alerts off.
How can I set the displayAlerts property off when running this Script task?
SwitchDisplayAlertOff-Error.JPG
How can I set the displayAlerts property off when running this Script task?
SwitchDisplayAlertOff-Error.JPG
Glad you made some progress.
Take the CALL out from the front of it.
Call is used when calling a method
Call is not used when setting a property (which is what you're doing)
Take the CALL out from the front of it.
Call is used when calling a method
Call is not used when setting a property (which is what you're doing)
ASKER
Thank you for the tip. Is this .NET script that I am using in this code?
If its inside a script task then yes its .Net script.
The ActiveX script task is for backwards compatability. It is VBScript code (comparable to VB 6)
You are using .Net script to call a COM object by late binding, which is a bit of a mish mash. COM is more used back in VB6 days. thats why we had to mess about to get it to work.
Hope that makes sense.
The ActiveX script task is for backwards compatability. It is VBScript code (comparable to VB 6)
You are using .Net script to call a COM object by late binding, which is a bit of a mish mash. COM is more used back in VB6 days. thats why we had to mess about to get it to work.
Hope that makes sense.
ASKER
Thanks for that tip that worked fine for the ODBC Connected datasets.
It does not however update pivot tables in the dataset. Is there another .NET comand that can update the pivot tables?
It does not however update pivot tables in the dataset. Is there another .NET comand that can update the pivot tables?
Apparently the RefreshAll method is meant to refresh them. But if it isn't, you can try using the code on this page:
http://stackoverflow.com/q uestions/7 0947/how-c an-i-refre sh-all-the -pivot-tab les-in-my- excel-work book-with- a-macro
Which shows how to cycle through all pivot tables and refresh them.
I have converted it for you and attached below. Just plug it in before the SaveAs line.
http://stackoverflow.com/q
Which shows how to cycle through all pivot tables and refresh them.
I have converted it for you and attached below. Just plug it in before the SaveAs line.
dim w, p
for each w in oExcel.ActiveWorkbook.Worksheets
for each p in w.pivottables
p.refreshtable
p.update
next
next
ASKER
Excelent result Expert. This worked like a charm.
I guess there is more than 1 way to skin a cat.
What the MACRO did was loop through a folder and find all the EXCEL files in that folder and refresh each file then saving it to 2 locations.
It seems we have built every thing except the first part: Loop through the folder collect all the Excel sheet names and execute the code for all the Excel files one by one!!.
If this is possible in .NET then I don't need to use the macro in excel at all.
Is there a way to do this?
So far I have the following codes working fine for 1 file. we need to do this for all excel files in say: C:\myFolder\ExcelTemps\
I guess there is more than 1 way to skin a cat.
What the MACRO did was loop through a folder and find all the EXCEL files in that folder and refresh each file then saving it to 2 locations.
It seems we have built every thing except the first part: Loop through the folder collect all the Excel sheet names and execute the code for all the Excel files one by one!!.
If this is possible in .NET then I don't need to use the macro in excel at all.
Is there a way to do this?
So far I have the following codes working fine for 1 file. we need to do this for all excel files in say: C:\myFolder\ExcelTemps\
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Public Class ScriptMain
Public Sub Main()
Dim sPeriod As String
Dim oExcel As Object
Dim oWB As Object
Dts.TaskResult = Dts.Results.Success
Try
oExcel = CreateObject("Excel.Application")
oWB = oExcel.Workbooks.Open("D:\KB\ExpertsExchange\Q_24357093\REPORT YYYYMM.xls")
sPeriod = (Format(DateTime.Today, "yyyyMM"))
Call oExcel.ActiveWorkbook.RefreshAll()
dim w, p
for each w in oExcel.ActiveWorkbook.Worksheets
for each p in w.pivottables
p.refreshtable
p.update
next
next
Call oExcel.ActiveWorkbook.SaveAs("D:\KB\ExpertsExchange\Q_24357093\REPORT " & sPeriod & ".xls")
Catch ex As Exception
MsgBox(ex.Message)
Dts.TaskResult = Dts.Results.Failure
End Try
Call oWB.Close(False)
oExcel.Quit()
oExcel = Nothing
End Sub
End Class
Yep there are many ways to skin a cat :)
For example to loop through a file you could write some VB.Net code to do it..... or you could use the For Eacg iterator in SSIS.
It depends what you would like to learn more about - SSIS or VB.Net?
I actually suggest SSIS if you are doing a lot of data integration stuff.
For example to loop through a file you could write some VB.Net code to do it..... or you could use the For Eacg iterator in SSIS.
It depends what you would like to learn more about - SSIS or VB.Net?
I actually suggest SSIS if you are doing a lot of data integration stuff.
ASKER
Hi Expert,
I will be gready and ask to know how to do this in both SSIS and VB.NET,
As I am having a winning streak with VB.NET could you please let me know how this can be done in VB.NET first?
I will be gready and ask to know how to do this in both SSIS and VB.NET,
As I am having a winning streak with VB.NET could you please let me know how this can be done in VB.NET first?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Expert,
I am working with this code from the website you provided. Just a quick question, is this an ACTIVEX script or script task?
Import these libraries:
Imports System.IO
Imports Microsoft.Office.Interop
Then in your method body:
Dim objDirectory As Directory
Dim arrFileNames As String() = objDirectory.GetFiles("X:\ ExcelWorkb ooks")
Dim strFileName As String
Dim objExcelApplication As New Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet
For Each strFileName In arrFileNames
objExcelWorkbook =
objExcelApplication.Workbo oks.Open(s trFileName )
For Each objExcelWorksheet In objExcelWorkbook.Worksheet s
' Do something with objExcelWorksheet.Name
Next
objExcelWorkbook.Close()
Next
objExcelApplication = Nothing
I am working with this code from the website you provided. Just a quick question, is this an ACTIVEX script or script task?
Import these libraries:
Imports System.IO
Imports Microsoft.Office.Interop
Then in your method body:
Dim objDirectory As Directory
Dim arrFileNames As String() = objDirectory.GetFiles("X:\
Dim strFileName As String
Dim objExcelApplication As New Excel.Application
Dim objExcelWorkbook As Excel.Workbook
Dim objExcelWorksheet As Excel.Worksheet
For Each strFileName In arrFileNames
objExcelWorkbook =
objExcelApplication.Workbo
For Each objExcelWorksheet In objExcelWorkbook.Worksheet
' Do something with objExcelWorksheet.Name
Next
objExcelWorkbook.Close()
Next
objExcelApplication = Nothing
Sorry I didn't respond - that would be a script task.
Regarding comment 24351517 way up the top, I found some info on using the interop. Apparently SSIS can only see assemblies stored in limited locations. (somewhere under windows I think). It can't see them stored in other standard assmbly locations.
Regarding comment 24351517 way up the top, I found some info on using the interop. Apparently SSIS can only see assemblies stored in limited locations. (somewhere under windows I think). It can't see them stored in other standard assmbly locations.
1. Install Excel on the server that SSIS is running on (can sometimes be an issue)
2. Record an Excel macro which does what you need it to do (refresh data, Save As etc.)
3. Convert that macro from VBA to .Net code
4. Paste the code into a script task in SSIS
There are some other minor complications but thats the gist of it. I can give you more info if need be.