SSIS - CODEPOINT Function

CODEPOINT :

                         Returns the Unicode code point of the leftmost character of a character expression.


Syntax :

CODEPOINT(character_expression)
 
  • character_expression must have the DT_WSTR data type.
  • CODEPOINT returns a null result if character_expression is null or an empty string.


EXAMPLE : 

Sample data:

CREATE TABLE TEST123(ID INT IDENTITY(1,1),Column1 VARCHAR(40))
INSERT TEST123 (Column1) SELECT 'A12345678'
INSERT TEST123 (Column1) SELECT 'B12345678'
INSERT TEST123 (Column1) SELECT '123456789'

Scenario:

In the above sample data,if we are required to export only the rows with starting character as alphabet in the Column -  Column1.

If we are required to achieve the above scenario using SSIS ,  CODEPOINT function can be used .

 Use this expression in the Conditional Split Task as shown in below images :

     CODEPOINT(UPPER(Column1)) >= 65 && 
   CODEPOINT(UPPER(Column1)) <= 90


SSRS - IsMissing - Visibility Function

SCENARIO :

In SSRS,based on certain condtions mentioned in the dataset  (query/stored procedure),if we are required to display only particular columns which holds data and to eliminate/hide columns,which are not required to be displayed in the report.

If we want to handle the above scenario in SSRS,IsMissing function can be used.

Example 1:

Sample data :

CREATE TABLE Famous_Personalities
(Profession VARCHAR(30),
Person_Name VARCHAR(30),
Game VARCHAR(30),
Talented_In VARCHAR(30))

INSERT Famous_Personalities SELECT 'sports','sachin','Cricket',NULL
INSERT Famous_Personalities SELECT 'sports','messi','Football',NULL
INSERT Famous_Personalities SELECT 'entertainers','AR Rehman',NULL,'Music composer'
INSERT Famous_Personalities SELECT 'entertainers','shakira',NULL,'Dancing&Singing'


--stored procedure - report dataset


CREATE PROCEDURE SSRS_Columns_Ismissing
@Profession VARCHAR(30)
AS

BEGIN

IF
@Profession = 'sports'

SELECT Person_Name,Game,Profession FROM Famous_Personalities
WHERE Game IS NOT NULL

 ELSE

SELECT
Person_Name,Talented_In,Profession FROM Famous_Personalities
WHERE Talented_In IS NOT NULL

END


Example :


Step 1:  create report with above sample data.




Step 2: Dataset will have columns that is mentioned inside the IF block in the stored procedure,
we have to manually add the column - Talented_In as shown below






     

SSRS - IsNothing - Decision Function

SCENARIO:

If a column has NULL value  and if we want to display the entire row only if the column has NOT NULL value and to eliminate\hide  the entire row if the column has NULL value .

 If we want to handle above scenario in SSRS ,IsNothing function can be used.

--sample data

CREATE TABLE TEST_SSRS(ID INT,Name VARCHAR(20))

INSERT TEST_SSRS SELECT 1,'Sathya'
INSERT TEST_SSRS SELECT 2,'Sunny'
INSERT TEST_SSRS (Name) SELECT 'Hansi'


--you can also try giving this condition directly "WHERE ID IS NOT NULL" in your  dataset to hide entire row if the column has NULL value.

SELECT * FROM TEST_SSRS WHERE ID IS NOT NULL


EXAMPLE : SSRS report not displaying  row if the column has NULL value