MSBI

I had a SSIS package where I was calling a stored procedure in OLEDB Source and it was returning a “The Value Was Too Large to Fit in the Output Column” error. Well, My Datatype in OLEDB source was matching with my OLEDB Destination table. However, when I googled, we got solutions like to increase the output of OLEDB Source using Advanced Editor option . I was not at all comfortable with their solution as my source, destination and my intermediate transformation all are having same length and data type and I don’t want to change. Then I found that I was missing SET NOCOUNT ON option was missing in Stored Procedure. Once I added it, my data flow task ran successfully. 

Hi All, Recently got into a situation of grouping set of data with a comma delimited. Here is the requirement. I have a table like this below,     And the way data should be shown as So let us create a sample data set to achieve this: create table #t1 ( ID INT , Name Varchar ( 10 )) Insert into #t1 Select 1 , 'a' Union Select 1 , 'b' Union Select 1 , 'c' Union Select 2 , 'e' Union Select 2 , 'f' Union Select 2 , 'a' Union Select 2 , 'H' Union Select 3 , 'X' And query for the output SELECT   ID        , STUFF (( SELECT ', ' + CAST ( Name  AS VARCHAR ( 10 ))          FROM #t1          WHERE ID = t . ID          FOR XML PATH ( '' ), TYPE ). value ( '.' , 'NVARCHAR(MAX)' ), 1 , 2 , ' ' ...

Recently I had copied a excel file for analysis from SFTP site to development server from my account and asked my team mate to analyze it, by loading it into table. He logged in to the server and used a simple data flow task in SSIS with source as Excel to load the data. When he was connecting excel using excel connection manager, he had a weird error. Unexpected error from external database driver ()  He was unsure of why he was getting the error when selecting the sheet names. Then when reported, we quickly got into the possible reason of security. As I copied the file from SFTP to Development machine using my account, it had limited the access to other users. When he was trying to load, he was facing the error because it was a read-only file for him. Soon I granted full permission to him on excel security and it worked for him. There are various different solution on internet but initially none worked for us when we were trying to resolve. Hope this so...

We can able to maintain old historical and current data (SCD) using the date column. Let me show you people how to write a small query to achieve it. For example, Assume we have a column called currency which has currency values of all the countries and your business needs it to keep it uniform, say convert all currency of different countries to USD, so it will be easy for Business judgement and analysis. For this, let me take an example of Yahoo! Finance . Yahoo! Finance API provides us currency conversion of all country based on time. I am not here going to tell how to pull the data from Yahoo! Finance API, But I am using such data which I pulled to explain how SCD can be achieved. Below is a image, which depicts, the data which we got from Yahoo! Finance. We wanted this to get converted as shown below: Where we wanted to derive Effective_To column, which indicates, from one date to other date, the currency was so & so for a country against USD. ...

Hi All, Many situation when we are pulling the data from website or calling the data through an API, we end up with JSON data. If we needed to load JSON Data into Database, we need to break the JSON data into columns. We will see how to break the JSON data using C#. Consider below JSON Data. {     "requestId":"299f#150942d9c47",    "result":[        {           "id":10357,          "leadId":3032,          "activityDate":"2015-06-16T20:20:36Z",          "activityTypeId":1,          "primaryAttributeValueId":26,          "primaryAttributeValue":"eos-celebration",          "attributes":[   ...

 In continuation with my previous blog,  here , we will discuss further more. Table Columns and Rows Usually the table is represented by “Fields” and “Records” which is wrong terminology. Just to improve our standard on SQL, we should refer as “Columns” and “Rows”. “Fields” are interfaces what we have on client applications and “Records” are what we have in files and cursors. Tables are logical and they are represented by Columns and Rows. There is nothing called “NULL Value” as mentioned above, null represents missing value. So we should rather call just NULL instead of saying it “NULL Value”. SQL Engine This is an interesting part of SQL. How the query is processed? Let us answer that in some time. SQL is English like language which uses common terms we use in English but converts into a code later stage. SEQUEL is original format (Structured English Query Language) was coined first but due to trademark dispute with Airline company, it is renamed as ...

Hello Everybody!!! This time, it is about Microsoft Exam. When I decided to take up Microsoft certification (MCSA) on Business Intelligence 2012 (I was planning from 3 years but was never that serious to write, Lol!! But now I am ready), I came up with training kit by Itzik-Ben-Gan. This book was very helpful for understanding the fundamentals of T-SQL and its architectures. However, for my preparation standpoint view, I am summarizing the tips and points from the book which I believe, going through main points, will help lot of them to get small tips instead of reading entire book before day of exam. So let’s start with SQL….!!! Standard SQL is based on the relational model , which is a mathematical model for data management and manipulations. The word relational is not about 2 tables related but it is from mathematical concept of Set Relation from “Set theory”. Relation in the relational model is called a “Table”. (T-SQL is based on multiset theory than on set th...

Hi All, Here is function to make first letter capital in a word, which is also known as "Pascal Naming Convention" CREATE FUNCTION [dbo] . [InitCap] ( @InputString VARCHAR ( 4000 ) ) RETURNS VARCHAR ( 4000 ) AS BEGIN DECLARE @Index INT DECLARE @Char CHAR ( 1 ) DECLARE @PrevChar CHAR ( 1 ) DECLARE @OutputString VARCHAR ( 255 ) SET @OutputString = LOWER ( @InputString ) SET @Index = 1 WHILE @Index <= LEN ( @InputString ) BEGIN SET @Char = SUBSTRING ( @InputString , @Index , 1 ) SET @PrevChar = CASE WHEN @Index = 1 THEN ' ' ELSE SUBSTRING ( @InputString , @Index - 1 , 1 ) END IF @PrevChar IN ( ' ' , ';' , ':' , '!' , '?' , ',' , '.' , '_' , '-' , '/' , '&' , '''' , '(' ) BEGIN IF @PrevChar != ...

Hi All, We when planned to delete some unused tables and also to know how much some of tables consuming the space, we needed to have a quick on table size. Then team approached me to write a quick query to know the table name and its size. The query to calculate size of table in given database is as follows: SELECT t.NAME AS TableName ,s.NAME AS SchemaName ,p.rows AS RowCounts ,SUM(a.total_pages) * 8 AS TotalSpaceKB ,SUM(a.used_pages) * 8 AS UsedSpaceKB ,(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.NAME ,s.NAME ,p.Rows ORDER BY t.NAME

We can create a Function which can returns multiple values. As the definition says, the Scalar valued function returns only single value. But on using a Data type TABLE, we can get multiple values. Example is shown below: I have a Table called family. Now let me create a function to call all the records where I am going pass FamilyID as a parameter. Create FUNCTION dbo . Fn_Family ( @ID AS VARCHAR ( 50 )) RETURNS TABLE AS RETURN SELECT FirstName , LastName FROM dbo . Family WHERE FamilyID = @ID ; Go The call this function, like: Select * from dbo . Fn_Family ( 1 ) Where (1) is the parameter.