Excel Connection Manager Help in SSIS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Excel Connection Manager Help in SSIS

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2014-10-13 : 13:24:57

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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 14:54:53

Normally you would set up a string variable as the target of the FOREACH File container. Then you would refer to that variable on the File Input transformation in the data flow.

tooba
Posting Yak Master

224 Posts

Posted - 2014-10-13 : 15:38:39

Yes, I did, but always he is pulling/inserting the same file.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-13 : 16:09:15

quote:
Originally posted by tooba

Yes, I did, but always he is pulling/inserting the same file.

What is the name of the variable that receives the filename in the foreach container? Is it the same one you reference in the dataflow Excel source connector? Is it a package-level parameter?

ONe thing you can do is set breakpoints at the start and end of the foreach loop and when you get there, look at the variables to see they are what you expect them to be.

tooba
Posting Yak Master

224 Posts

Posted - 2014-10-14 : 14:23:16

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 11:11:32

OK -- well pass the filename variable (the one I asked you about above) to the script component as readonly, then reference it using the variables collection

http://msdn.microsoft.com/en-us/library/aa337079.aspx

tooba
Posting Yak Master

224 Posts

Posted - 2014-10-15 : 12:15:33

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.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 14:00:46

at this point I'd put in some calls to MsgBox to see how far I get before the exception. e.g. just after the line

[code\]
File = CType(vars("Filename").Value, String) 'New Added For Variable
[/code]

and display the variable File in the msgbox. If it crashes before you get there, double-check your script task variable setup.

tooba
Posting Yak Master

224 Posts

Posted - 2014-10-15 : 14:08:27

gbritton, thank you for your reply.
I am not good in VB, could you please tell me how I can add Mesgbox in my code?

tooba
Posting Yak Master

224 Posts

Posted - 2014-10-15 : 14:19:14

I just add this one ( I am not sure, its right or not)

MsgBox(Prompt:="File")
'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
MsgBox(Prompt:="File")

However I am getting this warning, this is the same warning that I am getting before.

" Variable "Var" is used before it has been assigned a value. A null reference exception could result at runtime"

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-15 : 15:42:55

Why do you have "File" in quotes? Do you want the string or the contents of the variable? Also, I don't see a variable called "Var" in your program.

Look up MessageBox examples. Good ones here: http://www.dotnetperls.com/messagebox-show-vbnet

e.g. MessageBox.Show(File) 'display the filename just retrieved from the Variables object.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources