SQLSrvIntegrationSrv Forum Rss Feed

New Post: fixed SiteUrl, now problems with SiteListName

RobinTheHood — Fri, 20 Nov 2009 20:32:24 GMT

I got past the previous error by putting in litterally http://sitename.org/Pages/Default.aspx for the SiteUrl value.

I'm trying a local reference to the SiteListName with "/Lists/Tasks/" and that produces an "Error at Data Flow Task[SharePoint List Source [1]: System.ServiceModel.ProtocolException: The content type tezt/html; charset=utf-8 of the response message does not match the content type of the binding (text/xml; charset=utf-8) etc...

hints gratefully accepted.

/Robin

New Post: SSIS: SharePoint List Source and Destination + SoapServerException

RobinTheHood — Thu, 19 Nov 2009 17:55:26 GMT

This is probably a real silly question, but since I have not found it in a FAQ or any where in discussion, I'll ask it here.

After installing the SharePoint List Source and Destination msi, I added the tools to the data aflow toolbox, dragged a SharePoint List Source object onto the design surface and opened the Advanced Editor.

I added a SiteUrl and SiteListName, leaving the SiteListViewName blank, and I get an "Error at Data Flow Task [SharePoint List Source[21]]

Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unhandled SharePoint Exception ----> System.ServiceModel.FaultException: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.

I think I'm using the correct syntax for the SiteUrl and SiteListName values.... namely SiteUrl is http://sitename/ and the list name is relative "/collaborate/listname"

any clues gratefully recieved..

/Robin

New Post: SQL decimal to SP number issue

pjacob — Thu, 19 Nov 2009 01:31:19 GMT

As a follow up on this, I came across another reason to use this SSIS SharePoint adapter, with the variation on the SharePoint destination datatype.

Need: mapping from a SQL numeric(13,2) -> SharePoint currency

In SharePoint, I see currency's underlying datatype once again is DT_R8 - revealed inside the SSIS advanced properties of the SharePoint List Source, External Columns > DataType section. It'd been a while since touching this part of the solution, so I had to retrace my original solution posted above. As it turns out:

Solution: in SQL prior to retrieving the numeric column using the OLE DB source, convert the column into a float -- cast(column1 as float)

Afterwards, in SSIS make sure to remove the outbound OLE-DB source mapping & re-add the mapping (so it pulls the correct float/DT_R8 datatype) or edit the advanced properties on the green data flow coming from the OLE-DB source, then edit the datatype of the specific Output Column.

I'll post the complete SSIS walkthrough with glorious pictures on my blog; http://pritishwrites.blogspot.com

& thanks for the community contribution Kevin.

New Post: Upgrade from SQL 2005 to 2008

heibeili — Fri, 13 Nov 2009 03:58:36 GMT

Hi,

I have downloaded SharePointlist Adapter for SQL 2005. And we plan to upgrade sql server 2005 to 2008. Do I have to reinstall the SharePointListAdapter for SQL server 2008? Do I have to recreate package for sql server 2008?

Thanks

New Post: SSIS : SharePoint List Source and Destination + Excel

arioamir — Wed, 11 Nov 2009 09:02:37 GMT

hello everyone

i have 2 problems

1-i wanna import data from excel to sharepoint list but i can't import data because that i have lookup field in sharepoint list.

what's your suggestion?

2-i can't imoprt data from excel to sharepoint list(whitout lookup field in sharepoint list) because i have 12,000 records in excel.

when run this program , excel source read 700 records only...

what's do you think about it?

New Post: SharePointUtility DeleteItem idList

SteveMorris — Mon, 09 Nov 2009 17:50:37 GMT

I need a sample of how to build the idList, the third parameter used to delete a file using the SharePointUtility. I'm using a For Each Loop to copy files from a Sharepoint library to a file server. I need to delete each file after it is copied. I don't have the sharepoint file id available but I do have the file name.

thanks

New Post: EzApi for sql server 2005

jibin — Wed, 04 Nov 2009 09:07:57 GMT

Dear all,

Please tell me how to convert the eZapi[c# code] from sql server 2008 to sql server 2005.

thanks,

Jibin

New Post: No Input columns

thomastx05 — Sat, 31 Oct 2009 21:34:45 GMT

Ok so aparently I just neded to push refresh on the column mapping page. all is well.

New Post: No Input columns

thomastx05 — Sat, 31 Oct 2009 19:00:18 GMT

I bed that I seam to be the only one with the problem, but when I creat a sharepoint list source it connects fine but it show I have no columns for mapping. There are several columns however in the list they just do not show up in ssis.

New Post: SSIS Copy files form and to SharePoint document library

mweaver59 — Fri, 30 Oct 2009 11:55:42 GMT

I am trying to automate a files copy rename from source SharePoint Document Library to a Destination SharePoint Document Library with SSIS

There will be about six files posted once a week to source folder with the same name every week and will overwrite the file in the folder.

I need to copy and rename the files to the SharePoint Document Library archive folder before they will be overwritten and add the file date to the file name

The reason fro SSIS is because that is all that I am aloud. I just have bids 2005.

New Post: SSIS / SharePoint Source and SQL Agent issue

Larcher — Tue, 27 Oct 2009 16:53:14 GMT

Well ...i found my problem (after 4 hours spent on it !!) i put the SQL Agent account admin of SharePoint, but it seems that i need to declare this account to the SharePoint child List too

New Post: SSIS / SharePoint Source and SQL Agent issue

Larcher — Tue, 27 Oct 2009 16:47:40 GMT

I built a package on SQL 2005 with the SharePoint source component.

This package works on Business Intelligence Studio, and with SSIS (ran manually)

I put this package on a Job on SQL Agent : i got this message when i ran the job :

Executed as user: HAYWARD\SQL. ...ersion 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 17:34:47 Error: 2009-10-27 17:34:51.06 Code: 0xC0047062 Source: TRANSFERT SharePoint List Source [1] Description: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unspecified SharePoint Error. A possible reason might be you are trying to retrieve too many items at a time (Batch size) ---> System.ServiceModel.FaultException: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout) at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean on... Process Exit Code 1. The step failed.

I dont use the same Account for package designing and SQL Agent Account, anyway, both of them are setup on my sharepoint website.

Can you help me ?

regards

Thomas

New Post: Update the SQL table whne a new item is added in a sharepoint list

scvinod — Tue, 27 Oct 2009 05:14:43 GMT

Hi kevinidzi,

Thanks for your reply. Well, that is what I'm looking for.

New Post: Update the SQL table whne a new item is added in a sharepoint list

kevinidzi — Tue, 27 Oct 2009 04:44:54 GMT

With this component, you cannot update when a new item is added. But you could schedule a job to run every X minutes and check for any new items since the last time, and update the new items as appropriate. If that might be a scenario you are looking for, then we can help you out.

New Post: Update the SQL table whne a new item is added in a sharepoint list

scvinod — Wed, 14 Oct 2009 18:33:31 GMT

Hi All,

I have created a Integration Service Package with a SPList as the source and a SQL Table as the destination. I want the SQL Table to get updated when a new Item is added in to the SPList. I'm new to this.Can anyone please help me out.Thank you.

New Post: Update SP List using Sharepoint Destination List

Thehammer848 — Tue, 13 Oct 2009 09:51:37 GMT

Kevin

Thanks for responses on this issue. I think I have discovered the problem. The list that I am trying to update has a connection back to an application database and is synchronized by a process supplied by the vendor. I created a duplicate list that was not set up to be synchronized and I was able to update 1900+ records in a few seconds.

The way the synchronization works is when sharepoint is updated it immediately tries to update the SQL database with the new information. Before it tries to update the database it has to check the application security to ensure the person doing the update has permissions in the application to update that data. I think the added processing of trying to update the application database and having to check the security is adding the additional time, and processing, so that the update is timing out.

I am going to check with the vendor about this and see if there’s anything that we can.

Again, thanks for your help on this matter.

Jon

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.

This email has been scanned and certified safe by SpammerTrap(r)

New Post: SSL - Could not establish trust relationship for the SSL/TLS secure channel.

seanrob — Tue, 13 Oct 2009 09:14:33 GMT

Yeah - basically I think I need to implement some sort of callback interface so that when the certificate validation occurs this event handler captures it and then returns true. Ideally this would be some sort of option in the interface (Ignore SSL Cert Errors) because I'd imagine lots of people will hit this exact same challenge when working in preprod/dev environments. So I know what to do - in theory - but I'm just a lowly SQL Dev so putting this into practice is proving somewhat overwhelming for me at this point in time. Any tips and pointers on how to do this would be greatly appreciated. Certainly step one would be just to implement the interface (but which interface, where???) and then step two would be to make this a parameter.

Thanks

Sean

p.s Big shoutout to all the people who put this together - it rocks my world and has solved so many of my sharepoint challenges and opened up so many opportunities for us to capture simple additional datapoints for reporting in our BI environment!!

New Post: Https for SiteUrl

kevinidzi — Tue, 13 Oct 2009 06:49:16 GMT

Hm, you shouldn't have to do that. If you enter that exact URL into the Browser, I assume it brings up your site? Try appending this to your site url:

/_vti_bin/lists.asmx

If you get a webservice page, then your root url (before the first slash above) is what needs to go in the config.

New Post: Update SP List using Sharepoint Destination List

kevinidzi — Tue, 13 Oct 2009 06:46:58 GMT

Wow, you must have a very wide row to make it timeout at that small a size.. Did you try a worst case batch size of 1 and see if you still get that issue?

Due to the way that it is an XML blob going over the wire, the batch size is the only way to really tweak that. Perhaps the timeout could be increased, there has been requests to make that a bit more configurable. I am thinking of making some updates shortly to the component, i'll keep this in consideration if i do so.

New Post: Updating site column with multiple values in share point list destination

kevinidzi — Tue, 13 Oct 2009 06:44:35 GMT

How are you creating a separated list? One thing to keep in mind is that the delmited list is actually 2 elements each.. ID/Name;ID/Name .. Name can be blank in the case where you are updating and giving an ID, but you have to have the space for the name. You can't just do ID;ID;ID