June 7, 2010 at 8:18 am
Any ideas how I can load a excel file with a dynamic filename?
Many Thanks for your help
June 7, 2010 at 8:34 am
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.
June 7, 2010 at 8:40 am
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...
June 7, 2010 at 9:10 am
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.
June 7, 2010 at 9:41 am
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
June 8, 2010 at 7:45 am
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...
January 20, 2012 at 5:32 am
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
June 16, 2016 at 3:59 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply