October 13, 2014 at 11:25 am
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.
October 13, 2014 at 11:35 am
Are the tab names in the spreadsheets all the same?
--Edit: ignore that – I just read your post more carefully
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2014 at 11:36 am
Sounds like the tab name in each file are is the same. Is that correct?
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]
October 13, 2014 at 11:38 am
Ooops. Looks like you were faster than me Phil. 😀
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]
October 13, 2014 at 11:38 am
Sounds like you need to set the Excel sheet name dynamically via an SSIS Expression. Or are you doing that already?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2014 at 11:43 am
Tab is not same every day we receive the file and File Name = Filename_MMDDYYYY and Tab Name = FileName_MMDDYYYY
October 13, 2014 at 11:51 am
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.
October 13, 2014 at 12:06 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2014 at 12:40 pm
Sheet name is not same every day we receive the file. File Name = Filename_MMDDYYYY and Tab Name = FileName_MMDDYYYY
October 14, 2014 at 12:24 pm
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
October 14, 2014 at 12:41 pm
Thanks for your email. I GOOGLE it before I submit my question, I couldn't figure it out thats why I asked.
October 15, 2014 at 10:18 am
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.
October 22, 2014 at 3:07 pm
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