MSBI

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...

Getting error "Failed to Lock variable". Variable not found" when using Foreach container and file system task Solution: If you had used expression in file system task and if you have used Source/Destination has a variable, we end up with that error. Remove the expression and it works!

Hi All, Here I wanted to show you how we can implement Yahoo! Finance Currency Convertor API using C# . Note: This is based on Standard: ISO_4217. I am using a Console application to get the results. Logic: Yahoo! Finance API convertor will calculate, what is value of 1 unit in current currency system against other currency system we wanted to convert. Use the result and multiply with Amnt you need to convert. E.g., (INR: Indian Rupees; USD: US Dollar) 1 INR == 0.016 USD, then how much is 300 INR equals to? Simple math, we do using a cross multiplication, We get,             X= 0.016*300 This is the same logic; we are going to apply here. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Web; using System.Net; using System.Diagnostics; using System.Te...

[OLE DB Source] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "\foo\c$\bar" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. If you face this error, then, go to project properties and select Debugging option from Configuration Properties and set Run64Bit mode to “False”.  

If you get “ SSIS: OleDB Data source: cannot retrieve the column code page info from the OLE DB provider “ error, then follow these steps to resolve. Go to the SSIS project properties page Ø   Then Click Debugging under configuration properties Ø   Select “Run64BitRuntime” property to false Ø   Save the project Ø   RUN Build againThen check the package runs to a successful completion.

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