Microsoft SQL Server Community Samples: Integration Services

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

Commented Issue: Unspecified error while using SharePoint Destination Adapter [10828]

kevinidzi — Fri, 20 Nov 2009 04:26:00 GMT

I am getting UnSpecified Error while SharePoint Adapter is trying to insert items in the list.
How do I check what is the error?

Commented Issue: Svcutil converts Linq.Xelement types to XML.XmlElement in the proxy class [10924]

kevinidzi — Fri, 20 Nov 2009 04:24:51 GMT

I am having a issue with svcutil. I am having a WCF service with Xelement type to carry data from client to the service (request objects). When i create a proxy class using svcutil, for the client (simple C# windows app) to consume every Linq.XElement type is converted to XML.XmlElement. Now the client will not compile as it expects Linq.XElement

The suggested solution to this issue (Find and replace XML.XmlElement ) is the only way.? it works fine, but its a pain, esp in the development phase when the webservice keeps changing a lot, and proxy needs to be generated often. Whats the catch here any ways.? Why it should do this Xelement inherits ixmlserializable so it should work fine..

Can anyone help me here..

Cheers
Om

Created Issue: Does SSIS Sharepoint adapter work with a Contacts List as a Target? [10969]

preachuk — Thu, 19 Nov 2009 21:11:09 GMT

When I try to use a Contacts List as a target, the adapter appears to work but the [Last Name] Sharepoint field field is not mappable. It does not appear in the Output column list. I noticed in Sharepoint that the column is defined as a required field and that it is of type 'Item' rather than 'Column'.

Can Contacts lists be used with this SSIS adapter?

The reason I ask is that we are have been told that within Sharepoint only Contact Lists can be synchronized with Outlook (and that Custom Links CANNOT be synched to Outlook). We are triyng to build Master Contact lists from external SQL Server DB sources that can be sent to Outlook.

Thanks!
Bill.

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.

Reviewed: SharePoint List Source and Destination (Nov 18, 2009)

pjacob — Wed, 18 Nov 2009 23:30:18 GMT

Rated 5 Stars (out of 5) - This adapter was a tremendous help to the project and is part of a robust system integration.

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

Created Issue: Svcutil converts Linq.Xelement types to XML.XmlElement in the proxy class [10924]

omapte — Wed, 11 Nov 2009 16:03:04 GMT

Commented Issue: Compiling the sample [8568]

omapte — Wed, 11 Nov 2009 15:57:45 GMT

Hi,

It's more of a question than an issue (although it is an issue for me!).
The sample compiles fine if I do not touch service references. If I update even one of them, all references to System.Xml.Linq.XElement are replaced with references to System.Xml.XmlElement. So my Visual Studio (2008, with the latest service pack) does not for some reason generate service stubs that work with LINQ.
I tried to generate the proxy classes on command line by using svcutil with targetClientVersion:Version35 switch - does not make any difference.

Your auto-generated file was created by a tool with "Runtime Version:2.0.50727.3031". Mine is "2.0.50727.3053".

So the question is - how to generate a web service reference so the proxy class uses LINQ types?

Many thanks
Mike

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?

Reviewed: SharePoint List Source and Destination (Nov 10, 2009)

CharlesAsbornsen — Tue, 10 Nov 2009 16:22:47 GMT

Rated 4 Stars (out of 5) - This is a great addition to my SSIS toolbox, but it's a little awkward in some respects. For example, I discovered that when I tried to insert datetime columns to lists, I would lose the time value (i.e. mm/dd/yyyy 12:00 AM). I was able to correct this by converting the datetime to a string (DT_STR) in the dataflow before loading. I'm sure this will be fixed in future releases. Good work!

Reviewed: SharePoint List Source and Destination (Nov 09, 2009)

anyam_99 — Mon, 09 Nov 2009 20:50:19 GMT

Rated 1 Stars (out of 5) - Can't get the SQL Server 2005 version to work.

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

Commented Issue: Unspecified error while using SharePoint Destination Adapter [10828]

cs51tg — Thu, 05 Nov 2009 10:37:01 GMT

I am getting UnSpecified Error while SharePoint Adapter is trying to insert items in the list.
How do I check what is the error?
Comments: ** Comment from web user: cs51tg **

Hi,

We too are getting a similar errors when trying to insert or update items in a list. The process to update items in SharePoint has been running for several months now without error. Please advise how to further diagnose the problem. SSIS error output is below:

Beginning of package execution.
Error on row ID="1": 0x80070005 - Unspecified Error - Check SharePoint Server Logs if possible.
Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.PipelineProcessException: Errors detected in this component - see SSIS Errors at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "New Data List" (15515) failed with error code 0x80131500. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0x80131500. There may be error messages posted before this with more information on why the thread has exited.
End of package execution.

Not much to go on, I appreciate, but where else should we be looking.

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

Reviewed: SharePoint List Source and Destination (Nov 03, 2009)

salemboot — Tue, 03 Nov 2009 22:01:52 GMT

Rated 5 Stars (out of 5) - Deffinately the skeleton key you need

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.