Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Excel File Source


Dynamic Excel File Source

Author
Message
jbsaari
jbsaari
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 42
What are the steps necessary to pass the Excel Source the excel file name as a variable? I've tried this a few different ways now and have had issues each way.

I've been trying to get this working for days! I've read a couple of long threads that involve a ForEach Loop Container to get multiple excel files and I've stepped thru them and am still stuck. I only wish to load one file per run. The format and location of the Excel File will never change - just the file name.

Here's what I've tried most recently:

1. Create Variable
Variable Name: FileName
Scope: Package
Data Type: String
Value: F:\apps\excel\Expense_Report_Form.xls

2. Run Package Nomally (w/o variable)
Excel Source: I set this up to point to the Excel File and tested with the Data Conversion and OLE DB Destination. Execute the package and it runs as expected. Moves Excel data to SQL Server.

3. Create expression (Connection Manager)
In the connection manager for the excel source create a expression
Property: ConnectionString
Expression: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] +";Extended Properties=\"Excel 8.0;HDR=YES\";"

When I click evaluate expression it returns the following:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\apps\excel\Expense_Report_Form.xls;Extended Properties="Excel 8.0;HDR=YES";


As soon as I setup the expression the excel source gets a red X with error "an OLE DB error has occurred." If I try and execute the package it fails at that step. What am I missing here?? Right now I have delay validation set to "false" on the connection manager properties.

I am beyond frustrated at this point. I would greatly appreciate any advice you have to offer. Thanks in advance:-)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8314 Visits: 19461
Delay validation should be set to True, not False, if the source file does not exist until run-time.

You could approach this a different way. Set up a hard-coded connection to, say, Data.xls and as the first part in your package, copy the dynamically named Excel file to it, overwriting what is already there.

Then allow the process to run using data.xls, which will always exist and your validation problems should go away.

Phil


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
jbsaari
jbsaari
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 42
Thanks for the reply Phil! I completely rebuilt the package, set delay validation = false..and it runs without error!

One more question. I'm trying to execute the package from a SP. What's the proper syntax to pass the variable from the SP to the SSIS package? My overall goal is to call the SSIS package programatically from an asp.net application. So I'll be passing the filename as a varaible from the program.

Here's what I have so far:

AS
DECLARE @cmd varchar(1000)
DECLARE @SSISpath varchar(1000)
DECLARE @ReturnCode int

set @SSISpath = 'F:\apps\SSIS Packages\ExcelImport.dtsx'

set @cmd= 'dtexec /F "' + @SSISpath + '"'
set @cmd = @cmd + ' /SET \Package.Variables[User::FileName].Properties[Value];"'

EXEC @ReturnCode = master..xp_cmdshell @cmd

Again, thanks for all your help!
Munabhai
Munabhai
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 551
Hi I have same problem, I could not follow you could you please explan little more detail what to do.

It would be really great help, I spend, 5 hrs on this. In .cvs works perfect
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
Munabhai (11/8/2012)
Hi I have same problem, I could not follow you could you please explan little more detail what to do.

It would be really great help, I spend, 5 hrs on this. In .cvs works perfect


What's the issue? Any errors?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search