Hello All, I have a requirement here that: User will uploads the excel files into a file server on a weekly basis. I am writing an SSIs package in the database server and that SSIS package will dumps the data from excel files(in the File server) to SQL Server 2005 destination. Since user will uploads the Excel files once in a week. My question is how can I get the names of that Excel files when the package runs each time. Please provide me any links or any steps to follow. The name format of the Excel file is Sitecheck-Monthly_datetime.xlsx Thanks you so much All Great Regards

Please check this http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspxhttp://uk.linkedin.com/in/ramjaddu

See How to: Loop through Excel Files and Tables by Using a Foreach Loop Container http://msdn.microsoft.com/en-us/library/ms345182%28SQL.100%29.aspxArthur My Blog

Also this post must be helpful: http://bi-polar23.blogspot.com/2007/09/loading-multiple-excel-files-with-ssis.htmlArthur My Blog

Hello Arthur Z and RamJaddu, Thanks for the reply. How to get the exact excel file name that is uploaded in the file server using SSIS package. To search and get the file names similar to Sitecheck_Monthly_datetime.xls (here datatime is dynamic)... because the time part in datatime part is not same between uploding and package running time. Although I use C# code to upload the excel sheet and run the SSIS package still the time part is not same. what to be defined in Variable mapping and what to define in data source connection string properties. Thanks

You just loop through the folder contents based on a whildcard of Sitecheck_Monthly*.xksArthur My Blog

Please read through the links posted. They contain the exact steps you're looking for - your scenario is extremely common. Talk to me now on

From your description I understood that part, but my question is, say for example I have Sitecheck-Monthly_05162011.xls (last week's excel sheet) and Sitecheck_Monthly_05242011.xls (this week's excel sheet). If I schedule this SSIS package to run weekly, by now the file Sitecheck-Monthly_05162011.xls would have ran and now I only want to run the excel file Sitecheck_Monthly_05242011.xls (which is this week's excel sheet). My question is how to specify and where to specify in th SSIS package that SELECT THE EXCEL SHEET WHICH IS related to THIS WEEK. I am sorry if I ask a silly question. Thanks

I have recently modified a package to capture the file name http://geekswithblogs.net/Compudicted/archive/2011/05/17/how-to-ftp-and-process-anyhow-named-file.aspx Perhaps you can extend/modify that further into using the filter based on the date. So the logic is as follows: You cycle through all the Excel files (*.xls) and get the file name; If the file name matches the current week pattern you process it (check using a script task or a precedence constraint). Cut off the non-date portion and use a SSIS expression like: DATEPART("week", (DT_DBTIMESTAMP)Package::MyFileNameDateOnlyPart) == DATEPART("week", (DT_DBTIMESTAMP)GETDATE()) Load that file or skip based on the #2 Arthur My Blog

Hello Arthur, I did the exact steps that are mentioned and I got the error below. The excel file is in File server (file server and web server are same) and I developing the SSIS package on the database server. TITLE: Microsoft Visual Studio ------------------------------ Error at Sitecheck_Analog [Connection manager "Excel Connection Manager"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. ------------------------------ ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ BUTTONS: OK ------------------------------ Note: Sitecheck_Analog: is My package name Excel Connection Manager: Connection Manager to connect to Excel (data source) Please let me know where it went wrong. Thanks

Probably a wrong path to the Excel file.Arthur My Blog

My excel file is located in the D drive of the file server and he is the path that I gave folder in which my excel files will be there: \\servername\d$\\servername\SiteChecks\Analog Data source properties: connection string: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\servername\d$\servername\SiteChecks\Analog\Sitecheck_Monthly.xls;Extended Properties="Excel 8.0;HDR=YES"; excelfilepath: \\servername\d$\servername\SiteChecks\Analog\Sitecheck_Monthly.xls please let me know where went wrong Thanks

My excel file is located in the D drive of the file server and he is the path that I gave folder in which my excel files will be there: \\servername\d$\\servername\SiteChecks\Analog Data source properties: connection string: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\servername\d$\servername\SiteChecks\Analog\Sitecheck_Monthly.xls;Extended Properties="Excel 8.0;HDR=YES"; excelfilepath: \\servername\d$\servername\SiteChecks\Analog\Sitecheck_Monthly.xls please let me know where went wrong Thanks Folder has \\ in front of servername?Arthur My Blog

Also one single file in there named Sitecheck_Monthly.xls?Arthur My Blog

Does the account the package is being executed under have rights to see D$? D$ is an administrative share - so unless you're running the job under an administrative account, I'd don't think it'll work. Use a properly shared UNC, or a local path (not a mapped drive). Talk to me now on