Azure Feature Pack for SSIS 2017 installation

Azure feature pack for SSIS 2017.
Below video demonstrates How to install Azure feature pack for SSIS 2017.

How to use Count(*) twice in one sql statement

Using SQL Count() function twice in a Select statement, we can achieve below ways

Consider in general ways we need to find the product names and corresponding Male or Female subscriber to that product. 

We can use better example also, I have used here the functionality of usage.

select 
dm.EnglishProductName,
count(dm.EnglishProductName) over (Partition by dm.EnglishProductName) CntProduct ,
dc.Gender,count(dc.Gender) over (Partition by dc.Gender,dm.EnglishProductName) CntProduct
from [dbo].[FactInternetSales] FI
join DimProduct DM on DM.ProductKey=Fi.ProductKey
join DimCustomer DC on dc.CustomerKey=fi.CustomerKey
where dm.EnglishProductName in('Long-Sleeve Logo Jersey, S','All-Purpose Bike Stand')

output :

Above scipt will display count of both Product as well Gender

If it required to display count of distinct Gender for the product details, we do below way

SELECT dm.EnglishProductName 

, SUM(CASE WHEN dc.Gender='M' THEN 1 ELSE 0 END) AS 'Male'

, SUM(CASE WHEN dc.Gender='F' THEN 1 ELSE 0 END) AS 'Female'

from [dbo].[FactInternetSales] FI

join DimProduct DM on DM.ProductKey=Fi.ProductKey

join DimCustomer DC on dc.CustomerKey=fi.CustomerKey

where dm.EnglishProductName in('Long-Sleeve Logo Jersey, S','All-Purpose Bike Stand') 

GROUP BY dm.EnglishProductName

Output:

The value of the Color property for the textrun which is not a valid Color in SSRS

While setting up background color of a tablix cell or font, below error is generated.

The value of the Color property for the textrun ‘Textbox1.Paragraphs[0].TextRuns[0]’ is “#00ffffff” which is not a valid Color.

Textbox does not support (partial) transparency color.

Need to select valid RGB color values. (instead of selecting " No Color")

Create line break with SSRS expression

We can make line break in SSRS below way

predefined Visual Basic constant can be used

• Using vbCrlf

="Line 1" + vbCrlf + "Line 2"

Could not update list of Fields for the query in SSRS

While Connecting to the stored procedure from the SSRS reports getting below error.

"Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct."

An item with the same key has already been added.

I have added here sample stored procedure created.

create procedure products
as
begin
    with #ProductCat as(
        select DPC.EnglishProductCategoryName,DPC.FrenchProductCategoryName,dpsc.EnglishProductSubcategoryName,DPSC.FrenchProductSubcategoryName
        from DimProductCategory DPC
        join DimProductSubcategory DPSC on DPC.ProductCategoryKey=DPSC.ProductCategoryKey
    )
select PC.EnglishProductCategoryName as Product_Category,PC.FrenchProductCategoryName as Product_Category
,PC.EnglishProductSubcategoryName as Product_SubCategory
from #ProductCat PC
end

Two column names are having in the output with same name.
unfortunately SSMS will not throw any error for this.
Make proper naming while naming the dataset column names.