Excel Connection Manager in SSIS Help

  • Hi Guys,

    I need urgent help. I am working on project where I am going transfer all .XLS file format everyday. File Name "FileName MM-DD-YYYY" and table name "File Name MM-DD-YYYY". Here is my SSIS Package looks like

    1)Loop though the file and Insert All Files to SQL Table.

    2)Execute SQL Task = Getiing Full File Name from SQL Table and putting the result set to Variable "FilePathToLoad"

    3) Fooreach Loop Container==>Enumerator = Foreach ADO Enumerator==> ADO Object SOurce variable = FilePathToLoad

    and variable mapping to "Filename"

    4)In Data Flow Source ==> Excel Connection Manager==> Hit New and browse the file that I want to import and set excel sheet

    (Please keep in mind File name and Tab/Sheet name is FileName MM-DD-YYYY

    5)Right click on Excel Connection manager and hit properties ==> Expression ==> Connection String = Variable name "Filename"

    (In Foreach Loop Container).

    My Understanding, it should all .xls file one by one to sql table. However I am only transfer successfully the file I Picked during Excel connection Manager, after I am getting error "Make sure Tab is already exist"

    Please guide me what I am doing wrong. Urgent Please.

    Thank You.

  • Are the tab names in the spreadsheets all the same?

    --Edit: ignore that – I just read your post more carefully

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Sounds like the tab name in each file are is the same. Is that correct?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Ooops. Looks like you were faster than me Phil. 😀



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Sounds like you need to set the Excel sheet name dynamically via an SSIS Expression. Or are you doing that already?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Tab is not same every day we receive the file and File Name = Filename_MMDDYYYY and Tab Name = FileName_MMDDYYYY

  • I am using SQL Table to get the file name. First step Insert all FolderPath+FileName to SQL Table and then using the file name Variable in Foreach Loop Container as a Enumerator = Foreach ADO Enumerator and ADO Object Source Variable = File Name Variable and data type is Object. and in Foreach Loop Variable Mapping I am using different Variable that I used in Source = Excel connection manager.

  • rocky_498 (10/13/2014)


    I am using SQL Table to get the file name. First step Insert all FolderPath+FileName to SQL Table and then using the file name Variable in Foreach Loop Container as a Enumerator = Foreach ADO Enumerator and ADO Object Source Variable = File Name Variable and data type is Object. and in Foreach Loop Variable Mapping I am using different Variable that I used in Source = Excel connection manager.

    Please answer my question regarding sheet names.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Sheet name is not same every day we receive the file. File Name = Filename_MMDDYYYY and Tab Name = FileName_MMDDYYYY

  • Below is VB 2008/Script Task Editor Code, that I used for rename the sheet name/tab name in my excel sheet. Let me give you background what I am doing, I am receiving everyday Excel file, file name something like this

    ABC_MM_DD_YYYY and Tab Name = ABC_MM_DD_YYYY. Everytime when we receive file File Name and Tab is totally different. I couldn't figure it out, so the solution I have to In Foreach Loop get the file name and in Script Task

    replace tab/sheet name to "Sheet1" and then process my file. I am not good in VB, I want to know how Can I use Variable in below code to replace HARD CODE My file name. Any help would be great Appreciate.

    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()

    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

    Dim laPath As String = "C:\Excel\ABC_dr_daily_lf_10-07-14.xls" 'My File Name

    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 = "Sheet1"

    xlBook.Save()

    xl.Application.Workbooks.Close()

    Dts.TaskResult = ScriptResults.Success

    End Sub

    End Class

  • Sure. Have a look here.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for your email. I GOOGLE it before I submit my question, I couldn't figure it out thats why I asked.

  • Here is my code, that I added to use variable....

    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()

    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 = "C:\Excel\ABC_dr_daily_lf_10-07-14.xls" 'My File Name

    Dim lapath As String = "File" '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 = "Sheet1"

    xlBook.Save()

    xl.Application.Workbooks.Close()

    Dts.TaskResult = ScriptResults.Success

    End Sub

    End Class

    And I am using Variable = Filename READONLY IN Script Component.

    I am getting this error

    Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object.

    at ST_916c6c3c7c75477199a83e3031243cae.vbproj.ScriptMain.Main()

    --- End of inner exception stack trace ---

    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

    at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

    at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)

    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    Please guide me, where I am doing wrong,

    Thank You.

  • I solved this question, Please reply, if someone wants a simple Solution.

    Thank You.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply