Jun 082011
 

Sometimes you run into a situation where you want to allow users to update data in a simple fashion, and incorporate the data into a data warehouse.  For example the client’s ERP system maintains the data element called Departments but doesn’t have the ability to store Department Groups for reporting since it is not relevant to the ERP system.  Using a SharePoint list as a data source can be an easy solution.  Users can update it very simply, your ETL package can add or update new Departments from the ERP system via the data warehouse, and you can pull the user entered data back into the data warehouse.  There are some drawbacks to it, such as lack of data integrity checks on the SharePoint side.  But if you build your ETL package right, you can correct any integrity issues which might be created by user error. 

Here is what I will demonstrate, using a SharePoint list to manage the applying Department Groups to Departments which are maintained in the accounting system:

PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS

PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE

PART 3 – INSERT RECORDS INTO THE SHAREPOINT LIST

PART 4 – UPDATE RECORDS INTO THE SHAREPOINT LIST

PART 5 – DELETE RECORDS INTO THE SHAREPOINT LIST

PART 6 – BUILD DATA INTEGRITY INTO YOUR PACKAGE

PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS

1. From the server where you will be building the SSIS solution, open your browser and navigate to this URL. http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

2. Download, run and follow the instructions to install SharePointListAdaptersSetup.msi (2008) or SharePointListAdaptersSetupForSqlServer2005.msi

image

3. Close BIDS if it is open

4. Open BIDS from the Tools menu select Choose Toolbox Items.  Go to the SSIS Data Flow Items tab. – **See SQL Server 2012 Update at the end of this post.

image

5. Add a checkmark to the SharePoint List Destination and SharePoint List Source items.

6. Open up or create a new Integration Services solution. Go to the Data Flow tab.

7. Open up the Toolbox

8. Drag SharePoint List Source from the General section…

image

…to the Data Flow Sources section.

image

9. Drag SharePoint List Destination from the General section…

image

…to the Data Flow Destinations section.

image

Now you are ready to use a SharePoint List as a source or destination in your packages.

PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE

1. Create a Data Flow in your package.

2. Go to the Data Flow tab and drag the SharePoint List Source into the data flow as your source.

image

3. Double click on the source.  Scroll down to the bottom where it says SiteURL.

image

4. Paste the URL for the SharePoint site into the SiteURL (exclude anything from the word Lists onward in the URL)

Example: if your SharePoint site URL for Lists is

 http://ca-shpt01/Reporting/Lists/Department%20Groups/AllItems.aspx

then only type

http://ca-shpt01/Reporting/

5. Type the SiteListName.  For example if your List name is Departments,

image

then type “Departments”.

image

6. If you are wanting to import data from a particular view of this list in SharePoint, other than the default view, “MyView” for example:

image

then add the name of the view in the SiteListViewName, otherwise leave it blank.

image

7. Click the Refresh button at the bottom of the box to load the SharePoint List metadata into your Data Flow.

image

8. Then click on the Column Mappings tab to see the columns available from your SharePoint list.

image

9. Drag a Data Flow Destination onto your data flow and proceed as normal to pull data into your destination from the SharePoint list.

You’ll need to know a little about SharePoint lists to understand what data lives in which column.  You’ll need the ID column if you will be doing any updating from the data warehouse into the SharePoint list.  The Title is whatever item is the linked item in the SharePoint list – meaning if a user clicks on one of the items in the SharePoint list, which field is set up as the hyperlink to the item (usually the first column visible in the list).

image

Because there is often a  push and a pull of data to and from the SharePoint list I tend to create a table in the data warehouse  specifically for the data from the SharePoint list and I let the source data dictate the table structure by clicking on the New button for the “Name of the Table or view”.  In this way, when the data gets pushed back to SharePoint there are no data type issues.

PART 3 – INSERT RECORDS INTO THE SHAREPOINT LIST

Keep in mind that the SharePoint list does not manage any data integrity, so if you add duplicate records with the same linked “Title”, Department Name in this case, you will get duplicate records in your list.  Be sure to configure your data source to insert only new records, not existing records. Here we will add any new Departments that have been added to the ERP system (if any) and push them up to SharePoint.

1. Add a Data Flow to your package.

2. Go to the Data Flow tab and drag an OLE DB source as your source. Configure your source to pull any new Departments that you want to insert into the SharePoint list.  In this example SP_Department is the data which has been imported from the SharePoint list, and DIM_Department holds the Departments imported from the ERP system:

SELECT   �
DP.Department_Code,
DP.Department_Description
FROM  SP_Department as SP RIGHT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
    WHERE      SP.ID IS NULL

3. Drag the SharePoint List Destination into the data flow as your destination.

image

4.  Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above.  In addition, ensure that the BatchType is set to Modification.  “Modification” is used for Updates and Inserts, “Deletion” is of course for Deletions.

image

5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab.  Map the Source columns to the Destination columns.  Since we are INSERTING records, be sure NOT to map to the ID field. Doing so would have the data flow expect an update rather than an insert.

image

Note that the Department_Description is being mapped to the Title field, which is the linked SharePoint field described in Part 2 – item 9, above.

6. Run your data flow task and check the SharePoint list for the added records.

PART 4 – UPDATE RECORDS INTO THE SHAREPOINT LIST

Here we want to update the Department descriptions in SharePoint, in case a user has decided to edit it.  The only item the user should be editing is the Department Group.

1. Add a Data Flow to your package.

2. Add an OLE DB Source to the Data Flow tab and configure the source with the correct query to update the Department descriptions for Departments that already exist in the SharePoint list.  For example:

SELECT   �
SP.ID,
DP.Department_Code,
DP.Department_Description
FROM  SP_Department as SP INNER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code 

3. Drag the SharePoint List Destination into the data flow as your destination.

image

4.  Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above.  Esure that the BatchType is set to Modification. 

image

5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab.  Map the Source columns to the Destination columns.  Since we are UPDATING records, BE SURE TO map to the ID field. Without the ID the data flow will implement an insert rather than an update.

image

6. Run your data flow task and check the SharePoint list for the updated Department Descriptions.

PART 5 – DELETE RECORDS INTO THE SHAREPOINT LIST

Here we want to delete any Department records from the SharePoint list that a user may have entered in error. The user training indicated to users that Departments should only be added from the ERP system and not by users.

1. Add a Data Flow to your package.

2. Add an OLE DB Source to the Data Flow tab and configure the source with the correct query to select the Departments for deletion from the SharePoint list.  The only field you need in the result is the ID field which maps to the ID in the SharePoint List.  If the ID is in this query it will get deleted from the SharePoint list.  In this example we will remove any duplicate records, and remove any Departments that don’t exist in the ERP system:

–DELETE DUPLICATE Department Codes.  Take the oldest record.
SELECT ID FROM SP_Department
WHERE Department_Code IN (
SELECT DP.Department_Code FROM  SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
GROUP BY DP.Department_Code HAVING COUNT (sp.id) > 1)
AND ID NOT IN
(SELECT MIN(ID) FROM SP_Department
WHERE Department_Code IN (
SELECT DP.Department_Code FROM  SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
GROUP BY DP.Department_Code HAVING COUNT (sp.id) > 1))

UNION
–DELETE Department Codes that don’t exist in ERPSELECT SP.ID FROM  SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code
WHERE      DP.Department_Code IS NULL

3. Drag the SharePoint List Destination into the data flow as your destination.

image

4.  Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above.  In addition, ensure that the BatchType is set to Deletion.

image

5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab.  Map the ID input column to the ID destination column.

image

6. Run your data flow task and check the SharePoint list for the deleted Departments.

PART 6 – BUILD DATA INTEGRITY INTO YOUR PACKAGE

Putting this all together will enforce the data integrity for this simple list.  Here is what this package looks like.

image

Here is what each step does.

1.  Truncate Tables: Truncates these tables -

DIM_Department
DIM_Department_Group
SP_Department

2. Populate DIM_Department – populates the DIM_Department table from the ERP system.

3. Retrieve Dept Group from SP – Populates the DIM_Department_Group table from another SharePoint List.  This is used as a drop down in the Department SP list for users to select the correct department.

4. Retrieve SP_Depts – Populates the SP_Department table from the SharePoint list. See Part 2 above.

5. Update SP Dept Descriptions – updates any modified Department descriptions.  See Part 4 above.

6.Add Missing Depts to SP – adds any new or missing Departments to the SharePoint list.  See Part 3 above.

7. Delete invalid Depts from SP – deletes any invalid Departments from the SharePoint list. See Part 5 above.

8.  Truncate SP_Depts – Truncate the SP_Department table in preparation for reload of corrected data.

9. Retrieve Updated SP_Depts – Populate SP_Department table from the updated SharePoint list. See Part 2 above.

10.  Update Dept Group Codes – Updates the DIM_Department table with the Department_Group_ID.

UPDATE DIM_Department
   SET Department_Group_ID = SP.Department_Group_ID
   FROM SP_Department AS SP INNER JOIN
DIM_Department as DP on DP.Department_Code = SP.Department_Code

This may seem like a lot of work, but it actually doesn’t take that much time once you get the hang of it.  Of course if you have more complex lists or require a lot of lists to manage this kind of data, you will want to look into other tools.  This is a good solution to the occasional one-off with a fairly simple data requirement, where the client doesn’t have another tool available to handle it.

Update April 24, 2012

If you are populating date fields, see this post.  http://dataqueen.unlimitedviz.com/2012/04/populating-date-fields-in-sharepoint-using-ssis/

Update May 2, 2012

SQL Server 2012:   All components that are installed on the local machine now automatically appear in the new SSIS Toolbox. When you install additional components, right-click inside the toolbox and then click Refresh Toolbox to add the components. The SharePoint List Destination and the SharePoint List Source appeared in the “Common” folder in the SSIS Toolbox when I refreshed the toolbox.

  48 Responses to “How To Use a SharePoint List as a Data Source in your SSIS Package.”

  1. [...] You can find an excellent tutorial on how to set this up here, so I’m not going to go into any details on that. I will however cover the basic steps below. First, however I want to outline the logic involved. [...]

  2. Hey Martina – thanks for your help! I needed to find out how to delete records in a sharepoint list and you (and Bing) had the answer!

  3. Thanks Martina, I was given this task to do and was struggling until I found your very informative post.
    Had the package done in a tick. Great Stuff.

  4. I use Microsoft SQL Server 2012 with SharePoint Server 2010. SQL 2012 has replaced BIDS with Server Data Tools. I’ve installed the lastest version os SharePointListAdpater I can find online but when i select add new items in toolbox the SSIS Data Flow and Control Items tabs aren’t displayed.
    Please does anyone know how to accomplish this same task using SQL server 2012 with server data tools? Thank you

    • All components that are installed on the local machine now automatically appear in the new SSIS Toolbox. When you install additional components, right-click inside the toolbox and then click Refresh Toolbox to add the components. The SharePoint List Destination and the SharePoint List Source appeared in the “Common” folder in the SSIS Toolbox when I refreshed the toolbox.

  5. I was using SSIS to import the data from my SQL Server to SP2010, I create a column in my SQL called SPID, bascailly this column will contain NULL when it’s a new record, but the problem now is this value will always be NULL as SSIS wouldn’t update the SharePoint ID back to my SQL, any idea how do I accomplish this? Thanks in advanced.

    • Hi,

      Thanks for your question. You will need to use two different data flows, one for new records and one for updates. For more details on updating SharePoint lists and using SSIS to maintain data integrity in your SharePoint lists, you might find this article helpful – Maintaining Data Integrity of a SharePoint list with SSIS. If you are using InfoPath forms, you might rather use the combination of the SharePoint list and the InfoPath form to manage the data integrity. Check out this blog post for best practices on this subject. It talks about cascading lists, but the principles apply to single lists as well – Best Practices for Cascading SharePoint lists

      Cheers,
      Martina

      • Hi Kathy,The script task is silmpe and I included it below. It sets the sheetname and also builds the column list. In the SQL Task I set the SQLSourceType to Variable and the SourceVariable then becomes User::SheetTable. I can’t add a screenshot of the whole thing here, but I start with the script task, then the SQL task, then the data flow I showed in the original post.Public Sub Main() Add your code here Dim strDate As String = FormatDateTime(Now, DateFormat.ShortDate).ToString strDate = Replace(strDate, / , _ ) Dts.Variables( SheetName ).Value = DBSize_ strDate Dts.Variables( SheetTable ).Value = CREATE TABLE `DBSize_ strDate ` (`ServerName` NVARCHAR(50),`DBName` NVARCHAR(50), `Name` NVARCHAR(50), `LastRunDate` DateTime, `PriorRunDate` DateTime, `LastTotalSizeMB` Decimal(8,2), `PriorTotalSizeMB` Decimal(8,2),`TotalSizeDiff` Decimal(8,2),`LastUsedSpaceMB` Decimal(8,2), `PriorUsedSpaceMB` Decimal(8,2), `UsedSpaceDiff` Decimal(8,2), `LastFreeSpaceMB` Decimal(8,2), `PriorFreeSpaceMB` Decimal(8,2), `FreeSpaceDiff` Decimal(8,2)) Dts.Variables( SheetTable ).Value = Replace(Dts.Variables( SheetTable ).Value, Default , Dts.Variables( SheetName ).Value) Dts.TaskResult = ScriptResults.SuccessEnd Sub

  6. Excellent work Martina, keep it up!
    Made my life so much easier

  7. Hi Martina…I am porting a package from SQL2005 to SQL2012 and have had good results except for one area…I am runing a

    DataFlow(SP List Source—>RecordSet Destination)—>
    (Foreach Loop Container—–Execute SQL Task—>DataFlow(SP List Source—>Script—>SP List Destination))

    and trying to update a SP List…the item ID is in @User::ListItemID…

    in SQL2005 I use a CAML query of:

    0

    and it worked fine…in 2012 I am not getting the Item ID passed into the SharePoint List Source and therefore not updating any Items…any siggestions?

    • lol…never mind …I got it…need to use an Expression:

      “” + (DT_WSTR, 5) @[User::ListItemID] + “”

  8. lost the CAML Query in the previous post

    “0″

  9. [...] This is an addendum to my previous blog post How to use a SharePoint list as a data source in your SSIS package. [...]

  10. Is there a way to read files from a Share Point site without using the codeplex components?
    Can this all be done in a script task? ssis 2008

    Thanks

    • Hi Stever,

      Assuming I am reading your question correctly you are asking about reading files, not SharePoint list items. Your approach may depend on what type of files you are reading. This is how I did it to copy MS Office Files from SharePoint to the File System. I believe the DavWWWRoot is specific to MS Office files.

      Create a connection manager to the SharePoint folder like this
      \sharepointserverDavWWWRootmysitemylibrarymyfile.xls for a file, or this
      \sharepointserverDavWWWRootmysitemylibrary for a folder
      Then use a File System Task as usual in your package to perform whatever action you choose (Copy Directory, Copy File, etc) to whatever location you choose.

      On the server running SSIS, you may need to open Server Manager, Add Feature – Desktop Experience. This will allow WebDav to work. If you’re still having trouble you may have to install the WebDav components – http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

      I hope this helps you as a starting point.

      Cheers,
      Martina

      • Hi Martina – I have a share point Connection Manager (SharePoint Source from Pragmatic Works Task Factory) I can read the list data items – but how do I import the data in the associated infopath files for each of those records?

        Thanks
        Stever

  11. How to update a lookup type field ?

    • Hi Luis,

      You can’t update the displayed field which has been looked up. You may be able to update the field that the lookup is attached to. For example if you are displaying UserName in a UserGroups list, then the SharePointListID for that User is what is being stored in the UserGroups list lookup field.

        IF

      it is possible to update that field, you would need to send the data in the exact format that SharePoint is storing it. It likely looks something like 24;#Sarah Robinson. You could try pulling the lookup field into SQL and see how it is being stored, then try to update it.

      Please also take a look at this Best Practices post. I avoid LookUp fields as they can cause difficulties, and this talks about other ways to handle it. http://dataqueen.unlimitedviz.com/2012/03/best-practices-for-cascading-sharepoint-lists-using-infopath-forms/

      Cheers,
      Martina

  12. Martina,
    Thank you very much for the step by step post. Everything works for me when running in development environment. However, when scheduling the package using SQL Server Agent. It failed on the sharepoint connection manager. Do you know how to fix it? kind of needing it urgently Do I need to install something on the DB server? Thanks a lot in advance!
    The following is the Error Message:
    DTS:Name=”DelayValidation”>0 CM_SharePoint {C49B478E-04A7-4C9B-A978-7F7AB5″ from node “DTS:ConnectionManager”. End Error Could not load package “package.dtsx” because of error 0xC0010014. Description: The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails. Source: Started: 6:42:06 PM Finished: 6:42:07 PM Elapsed: 0.249 seconds. The package could not be loaded. The step failed.

    • Hi samsar,

      You’ll want to set up permissions for your SQL Agent Service Account on the SharePoint site you are accessing in your package. It’s not ideal, but it’s either that or set up a Proxy on SQL Server Agent which has permissions to the site, and run the job as the Proxy. I find it easier to maintain just setting up the permissions for the service account. Make sure you set read/write permissions if the package is writing back to your SharePoint lists.

      Cheers,
      Martina

    • Be sure you have installed the SharePoint list adapters on your SSIS server as well, if it is a different machine from where you have being working in Visual Studio.
      Cheers,
      Martina

  13. I am still trying to get the Connection SharePoint List Source to work in Visual Studio. I think it is a permission problem. I setup the Connection Manager with Custom Credentials that have Full Control to the site and the list. In the Advance Editor for SharePoint List Source I select the Connection Manager. Then under Componet Properties I enter the site URL to the site where the list lives example https://xxxxxx.xxxx.xxx/Site/SubSite/ Then I enter the List Name in the SiteListName, refresh and I get “Error at Data Flow Task [SharePoint List Source [1]}: System.ServiceModel.Security.MessageSecutiryException: …..” The custome Credentials should have Full access to the site and List. Any suggestions would be helpful.

    • Hi Guy,
      That does look like a permissions error. Can you successfully sign into SharePoint as the custom user, navigate to the list and add an item to the list?
      Cheers,
      Martina

  14. I am using this to pull data from a SharePoint 2010 List using SQL Server 2012 Data Tools. My SharePoint List Source is returning the List Definition (I verified using Fiddler to inspect the HTTP traffic) but no records – I have verified there are 2 records in the List that I should expect to see returned. I also do not get any results in the “Execution Results” Tab. I took the step of specifying a View but that did not help.

    Any thoughts?

    Thanks,
    - Ian

    • Hi Ian, sorry not sure where the disconnect lies. I have never had an issue returning records. The tools version shouldn’t be a problem. You’re certain the view is not filtering out records, even by user?
      Cheers,
      Martina

  15. Hi Martina,

    I am trying to migrate data from SQL(2008) table to SharePoint(2010) List.But I was little bit confused with the SharePoint Credentials.Could you please help me with this.And also My SQL table is having 6 Columns But My List Should have 10 Columns.So I mapped these 6 Columns by making the other columns in the list Ignored.But Can’t figure out the error.Could you Please Help me Out.

    • Hi Lalitha,

      For Insert into a SharePoint list, be sure that Batch Type is set to Modification, and that you DO NOT map the ID column. SharePoint will create an ID for each record inserted. If you are still having an error with the empty columns in SharePoint, then perhaps they List setting require them to have data. For example the Title column normally requires data and it must be filled in.

      If you were able to get that far, I’m not sure what issue you are having the the credentials. If you are developing with a login which also allows you to seamlessly log in to SharePoint then you shouldn’t require credentials for SharePoint.

      Cheers,
      Martina

  16. Hi Martina,
    I love reading your detailed steps for SharePoint config. Would you mind me asking a frustrated question?
    I was trying to upgrade a SSIS package from 2008 to 2012. Everything works except the SharePoint list Source, which showed no color. BTW, I have installed SharePoint list adaptor in the SSDT2012, and I can configue another SharePoint list source from scratch. However, I can neither EDIT the bad existing SharePoint list Source nor DELETE it. Have you met this kind of problem before? I am lookig forward to your reply.

    Thanks a lot,

    Larry

    • Hi Larry,

      I ran into problem upgrading SharePoint list source/destinations in SSIS as well. I ended up recreating them from scratch. Sorry. :(

      Martina

  17. Thanks for taking the time to write all this up with the screenshots, it’s much better than my post on SharePoint List adapter, you’re making me want to re-write mine.
    Cheers,
    -TD

  18. Hi Martina,

    Informative post you have. Very detailed.

    I have a question though when I try deleting from a sharepoint list and reinserting again the ID field on the SharePoint List doesn’t seem to reset to 1. How do I do this?

    Thanks

    • The ID is controlled by SharePoint and can’t be reset to 1.

    • There is something wrong in your ROW_NUMBER. You need to group by something in order to have the row_number increase, in this example it is grouped by LAYOUT_CODE as indicated in the PARTITION BY portion of the statement.

      SELECT
      ROW_NUMBER() OVER(PARTITION BY Layout_Code ORDER BY Product_ID) AS Row
      ,[Layout_Code]
      ,[Product_ID]
      ,[Variant_Code]
      FROM[DIM_Product]

      Cheers,
      Martina

  19. Hi Martina ,
    The Post is excellent . In my case I have a bulk data in SQL 2012 which is actually having Document of various format also . Now I need to move this to SharePoint 2013 Document library .
    Could I use SSIS package as given . Any steps I need to change .What data type of document should I take at SQL DB end .

    Actually the big picture of this requirement is I need to move documents from sales force to a SQL db VIA intermediate tool and then from SQL db to SharePoint 2013 .
    Your suggestion will be really helpful for part of moving documents in SQL table to Sharepoint 2013 .

    Thanks

    • Hi Karry, This answer is probably far too late, but I’ll answer it now in case it helps. Sounds like the intermediate tool may get in the way. If you can bring the file down to the file system from Salesforce, it can be dragged and dropped into SharePoint through OneDrive for Business (essentially a synchronized doc library). In fact, if the SalesForce docs could be synced to the file system, the destination folder could be the library itself, removing a lot of moving parts. I hope that helps.
      Martina

  20. Hi!

    I’m a newbie in using SharePoint Lists Source and Destination in SSIS, so I hope you can help me with this error:

    Error at Data Flow Task [SharePoint List Source [1]]:
    Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unhandled SharePoint Exception —> System.ServiceModel.FaultException: Exception of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.

    I’ve read the same error somewhere, and it says it is a permissions error. I’ve checked that I have ‘Full control, Limited Access’ permission in the target SharePoint List.

    I’m wondering if that error really is a permission error and do I have to have a ‘Full Control’ permission, instead of ‘Full Control, Limited Access.’ The error message was really vague. Hope you can help ASAP.

    Thanks :)

  21. I am using SQL 2012, with the 2012 adpator (installed on VM that i am building the package on where the server also resides). I’ve built my package in Visual Studio 2010, when i execute the package in studio, everything works fine. When I try and schedule the dtsx package, i get the following error below:

    “This occurs when CPackage::LoadFromXML fails”.

    Any thoughts around this? I’ve tried multiple things:
    create a proxy to make sure the sql agent can pull the data out of sharepoint (has permissions)
    force it to run in 32 bit (someone in browsing google suggested this)
    formatting of dates; i excluded dates to test

    Ultimately, the above error is what I keep getting. Thoughts? It’s driving me nuts, since I have a lot of lists that I need to automate and I am hoping i don’t have to kick the jobs off manually everytime.

    I am using sharepoint 2003.

    • Hi Steven,
      I don’t know this error in particular but searched a bit. Sounds like it might be a version difference between your development environment and your deployment environment. Is that possible? Can you log in to the SQL Server itself and successfully run the package in Visual Studio from there? You might take a look at this thread for any clues – http://www.sqlservercentral.com/Forums/Topic737169-148-1.aspx. Sorry I have not seen this error in particular. I always develop the package on the same server where it will be executed, in order to catch any issues up front.
      Cheers,
      Martina

      • I found the issue – SSIS was not installed on the VM I was using, which has since resolved my problems. Great article, life saver!

  22. Great post! Thanks for the detailed description, appreciate it.

  23. I have 10 million records, and want to load that from Oracle to Sharepoint 2013 list.
    Will this component work for this scenario or there is some limitation on number of records?

    • You don’t want to load more than 5,000 records into a SharePoint list. It won’t perform. Why do you want to expose that many records in that way? Perhaps there is a better solution.

Leave a Reply

Wordpress SEO Plugin by SEOPressor
%d bloggers like this: