MSBI

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