troubleshooting Question

SSIS dynamic excel worksheet tab names issue

Avatar of DixieDev
DixieDevFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
12 Comments1 Solution4792 ViewsLast Modified:
In reference to a prior ticket:
https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23133695.html?sfQueryTermInfo=1+10+30+connect+dynam+excel+manag+name+ssi+tab+worksheet

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:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

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
	End Sub

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!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros