Microsoft SQL Server Community Samples: Integration Services

New Post: Extracting numbers from Sharepoint generates zeros

boris_w — Wed, 10 Mar 2010 16:30:02 GMT

Hi,

First of all, this is my first post here. I am from Germany and using the Sharepoint List Adapters for my Master-Thesis.

I have encountered a problem when extracting numbers from a Sharepoint List. If I have the number, let's say, 160, in Sharepoint it is viewed as 160. But when I load the list in SSIS, converting the number from DT_R8 to any other data type (I tried numeric, int, unicode), it fills the number with zeroes, so the result in my SQL-table is something like 160000000000000.

That is a huge problem. Did anyone yet encounter the same problem and has a solution for it? I saw a discussion about problems with numeric to float conversion here, but it was for writing into a Sharepoint list and not this way.

If you need more information, please don't hesitate to ask. Thanks in advance.

Boris

New Post: Synchronise SQL table with SharePoint List

anits — Wed, 10 Mar 2010 03:49:42 GMT

Allen, thanks for the details. I have been searching for updation on sharepoint lists and found the way out here..Iam new to integration and this question may sound silly..

I used create and update sharepoint lists destinations...Even if only one row is updated, its looks like all the rows in the destination are getting updated.

Would this affect the performance on the system..Currently we have 1800 rows in our sharepoint list and when i update one row in my sql-database, I could see that its updating all those 1800 rows..

Is it possible to update just that one row which changed..

thanks,

Created Issue: SharePoint Utility DLL returns items recursively, but does not include folders [12023]

kevinidzi — Sat, 06 Mar 2010 07:44:38 GMT

This is a placeholder for me to update the ViewAttributes scope to make the Scope=RecursiveAll instead of Scope=Recursive.

New Post: Code for SSIS-SP adapters 2005

pscorca — Fri, 05 Mar 2010 10:47:01 GMT

Where is it possible to download the code about SSIS-SP adapters for SQL Server 2005, please?

Thanks

Created Issue: About inserting a datetime (DT_DBTIMESTAMP) with SP List Destination [12013]

pscorca — Fri, 05 Mar 2010 10:45:15 GMT

With the newest version of SharePoint List Adapters isn't possible to insert correctly the time part of a datetime in a list.
With an user suggest (converting datetime in a DT_WSTR with 50 chars) this issue is skipped but when an updated version
could be available to download?
Thanks

New Post: Writing a date + time inside a SP list with the Destination Adapter

pscorca — Thu, 04 Mar 2010 22:24:47 GMT

In the project code I can see these statements:

case DataType.DT_DBTIMESTAMP:
                                if (buffer.IsNull(_bufferLookup[fieldName]))
                                    rowData.Add(fieldName, String.Empty);
                                else
                                    rowData.Add(fieldName, buffer.GetDateTime(_bufferLookup[fieldName]).ToString("yyy-MM-dd hh:mm:ss"));
                                break;

Is it rigth to write yyy (3 chars for the year)?

Is it right to write hh:mm:ss? In the regional settings, hh indicates hour expressed with AM/PM but I cannot see "tt" after "ss";

moreovert, to render the hous as 24 hours format it is to use HH (uppercase) and not hh.

Perhaps it occurs to substitute "yyy-MM-dd hh:mm:ss" with "yyyy-MM-dd hh:mm:ss tt" or "yyyy-MM-dd HH:mm:ss".

To write correctly into a SP list, do I convert my SQL date in a particular date format, please? Thanks

New Post: Writing a date + time inside a SP list with the Destination Adapter

pscorca — Thu, 04 Mar 2010 14:36:00 GMT

Do I convert my SQL date in a particular date format? Thanks

New Post: Writing a date + time inside a SP list with the Destination Adapter

pscorca — Thu, 04 Mar 2010 13:22:54 GMT

Nothing results also if I set the time zone to Greenwich one.

Any helps, please. Thanks

New Post: Writing a date + time inside a SP list with the Destination Adapter

pscorca — Thu, 04 Mar 2010 12:36:35 GMT

I have tried to modify the time format of the Regional settings on the db server and the web server by changing it as H:mm:ss,

with nothing results.

Any helps is necessary to solve this issue, please. Many thanks

New Post: Writing a date + time inside a SP list with the Destination Adapter

pscorca — Thu, 04 Mar 2010 10:08:32 GMT

I'm using the SharePoint List adapter destination to write in a SP list from a SQL table. I have a datetime column with value expressed with date + hour + minutes.

I have two servers, the web server and the db server. On the db server I have installed the Adapters (SQL Server 2005 RTM): the Regional Settings is set to en-us

and the hour is set to AM/PM (but I think it is no matter). The web server that has the SharePoint content db are the same Regional settings (en-us + hour in AM/PM).

I'm using the same regional settings as site settings.

For a SQL datetime as 2010-03-19 10:00 PM, the adapter writes 2010-03-19 10:00 AM.

For writing in my list, in SSIS I must convert my SQL datetime in a DT_DATE: I have used a grid data viewer to observe the data passed before and after

the data conversion and before the writing and I can see the same datetime in the SQL table.

Any suggests to solve this issue, please? Many thanks

Source code checked in, #40668

kevinidzi — Wed, 03 Mar 2010 19:18:37 GMT

Added a deployment Diagram to the document to visualize how things need to lay out.

Closed Issue: Create Sample for a Full Install of a Custom Task/Component [8407]

kevinidzi — Wed, 03 Mar 2010 19:12:00 GMT

I've beaten my head on this one for a few days over the past weeks - and have gotten very close, but am still unable to create an installer that will:
1 - Copy the DLL to the PipelineComponents or Tasks folder (easy)
2 - Install the DLL into the GAC
3 - Add the component/task to the appropriate VStudio toolbox

It would be fantastic if you guys could work your magic, do a little nudge-nudge over to the Windows Installer and Visual Studio Extensibility folks if necessary, and cobble together a very simple end-to-end installer for just one of the custom components in this sample set, with an eye to creating simple instructions to use it as a template for packaging up other tasks/components.

It doesn't have to be complicated and install all of the components here in one shot - just one component at a time, per MSI/VSI (or whatever it ends up being) would be fantastic.
Of course, having a single "package" that could contain and automatically detect and install the appropriate BIDS 2005 and/or 2008 versions of a component would be uber-sweet too...

Commented Task: Error updating SP datetime fields by using SharePointListDestination [8528]

kevinidzi — Wed, 03 Mar 2010 19:05:23 GMT

I have a SQL Server datetime field and a SharePoint datetime list field. The SP datetime field hasn't the time. When I try to update a particular item of the list containing the SP datetime field with the SQL Server datetime field I have an error that says that the datetime is in invalid format. Why? How can I solve this issue? Thanks
Comments: Changing from Issue to Task since the task is to look at the otheritem mentioned which this was consolidated with.

Commented Task: How SharePoint List source returns the list fields? [8491]

kevinidzi — Wed, 03 Mar 2010 19:04:00 GMT

I'm trying to read the SP list field using SharePoint List Source, but when I read the SP numeric fields (float - DT_R8)
and then convert them in an eight-byte signed integer I see the number, that I put inside the list, followed by some zero digits.
Does SharePoint List source return the list numeric fields as nvarchar fields? Thanks
Comments: Changing from Issue to Task since the task is to look at the referenced work item :)

New Post: Sharepoint Utility DLL

kevinidzi — Wed, 03 Mar 2010 18:10:19 GMT

That is discussed in a closed issue:

http://sqlsrvintegrationsrv.codeplex.com/WorkItem/View.aspx?WorkItemId=8568

New Post: SP List Destination 0x8102001c

kevinidzi — Wed, 03 Mar 2010 18:07:35 GMT

Thank you for the followup, I was surprised to see issues with the date/time still popping up.

New Post: SSIS SharePoint List Source - Missing Columns

kevinidzi — Wed, 03 Mar 2010 18:05:56 GMT

Pull the data into a table and look at it, it is most likely there with a different name. Sharepoint might have other ideas for what the names of your columns should be :)

New Post: Update SP List using Sharepoint Destination List

kevinidzi — Wed, 03 Mar 2010 18:05:06 GMT

If you happen to just let the adapters pick all of the columns when they are not needed, then it updating fields you did not intend may happen as it 'passes' through.

In the adapter properties, you can turn off columns (can't do it in the default views) so that the adapter completely ignores columns you do not care about. It also reduces your impact if those fields are later dropped or changed.

New Post: Porting pkg on prod environment

kevinidzi — Wed, 03 Mar 2010 18:02:53 GMT

Yes - make the lists the same. The problem is that you have different underlying list column names. Generally this happens when you create a list in one environment, adjust the names, and then use Excel or something to create the list in the other environments.

The way to make this smoother is to make your list, and when you are ready to go to other environments, you either make the columns the exact same way - or you export to excel and dump to the other environments INCLUDING The dev environment (as a new list). Resync to the new list, and the other environments will adjust without warnings given.

New Post: List Item's version history in the pipeline

kevinidzi — Wed, 03 Mar 2010 18:01:04 GMT

No, this is a whole different API which turns out is not row by row, but field by field -- which makes a generic querying more complicated.