SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Send Excel SpreadSheet Filename to SSIS


Send Excel SpreadSheet Filename to SSIS

Author
Message
davidlcrooks
davidlcrooks
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 49
I guess this is the right place for this question?

I am trying to pass a variable (filename with path) to a package to send that variable to SSIS as an Excel Source. I have a variable defined as a string and a default value where an Excel file is located (verified). When I change the Data Access Mode to table name or variable and then select the variable it throws the error that opening the rowset failed. Any ideas? TIA!

David L. Crooks
sabotta
sabotta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 124
Did you use an Expression to configure the ExcelFilePath property value of the Excel Connection Manager:

@[<variable name>]
davidlcrooks
davidlcrooks
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 49
No, I had thought about something like that after I posted the question.

Now, I get an error to Please provide a file extension in the Excel file path... I did try to brute force it but then it said it was an invalid file name.

Thanks for the reply!
sabotta
sabotta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 124
What is the file path (full path) that you're storing in the variable? And, is this an Excel '97 file or a later version?
davidlcrooks
davidlcrooks
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 49
The path is long one with many spaces like t:\clients\projects\blah blah\report1

It is set to Excel 97-2003.
sabotta
sabotta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 124
Without being able to see the package you've created, I can't be certain what the problem may be. Have you confirmed that the Excel file you're using is not corrupt (trying opening it in Excel)?

I created a simple package (using BIDs on a 32-bit machine) that contains an Excel Source & OLE DB Destination, Excel Connection Manager, and an SSIS variable that stores the full path to the Excel file. The package runs successfully. Here are the steps I followed. I hope this is helpful in resolving the error you're getting:

1. Added Excel Source.
2. Added Excel Connection Manager, clicked Edit, and entered the full file path in the Excel file path box (C:\Work\Excel Folder\TestBook97.xls). Saved changes.
3. Configured the Excel Source to point to the connection manager.
4. Added a string variable to the package, confirmed that the variable scope was Package, and filled in the variable value (C:\Work\Excel Folder\TestBook97.xls).
5. Added an expression to the Excel Connection Manager, to set value of the ExcelFilePath property using the string variable.
6. Connected the Source to the Destination.
P Jones
P Jones
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2776 Visits: 1524
Set the delay validation property to true to stop it checking the connection file before the variable is properly loaded. I can't remember if it's on the connection manager or the actual component that needs it.
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