December 15, 2014 at 8:53 am
Hi Guys,
Need urgent help.
I am working on project in SSIS. Simple SSIS Package, read file from Excel and do some transformation and insert the data into SQL table (Very simple). Problem is Every time excel file has different tab. So What I am doing, I am using Script task (VB 2008) to rename Excel tab to "Sheet1", working fine on my local without any problem. Here is my VB Code. In my Local machine Microsoft Office are installed. Now I have to move this package to Dev Inv. Unfortunately my Dev and Prod server doesn't have MS Office installed. I am having a problem to run or rename excel sheet.
Two questions
1) Is there other way to rename or read dynamic excel tab from Excel source?
2) Is there is a way we can Open Excel file/rename and save Excel file in VB without MS Office installed?
Please advise.
Thank You.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Text
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dts.VariableDispenser.LockForRead("User::FileName") ' File Name Variable
Dim variablesList As Variables
Dts.VariableDispenser.GetVariables(variablesList)
' Dim SFileName As String
'SFileName = variablesList("User::Filename").Value.ToString
' Dim vars As Variables 'New Added For Variable
Dim oMissing As Object = System.Reflection.Missing.Value
Dim xl As New Microsoft.Office.Interop.Excel.ApplicationClass()
Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
'Dts.VariableDispenser.LockForRead("Filename") 'New Added For Variable
'Start For Variable
'Dim File As String 'New Added For Variable
'File = CType(vars("Filename").Value, String) 'New Added For Variable
Dim lapath As String = variablesList("User::FileName").Value.ToString 'New Added For Variable
xlBook = DirectCast(xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, _
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, _
oMissing, oMissing, oMissing), Workbook)
xlSheet = DirectCast(xlBook.Worksheets.Item(1), Worksheet)
xlSheet.Name = "data"
xlBook.Save()
xl.Application.Workbooks.Close()
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
January 13, 2015 at 3:57 pm
I had a similar issue, re not having office installed on the server.
I ended up using Windows Scheduler on a machine with office installed. This just opened an excel file which contained an auto_open macro. The macro made all the updates to the file (i.e. changing the tab name) and then saved the updated file down to a particular location.
I then updated the SSIS package to use a For Loop Container, with the first step a Script Task that checked if the newly output file exists, if not it would pause for a given period of time and check again. Once the file was found the rest of the package would execute.
The last step of the package was to delete the excel file so that when the package looped back around it wouldn't be found and would keep checking until it appeared again.
January 14, 2015 at 8:52 am
Thanks for reply, would you mind if you send me Macro code or any steps that would be help me to achieve what you are suggesting?
Thank You.
January 14, 2015 at 3:19 pm
1) Add a For Loop container and in the eval expression and 1==1 (so always returns true and will continuously loop).
2) Inside the container add a script task to check if the file exists. The below code will check if the file exists. If it does it will move onto the next step. If not it will pause for 1 minute and check again. Change "System.Threading.Thread.Sleep(numberofmiliseconds)" property to adjust the interval.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Dim Answer As String
Answer = False
While Answer = False
If System.IO.File.Exists("yourfilelocation\yourfilename.xls") Then
Answer = True
Exit While
Else
System.Threading.Thread.Sleep(60000)
End If
End While
System.Threading.Thread.Sleep(60000)
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
3) Add the rest of the SSIS package transformations inside the container
4) Create an excel file with an auto_open macro in it along the lines of
Sub Auto_Open()
Application.DisplayAlerts = False
ChDir "YourFileLocation\"
Workbooks.Open Filename:="yourfilelocation\yourfilename.xls"
'Enter your macro data here to change the tab name and any other updates to the file
'Saves the file to the import location specified in the script task
ChDir "C:\_data\ToImport"
ActiveWorkbook.SaveAs Filename:="yourfilelocation\ToImport\yourfilename.xls", _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
'Closes the file and quits excel
ActiveWorkbook.Close
Application.Quit
Application.DisplayAlerts = True
End Sub
5) Open Task Scheduler on a machine that has office installed and schedule a new task to run. Under actions choose "Start a program" and add the location and name of the excel file just created in excel with the auto_open macro in it.
6) Start you SSIS package and it will continue to run until manually stopped. If a file is found in the ToImport folder the SSIS package will run through to the end and then loop back to the start again, otherwise it will keep repeating the script task until a file it found
Hope that helps.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply