Do I have to run BI Dev studio and the component on the Sharepoint server?

Feb 17, 2009 at 11:43 AM
Hello,

I'm developing a simple DTS package on my local machine to import a list from Sharepoint to a SQL table.  I followed each step in the tutorial on MSDN. When I run the debug I get lots of error.  Am I suppose to develop directly on the Sharepoint server?

Thanks.
Coordinator
Feb 17, 2009 at 2:01 PM
Nope, you just need access to the webservices, which if you can get to a SharePoint List, is all you need.  What kind of errors are you seeing?
Feb 17, 2009 at 6:56 PM
Hi thanks for answering fast.

Here's the error I get:

---------------------------------------------------------------------------------------------------------------------------------------------
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0047062 at Data Flow Task, SharePoint List Source [1]: Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
   at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)
   at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
   at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "SharePoint List Source" (1) returned error code 0x80131600.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED.  Thread "SourceThread0" has exited with error code 0xC0047038.  There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
---------------------------------------------------------------------------------------------------------------------------------------------

I've tried many list with either many or few columns ..

Thanks.
Coordinator
Feb 18, 2009 at 7:31 AM
OK.. it looks like it is having a mismatch on one of your columns.  If you could export your list to something like Excel and attach that, then I can grab it and make it into a list and try to repro it.  Is there a column with a LOT of data in it? This looks like an internal bug of some sort. 

I assume you're using the most recent version, right?

Core Error:
Microsoft.SqlServer.Dts.Pipeline.DoesNotFitBufferException: The value is too large to fit in the column data area of the buffer.
   at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetString(Int32 columnIndex, String value)
Feb 18, 2009 at 4:27 PM
Hi,


I found out the column that caused problems.  the DataType was Unicode string [DT_WSTR] and lenght =10. I had to remove one column at a time to find out which one was causing problem.

I'm new to DTS package so I don't know if there is a easier and quicker solution.

Now I just have to figure out how to remove the Sharepoint code ;# before uploading the table into SQL..

Thanks.
Coordinator
Feb 18, 2009 at 6:25 PM
OK, well, i'm still not sure why you'd have a problem with that. What language are you using? Is it a real unicode string that is in there? I tried to use the setntext like statements but they kept erroring, i could only get it to work with SetText (which is not unicode friendly). 

As for the ;# in the Lookup columns, you can make a script dataflow item where for each row you can just rip that off and put the updated column in the output.

Feb 18, 2009 at 6:40 PM
Here a sample data of what is in this column:  20081011-023139

In Sharepoint, it is set a as a single line of text with 10 char max.

As for the ;#  can you tell me if there's a tutorial somewhere on how to clean that up?  I have no idea how to script that.  Like I said, I'm a newbie in SSIS and I haven't don such thing yet.
Coordinator
Feb 19, 2009 at 9:30 AM
As for the ;#, not sure if there is a tutorial, you can probably post in the SSIS Forums to get a good response / pointer to a tutorial on scripting.
As for that data, that sure doesn't look like 10 chars :)  Should it be changed to 15?
Feb 19, 2009 at 11:24 AM
Alright, thanks for all the info.  I have to say I'm pretty impressed with your quickness to answer my questions.  Thanks a lot.

If I figure out how to remove the character I'll try to post a little How-to in the Discussion zone.

Thanks again, very appreciated!
May 4, 2009 at 8:53 AM
Hi microcontoleur,
 I am having issue where-in I am getting String;# in the data when its exported to SQL. as you mentioned that you have cleaned it up before loading to tables. Could you please share how you got rid of ;#?

Thanks a lot for your help.

jammyg
May 4, 2009 at 11:56 AM
Edited May 4, 2009 at 11:56 AM
Hello jammyg,

I just started a discussion to explain how.  Check it out here.  Hope it can help.