In reference to a prior ticket:
I have 2 SSIS packages that have dynamic excel worksheet tab names. I followed the directions for the solution presented within the ticket listed above for 1 of the packages and that package is now picking up the dynamic tab names, problem solved. However, I followed those same directions for my 2nd package and continue to get metadata errors because the excel connection manager is unable to "process" the variable code in order to read the dynamic worksheet tab name.
Here's what I have set up:
Variable (package level): "ExcelQuery", Value = "SELECT [import$].* FROM [import$]"
Script Task (initial component within ForEach Loop Container):
ReadWriteVariables: "User::ExcelQuery, User::SourceFile"
Script Task code:
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim f As New FileInfo(Dts.Variables("User::SourceFile").Value.ToString())
Dts.Variables("User::ExcelQuery").Value = "SELECT * FROM [" + f.Name.Substring(0, Len(f.Name) - Len(f.Extension)) + "$" + "]"
Dts.TaskResult = Dts.Results.Success
Data Flow Task > Excel Source:
Data Access Mode: "SQL command from variable"
Variable name: "User::ExcelQuery"
Errors at run time (Data Flow Task name = "Import data to Benefits_Tbl):
Error: 0xC004706B at Import data to Benefits_Tbl, DTS.Pipeline: "component "Excel Source" (11818)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error: 0xC004700C at Import data to Benefits_Tbl, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Import data to Benefits_Tbl: There were errors during task validation.
Any help would be greatly appreciated! Thank you!