In my last tip, "Importing Data From Excel Using SSIS - Part 1" we found out that the SSIS Excel Connection Manager determines the data type of each column of the worksheet on the basis of data for that particular column from the first 8 rows. This is the default behavior and connection manager uses a value in the registry key to consider the number of rows for data type determination. We also saw that the data import fails if the data in others rows (other than first 8 rows) is not compatible or has a longer length than the data in the first 8 rows. I talked about different ways to handle this to make the data import succeed. I also talked about the easiest solution which is to change the registry key value, but this has its own implications and hence the question; what other options are there without creating too much additional overhead? for information click here.
Sunday, October 21, 2012
Recently while working on a project to import data from an Excel worksheet using SSIS, I realized that sometimes the SSIS Package failed even though when there were no changes in the structure/schema of the Excel worksheet. I investigated it and I noticed that the SSIS Package succeeded for some set of files, but for others it failed. I found that the structure/schema of the worksheet from both these sets of Excel files were the same, the data was the only difference. How come just changing the data can make an SSIS Package fail? What actually causes this failure? What can we do to fix it? Check out this tip to learn more.
SQL Server 2008 R2 introduced Master Data Services (MDS) as Master Data Management (MDM) platform for managing enterprise master data centrally in a consistent, clean and up-to-date manner. There were three ways to manage master data in MDS. First, was the Master Data Manager which is a web based User Interface. Second, was loading data through the staging process. Third, was to use the MDS WCF services to programmatically manage master data. These were fine options, but there were many users who wanted to manage data in Microsoft Excel in bulk than managing each single record/member at a time in Master Data Manager UI. The SQL Server team heard this requirement and introduced a brand new Excel add-in for SQL Server 2012 for MDS to manage master data. In my last tip I talked about how to get started with MS Excel MDS add-in to manage master data stored in Master Data Services database in Excel. In this tip, I am going to talk more about editing, creating, combining master data, creating entities and how it can be used in conjunction with Data Quality Services for data matching. For more information click here.
SQL Server 2008 R2 introduced Master Data Services (MDS) as Master Data Management (MDM) platform for managing enterprise master data centrally in a consistent, clean and up-to-date manner. There were three ways to manage master data in MDS. First is the Master Data Manager which is a web based user interface. Second is loading data through a staging process. Third is using the MDS WCF services to programmatically manage the master data. These are fine, but there are many users who want to manage data in MS-Excel in bulk rather than managing each record one at a time in the Master Data Manager UI. The SQL Server team heard this requirement and introduced a brand new Excel add-in for MDS server in SQL Server 2012 to manage master data. With this new option, now the questions are, how to get started with this new add-in, how to manage data in bulk with this add-in and how publish it to MDS server? Check out this tip to learn more.
When we are done creating a SSAS cube we often think of whether to use SSRS (SQL Server Reporting Services) or PPS (PerformancePoint Services) for the reporting UI. The question is what are the pros and cons of each of these approaches and in what scenario should one be chosen over the other or can they be used together to compliment each other?
For more information click here.
For more information click here.