MSBI

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

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.