| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2009-01-29 : 16:36:53
|
| what data access mode should I use?...I expected to see something 'excel'-like...1. I want to import data from an excel sheet in a sharepoint document library.2. I created a new connection in connection manager, using the 'EXCEL , Connection manager for Excel files' in the Add SSIS Connection Manager wizard. It took my web url to my excel file in the sharepoint doc lib, and I set the excel version appropriately (97-2005). I do note that I am on office 2007, but the save as option inlcudes Microsoft Excel 97-2003...I dont know if the 2003 v. the 2005 in SSIS matters...3. I create a data flow task, and open it.4. In the dataflow tab I pull in the Excel source icon. double click. In the OLE DB connection manager I select the excel connection. The options I have in data access mode are: table or view; table name or view name variable; sql command; sql command variable. I left it as table or view (??)...5. When I select Name of excel sheet, I get a loading message then a warning box "Microsoft Jet Database Engine !Failure creating file."In the Name of Excel sheet box, "No tables or view could be loaded...what am I missing? |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-29 : 17:46:03
|
| does your excel file containt any sort of data and/or columns? Cut paste sample hereor it could be rights issues. try copying the file locally or to a folder on the sql server I did just as you said (sin Sharepoint) and it works fine |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-30 : 09:39:45
|
| MetricID ReportWeek Target Value1 Value2 Value3 Value4 Value5 MetricValue84 12/06/08 870000 900000 1.03448275984 12/13/08 870000 750000 0.86206896684 12/20/08 870000 800000 0.9195402384 12/27/08 870000 923000 1.06091954I redid the excel source editor, and when I click the down arrow fo the 'Name of the Excel sheet:" field, I now get "Microsoft JET Database Engine... ! Failure creating file."....maybe I did not connect the excel sheet correctly in the connection manager? not sure if there is a way to test the connection...?/ |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-01-30 : 09:39:51
|
| MetricID ReportWeek Target Value1 Value2 Value3 Value4 Value5 MetricValue84 12/06/08 870000 900000 1.03448275984 12/13/08 870000 750000 0.86206896684 12/20/08 870000 800000 0.9195402384 12/27/08 870000 923000 1.06091954I redid the excel source editor, and when I click the down arrow fo the 'Name of the Excel sheet:" field, I now get "Microsoft JET Database Engine... ! Failure creating file."....maybe I did not connect the excel sheet correctly in the connection manager? not sure if there is a way to test the connection...?/ |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-02-03 : 09:20:39
|
| My problem seems to be in the permissions to the sharepoint document library where the excel sheet is stored...I moved the excel file to my desktop, and redid the connection to that file and the script works fine....thanks for the suggestions! |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-02-05 : 14:30:26
|
| my problem was not permissions..its worse than that..turns out to read an excel file in a sharepoint library using an ssis script..requires writing some custom code to access the sharepoint web service...which is beyond the scope of my skills...So, I just created a shared folder in my networked file system, for staff to submit excel sheets to update my monthly metrics...as sharepoint solution would have been more elegant (with versioning and email option to mail to the sharepoint doc lib)...but hey.... I need to keep it simple....thanks for all the advice!update - as both sql and sharepoint are running on 64-bit servers..apparantly the Microsoft.jet.oledb.4.0 was not ported into the 64 bit environment..yet....so the jet driver failed... |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-06-08 : 12:47:58
|
| I was wrong...you do not need custom code..the problem with accessing a sharepoint file in a document library, is that you cannot use a url..you have to use the UNC address....\\server.gov\exec\scorecard\Manual Submissions\metric 117.xlsworks..https://server.gov/exec/scorecard/Manual%20Submissions/metric%20117.xlsfails... |
|
|
|
crksk2002
Starting Member
2 Posts |
Posted - 2009-07-08 : 16:11:29
|
| Was there anything more to accessing the sharepoint doc lib? I am trying to copy excel files to sp doc lib which I can in BIDS but I get an invalid connection error when I deploy the package to integration services. Any help would be appreciated, thanks. |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-07-08 : 18:33:37
|
| check that: - you can only use excel xls files... - I also have turned on excel services for my doc lib, but I really dont think that matters for ssis... - Use the excel connector...Microsoft Excel 97-2005 (I check First Row Has Column Names) - my connection string looks like this:\\mysharepointsite.illinois.gov\ops\scores\Manual Submissions\Metric 01.06 Ave days to billing.xls |
|
|
|
crksk2002
Starting Member
2 Posts |
Posted - 2009-07-09 : 10:31:06
|
| Are you using a config file? |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2009-07-09 : 11:24:27
|
| Likely not, as i do not know what a config file in this context (ssis, sharepoint) is...? |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-06-03 : 10:39:49
|
| update...and I found you cannot have any %20's in the UNC string...\\portal.gov\exec\scorecard\Manual Submissions\WintelMasterSheet-AGR.xlsworks\\portal.gov\exec\scorecard\Manual%20Submissions\WintelMasterSheet-AGR.xlsfails |
|
|
|
dan.ling@pcubed.com
Starting Member
4 Posts |
Posted - 2010-07-09 : 08:56:24
|
| I uisng SSIS 2005 SP3 with SQL2005 and can get the excel data to import iwht SSIS when the Excel file is located in the file system and not in a SharPoint Doc Lib. I've tried using the UNC path and removed the "%20" from the connection string but still getting errors. This import if from SharePoint 2007 and a .xlsx Spreadsheet.Path:\\oekawsapvs04:81\PWA_ENG\HBAS Master Project List\LTP Costs\LTP Cost Tracking.xlsxError:Microsoft Data Link Error: Test connection failed becasue of an error in initializing provider. Failed to create file. |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2010-07-12 : 10:02:40
|
| Morning Dan...try saving the excel file as a 2005 version...xls...then try your script again... |
|
|
|
|
import excel from sharepoint