Beginning SSIS - Sharepoint and Excel

Hi all, I have never used SSIS before, but am looking to use it in this aspect:

1) A user uploads an Excel file into Sharepoint. This will be through a Document Library in a Sharepoint page.

2) When this file is uploaded, I'd like SSIS to notice there is a new file and process it - it will pull information from the Excel file, put it into a database, and - if this is possible - delete the new file.

3) This is iffy. Can SSIS then generate an InfoPath document from the information stored in the database? If not, I can just have a InfoPath query form.

I'd just like to know if this is possible. If you have any helpful links, please let me know - I would greatly appreciate it!

Thanks,

James

James,

2) SSIS can do all of this. You can use the FileWatcher task as provided on SQLIS.com. Deleting the file is easy.

3) No idea. If there is an API for InfoPath then it should be possible.

-Jamie

|||Thanks for the reply, Jamie. I'll take a look at that.|||

Alright, well I've taken a look and I'm having some issues -

In Control Flow, I have the file watcher watching a directory for any new Excel files and the output of the filewatcher is User::NewExcelFile, which I understand will be a string with the information of the type "file.xls".

I also have an Excel connection manager which points to the format of the Excel file that will be used. Then I have a SQL server destination that is mapped to the database the information will be put to. How does the File Watcher control flow task pass the information of the filename to the Excel/SQL data flow tasks? They don't allow variable inputs.

Thanks,

James

|||

The Excel source component uses an Excel Connection Manager which has a property called ExcelFilePath that contains the name and location of the file.

You can set this property using a property expression. (Select the connection manager, press F4, expand 'Expressions' in the properties pane). Set it to the value returned by the File Watcher.

-Jamie

|||

Thanks Jamie, that works - I assume as File watcher notices a new file, it triggers the following control/data flow.

Is there a way to set the Excel file's name to be something dynamic? For example, to watch for an Excel file of any name, not just the name of the Excel file specified in the Excel connection manager? File watcher appears to only watch a path, which it can pass to the connection manager when triggered by a new file upload, but the Excel Connection Manager needs a static Excel name.

And what is the task for deleting the Excel upon completion of the service?

Thanks for your time! I am new to SSIS and coming to grips with it :)

-James

EDIT:

Sorry, let me rephrase:

I set the File Watcher Output Variable Name to "User::NewExcelFile" - I assume that this contains either the path to the containing folder OR the containing folder + the name of the file.

If it is the path only, then how can I set the Excel Connection manager to get the name of the file as well?

And if it is the path and name, how do I parse the @.User::NewExcelFile to put the file path in ExcelFilePath and the name in Name?

Also, when the ExcelFilePath is set to @.User::NewExcelFile, I cannot set and save the file path in the Excel Connection Manager. It clears the field - this does not let me map the Excel field to the Sql destination.

|||

James,

The output variable will contain the path and the name of the file.

You need to put the whole thing (the path and the name) into ExcelFilePath property. So there's no need to parse it. The Name property is just the name of the connection manager. Its irrelevant.

Regarding the last point, the reason the field is cleared is because there is nothing in @.User::NewExcelFile. Put a path in that variable to an excel file that has the same metadata as the file you will be processing at runtime. This value is only used at design-time as it will be overwritten at runtime by the FileWatcher Task.

-Jamie

|||

I used file system watcher to read excel on my pc it worked fine, but when I tried to read the excel from SharePoint it did't work. The FileWatcher box showing the yellow color for long time that I had to stop the ssis.

So my question what is the cause of this. Do i need to set something or am i missing something? Please help.