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


How to load excel file with dynamic filename??


How to load excel file with dynamic filename??

Author
Message
jyoti_bhatt
jyoti_bhatt
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 98
Any ideas how I can load a excel file with a dynamic filename?

Many Thanks for your help
Dan.Humphries
Dan.Humphries
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3331 Visits: 1212
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.
jyoti_bhatt
jyoti_bhatt
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 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...
Dan.Humphries
Dan.Humphries
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3331 Visits: 1212
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.
SSIS_NewBi
SSIS_NewBi
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 118
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
divyanth
divyanth
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1459 Visits: 556
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...
signforarun
signforarun
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 65
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
sarwar.ali490
sarwar.ali490
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 41
Hi All,
I got a requirement to load 40+ flat files in staging table along with file date and file name for each file.I could use for each loop to load the files at a time but confused about using variables in script task and also to get two extra columns in a table
1.For file modified date
2.For file name.

Can anyone help with detail steps to achieve this.


Thanks.
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