Creating BCS connection is little tricky part and if you miss some steps then it takes more time to do it. It’s just configuration work but still people are not able to do it because there are some small steps needs to be done like setting permissions configuring secure state service etc. and if those are missing while doing it then it’s becoming critical for them. Here I have given detail steps to configure the BCS connection and creating external list to bring external data in share point and I explained here each step required while doing this process.
May be you guys will help it for doing the BCS connection. Still you find any difficulty feel free to contact me on my mail.
Before start creating BCS connection from SP designer we must have to create secure target application from central admin
Open CA and then
Central Admin> Application Management > Manage Service Applications > Secure Store Service Application > Click on New button from ribbon.
Click on Next then below screen occurs add user id and password details
Click next and add administrator for this secure state application
Next step is to set the credentials for this application. Click on arrow icon of application id and it shows the menu. Select set credentials.
After clicking the set credentials its shows below screen for adding the credentials details. Add credentials and hit OK.
Now the configuration part one is done. We will have to do one more configuration that we will see in next steps. Its now time to start cresting the ECT(External Content Type) so start SP designer and open your site and click on external content type.
Add the connection name display name, list type and click on external data source link to make the connection. Its shows below screen now click on Add Connection and it gives you the three types of data source as we are connection with SQL server so select SQL type from it
After selecting the data source now its time to provide the connection details so add server name, database name. And select middle option connects with impersonation identity option.
After adding server details insert the credentials details in below window and click OK.
Now connection is added and it will display the tables from the database. Expand the required table and select the type of the operation you want to do with it.
After doing all the table settings. Click on save to save the connection.
Now its time to create the external list from external list template. So go to create list option select the ECT template and create the list.
After creating the list when you come to the list all item view you will get below error
Once the credentials are added just refresh the screen and you will get your data from related table.