MSBI

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

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.

Hi Everyone, Would like to share the knowledge how to achieve the ROW_NUMBER () Functionality through SSIS. For this, we shall consider an example. The business logic that needed to be followed was that I had to assign a “Twin Code” to each record. This meant that for each family in the database, if two or more members were born on the same day they should be treated as twins. The twins should be assigned a Code enumerating them in order of birth. This can be achieved through SQL by just writing a simple ROW_NUMBER () function. To achieve this same in SSIS, We shall in need of Data Flow task. Connect an OLEDB Source to the Family table. Now, use a Sort transformation which is likely to be used as ORDER BY Statement in our ROW_NUMBER () Function. We are going to sort by FamilyID and DateOfBirth Column. Now pull out a Script Component. Because we need to “Partition By” Family ID and DateOfBirth, We shall include those as an Input in...

I am trying to scrap a website and extracting the data from it. For Demo purpose, I am using  http://www.w3schools.com/sql/sql_select.asp  website as it is one for simple and popular website for learning basic of programming languages. I am now concentrating on the values which are on left pane on this site. Let’s go to extract these keywords. For the web parsing, we should install beautiful soup. If we go to “Inspect element”, the HTML tag for the website looks like this: We now came to know that, the names or text, we need to extract is lying under “<a>” tag. To get all the data from “<a>” tag, use the following code. from bs4 import BeautifulSoup import urllib2 html="http://www.w3schools.com/sql/sql_select.asp" WebParse = urllib2.urlopen (html).read() soup = BeautifulSoup (WebParse) for ul in soup.findAll ('a'):     print ul And if we investigate, we can see there are lot of r...