How to load excel file with dynamic filename??

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » How to load excel file with dynamic...

How to load excel file with dynamic filename??

Rate Topic

Display Mode

Topic Options

Author

Message

jyoti_bhatt

Posted Monday, June 07, 2010 8:18 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 19, 2010 2:32 AM
Points: 38, Visits: 98

Any ideas how I can load a excel file with a dynamic filename?

Many Thanks for your help

Post #933534

Dan.Humphries

Posted Monday, June 07, 2010 8:34 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 15, 2012 8:38 AM
Points: 802, Visits: 1,169

You have to setup a variable and assign it a value equal to the path of the file you wish to export to. Then when you setup the connection manager to Excel you go into the properties and asign the value of the variable as an expression in the connection string value. There are quite a few articles and forum posts on-line that demonstrate this but here is one of them.

http://www.sqlservercentral.com/Forums/Topic530390-147-1.aspx

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Post #933543

jyoti_bhatt

Posted Monday, June 07, 2010 8:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 19, 2010 2:32 AM
Points: 38, Visits: 98

Many thanks for your response.. I'd like to import from a excel file rather than export.. how do I assign the excel source to pick a dynamic filename name.. if I add the variable to connection string as directory + variable I get red X on the Excel source component?? Could you please provide step by step guide would be much appreciated...

Post #933544

Dan.Humphries

Posted Monday, June 07, 2010 9:10 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 15, 2012 8:38 AM
Points: 802, Visits: 1,169

I apologize I do not have time today to post detailed steps but there are many articles on-line with detailed instructions. If you have already done research on those then I recommend you post a specific questions as to what you have tried and where you are having a problem.

Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

Post #933560

SSIS_NewBi

Posted Monday, June 07, 2010 9:41 AM

Valued Member

Group: General Forum Members
Last Login: Monday, May 16, 2011 8:40 AM
Points: 60, Visits: 117

Hi Jyoti

Please follow the following steps :

1. Click on package properties. Set "DelayValidation" property to True.
The package will not validate tasks, connections, until they are executed.

2. Create an Excel connection in the connection manager. Browse to the target directory
and select the destination .xls filename or type it in. It doesn't matter if the file doesn't exist.

3. Select the Package and then go to SSIS -->Package Configurations --> A Wizard will popup
--> Select Enable Package Configurations
--> Click on Add
--> Specify Configuration File Directly option -- specify the dtsconfig file name
--> From the list on left Select
Connection Manager--> Excel File Connection --> ExcelFilePath
--> Save the Configuration File -- give it a name here

4. Create a Data flow Task

5. In the data flow select Excel source Task --> Set it to Excel file connection created earlier

6. Create a data reader destination task

7. Connection the two tasks and you are set.

In the dtsconfig file you can manually change the excel file name

Post #933575

divyanth

Posted Tuesday, June 08, 2010 7:45 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 20, 2013 4:16 PM
Points: 215, Visits: 551

jyoti_bhatt (6/7/2010)

Any ideas how I can load a excel file with a dynamic filename?

Will the folder have only one file? If so, just use a For Each Loop with *.xls or *.Xlsx Pattern, so that it picks that single file for processing.. But before that you need to prepare your DFT by properly configuring the Connection Manager...

Post #934001

signforarun

Posted Friday, January 20, 2012 5:32 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 4:20 AM
Points: 27, Visits: 33

This can be run only in server

EXEC 'TABLENAME','PATH'

CREATE PROCEDURE BULKINSERTEXCEL
(
@tablename nvarchar(500) ,
@PATH nvarchar(500)
)
as
DECLARE @sSQL nvarchar(500)

SET @sSQL ='SELECT * INTO '+ @tablename +' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;IMEX=1;Database='+@PATH+';'', ''SELECT * FROM [SHEET1$]''); '
EXEC sp_executesql @sSQL

Post #1239351

« Prev Topic | Next Topic »

Permissions