Hi Arthur! I spent alot of time actually trying implement this example, however, it uses txt files and a flat file connection manager instead of an Excel Connection Manager. It seems (or I can't figure out) how one would dynamically use an Excel Connection manager by passing in a variable which would contain the file name. I have renaming working perfectly but everytime the pachage reiterates to wait for a new file I get a Connection OLE DB error because the file which is the datasource is no longer than and has been renamed to the new name. I also have DelayValidation set to True thoughout the Package. I am almost there. Can anyone please help me figure out how to make an Excel Connection Manager variable? (Not a Flat File Connection Manager which can't be used for Excel, I assume). Thanks!!!! MikeMike Kiser

Hello! I have developed an SSIS Package which consists of a FileWatcher (WMI EVENT Watcher Task) within an Infinite Loop Container. When a file is dropped into a folder the Package reads the file and exports the data a SQL Server Database and stores the data. Everything is working perfectly when I use a fixed file name (test.xlsx) and have an Excel Connection manager pointing to that file. HOWEVER, in my real system I need to drop a variable Excel file name (any Excel file with the same structure./data), for example, testmore.xlsx, and I need it to be processed the exact same way. I have a workaround but it is poor for the user; that is to rename their new file to the same test.xlsx filename and drop it on the folder, but that will be a pain for the user. Can anyone please help? Thanks! MikeMike Kiser

put the file name in a SSIS variable and use it all over the objects within SSIS a good example is the excel file name setting in this example Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

SELECT * FROM __InstanceCreationEvent WITHIN 10 WHERE TargetInstance ISA "CIM_DirectoryContainsFile" and TargetInstance.GroupComponent= "Win32_Directory.Name=\"c:\\\\WMIFileWatcher\""makes it watching for any file in a given directoryArthur My Blog

Hi Arthur! I did that and it does accept any file, however, my Excel Connection Manager will not function unless I can somehow use a variable name for that. If I use a fixed name, sss17.xlsx before I run the package it will accept that particular file. How can I use a variable for an Excel Connection Manager? Thanks!! MIKEMike Kiser

Hi Mike, Sure then once the WMI sees the file you pick it next through a ForEachLoop set to the foreach file enumerator that has the capability to capture the name of the file and map it into a variable that you will use to drive the conn string of the Excel connection managerArthur My Blog

Thanks! I have it all set up like that except "map it into a variable that you will use to drive the conn string of the Excel connection manager". Could you elaborate on this? I can't figure out how to do this and have googled....I think when I do this everything will be working. MikeMike Kiser

Any time Mike, see an example: http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx You will just have the ForEach Loop inside the WMI loopArthur My Blog

Hi Mike, We can set variable in Excel Connection Manager Expression Property, and then using dtexec utility to pass the path of the new file to the package as a variable. Detail steps please see: http://bidn.com/blogs/kylewalker/ssis/997/setting-up-an-ssis-package-with-a-dymanic-excel-source Thanks, Eileen