dinsdag 12 juli 2011

SSIS VS_NEEDNEWMETADATA strange message

At one of my current customers I created a simple package to export views to Excel documents.
Because I'm a bit lasy I copied the first package I did create to generate a new one for the next view.

The package contains the following DFT:

When I do run the package the following error is generated:

It's not really clear what the error is so let's take a look at the sysssislog:
The external columns for component "OLE_SRC OLE_DB_ZH308 ZH308GBSturing ZH308 varSQLSelectSource_str" (85) are out of synchronization with the data source columns. The column "ZH308_DBC_ID_Trigger" needs to be added to the external columns.  The column "Eerste_Trigger_Datum" needs to be added to the external columns.  The column "Laatste_Trigger_Datum" needs to be added to the external columns.  The column "ZH308_Aangemaakt" needs to be added to the external columns.  The column "Historische_Journaalposten_Aanwezig" needs to be added to the external columns.  The column "DBCTraject_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCFactuur_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCPatient_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "DBCUZOVI_Gegegevens_Aanwezig" needs to be added to the external columns.  The column "UZOVI_Digitaal_Factureren_Mogelijk" needs to be added to the external columns.  The column "DBCSpecialisme_Gegegevens_Aanwezig" needs to be added to the external columns.  The external column "Bedrag" (333) needs to be removed from the external columns.  The external column "CodeVerzekeraar" (330) needs to be removed from the external columns.  The external column "Factuurnummer" (327) needs to be removed from the external columns.  The external column "Titel" (324) needs to be removed from the external columns. 

Seems to be clear, or not? When you looked at my DFT task you did maby see that I don't have the OLE_SRC OLE_DB_ZH308 ZH308GBSturing ZH308 varSQLSelectSource_str source in there.
All MetaData of the OLE_SRC, is what I would expect.
So do open the advanced editor for the OLE_SRC OLE_DB_ZH308 :

Thats strange, all messages saying the column needs to be added or column needs to be removed, why I do not see it here. Do you?

It seems to be there somewhere, so to be sure I did take a look at the XML code of the package and did found:

What is that doing there? Why is it none gone! From the previous error from sysssislog whe can see the error is generated from the component id 85

As you can see in the lower part of the image above, my current OLE_SRC is has component id 201. The shown component id 85 is not even visable in the DFT any more.

How to solve this? I tried to remove the component id 85 from the XML code but this will end-up with a corrupted package. Need to find out what to do next time. So my final but not real great solution was creating the package from scratch. I needed to do this because I did rename my OLE_SRC task already. Recreating a OLE_SRC task with the same name and removing it again does not solve the issue.

The best way is just prevent this issue! When you have duplicated your template package the first you do is remove the OLE_SRC task and add a new one.
You can replay this issue in the following way:
1 - Create a DFT
2 - Add a SQL OLE_DB source with setting the SQL Data access command to : SQL Command and get your query in.
3 - Now copy your package to generate a new one
4 - Change the SQL Data Access command for the SQL OLE_DB source to : SQL Command from variable
5 -  Save your package and look at the XML code, when fine you still see the SQL Command setting as component in your package...

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you. Dynamic Intelligence | LinkedIn | Blog | Twitter

zondag 10 juli 2011

SharePoint 2010 using BCS with SQL Server database

SharePoint BCS (Business Connectivity Services) can be used to display information from you business applications in a SharePoint environment.

In this blog I try to explain how to use BCS to get data from a SQL server database.

image

Above image is a simple view of the environment I’m using.

  • SHP01 = SharePoint 2010 Web & Application server using NTLM security
  • SQL01 = SQL 2008R2 database server used to store the SharePoint databases
  • SQL02 = SQL 2008R2 database server used to create the BCS test data
  • PC01 = Regular Windows 7 client environment to be used

I created a site collection on SHP01 http://demosites.di.local/sites/BCSDemo using the blank site template

Domain user DI\Farm1Admin1 is the site collection administrator.

image

I created a domain user DI\adBCS_Reader that is used to create a BCS list based on Windows Authenticed secure store.

On SQL02 a demo database dbDemoBCS was created using the script you can download [here]. It contains a table tblDemoBCS that now has 1999 rows, why 1999? I will get to that later in this blog.

The user DI\adBCS_Reader is added to the database security with db_datareader rights. A SQL Login sqlBCS_Reader is created and also added to the database with db_datareader rights.

I think we are ready so lets get started.

BCS with SQL and Windows Authentication

First thing we need to do is create the secure store target application, we need central administration for this.

CA > Application Management > Manage Service Applications > Secure Store Service Application > Click on New (Click image to view configured values)

image

image

image

The secure store application is now created and we need to set the credentials

image

image

We are now ready to create a BCS Application using the SQL02 database.

We need SharePoint designer to do this, I use the PC01 client with the DI\farm1admin Login.

First open the site in SharePoint designer… and then follow the images..

image

image

Change Name of the BCS Application and start creating the data source
image

image

Now we need to give the SQL connection information, the Name setting is optional but to make sure that we can set the correct security later when we add the SQL based BCS I will give it a name. This I recommend to do when you create several BCS applications to the same database using different credentials, also use different connections.
image

After clicking OK we need to verify the security and need to login with the credential that is registered as credential for the Secure Store Application ID.
image

Now we can run into the first error you can get when using BCS:
image

I use the account DI\Farm1Admin1 as the site collection administrator an I logged in with this account on PC01 for using SharePoint designer. This means that when adding a BCS application (External Content Type) what we are doing you need to have privileges in the BCS service to do this.

CA > Application Management > Manage Service Applications > Manage the BCS Service Application

image

image
No add the Farm1Admin1 account with all possible priveleges:
image

Return to SharePoint Designer again. If you click ok on the error box you need to add the connection again. (see previous steps)

Now the connection is added, expand the database to view the tables, right click the table and select create all operations…
image

image

image

image

After clicking finish don’t forget to click Save button!

image
image

No we can go and add a list to the SharePoint BCSDemo site.

image

image

image

image

image

The list is created and data is displayed..
image

Now let’s see what happens when we switch user on the site, I use DI\Arjan who is member of the BCSDemo owners for the site.

image
This one we no already, lets get to CA > Application Management > Manage Service Applications > Manage the BCS Service Application. This time click the just created BCS application and select Set Permissions

image

I add the DI\Domain Users of course you could select a special group or only users that need to use this application. This time only execute will be enough.
image

A different error is displayed.

image

Why is this error displayed? It’s not really clear from the message. But I do have a solution that solved this so lets implement.

Remember that we created the secure store application? At some point we had to define some members that where allowed to impersonate as the given security privileges. We need to add the users that are going to use this application to this same members setting.

CA > Application Management > Secure Store Service Application > Manage

image
You can get to the members setting only by editing the secure store application. Just continue all steps until you get to where you can add members and add your desired privileges. I added domain users to keep it simple.

image

Return back to the site with the user with less privileges as the site collection administrator (in my case DI\Arjan), is the list displayed???

image

Yes it is!

BCS with SQL and SQL Authentication

In this case I want to use the SQL Authentication, most of the steps are the same as above and will skip some screens to not make this a massive blog Smile.

First create the secure store application, to not error I setting all corrections done above immediately.

image
No differences yet only in the name..

image
Here is a part of the difference between windows authentication.

image
This time I added domain users immediately, I now my errors already Smile.

image

image
This time we entered the SQL Login as credential.

We can switch to SharePoint Designer and add a new BCS application.

image

image

The previous connection is still there, we need to add a new one to make it work with the new secure store application.

image

image
This time we verify the SQL Login to have access to the database.
image

Now we create all operations again, see previous steps to display how. But end up with a screen like below and don’t forget to click the save button!

image

Go to CA to set permissions for the created BCS Application.

image

Remember this from previous given error solution..

image

Now return to the demo site and add a list based on the SQL BCS Application, I hope you remember how other wise scroll back Smile.

image

Is the list showing?
image

Does it also show when using a other login on the site?
image

One more time displaying the error:

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Correlation ID:b6729321-564e-4a39-a47a-c621db88894e

Why? It has to do with the number of records in the table, soon I will update for now the sun is shining.

Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.

Dynamic Intelligence | LinkedIn | Blog | Twitter