FILE WATCHER IN A FOLDER IN SSIS

Scenario

I have a scenario like this in real time. Client will paste the files in a folder. if file is there in a folder then you have to load into database otherwise package should check again for 10 min. Like this it should check every 10 min upto 1 Hour.

1) Job should run only once I.e you cannot schedule the job for every 10 min

Answer:

Step1

 create variables like below.

Step2: take script task  and select read-only variables and read-write variables as below

Step3:

go to edit script and add below code.

   public void Main()
        {
            // TODO: Add your code here
            string SourceFolderPath = Dts.Variables["User::SourceFolderPath"].Value.ToString();

           var directory = new DirectoryInfo(SourceFolderPath);
            FileInfo[] files = directory.GetFiles();

            //Declare and initilize variables
         
            int i = 0;
            //Get one Book(Excel file at a time)
             foreach (FileInfo file in files)
            {

                 i = i + 1;
                 Dts.Variables["User::count"].Value = i;
            }

            if (i == 0)
            {
                find();
            }
         }
        public void find()
        {
            int totalsleepcount = (Int32)Dts.Variables["User::totalsleepcount"].Value;
            if (totalsleepcount <= 6)
            {

                // sleep for 10 min
                System.Threading.Thread.Sleep(600000);
                totalsleepcount = totalsleepcount + 1;

                Dts.Variables["User::totalsleepcount"].Value = totalsleepcount;
                Main();
            }
         
        }

Step4:

drag and drop data flow task. this data flow task should run when count of file is greater than zero.
this can be achieved by precedence constrain like below.

thanks

if you want download this package click me

PIVOT EXAMPLE2 T-SQL

This is my sample Table, i want to pivot the category column and get the sales,stock and target as rows

I want the sample output in this form as shown in the below wherein the categories are in place of columns and columns in place of row

Solution

you can create table by using the Following code

create  table Table1 

    (Branch varchar(9), Category varchar(9), Sales INT,Stock INT,Target INT)

;

INSERT INTO Table1

    (Branch, Category, Sales, Stock,Target)

VALUES

    ( 'mumbai', 'panel', 10,4,15),

    ( 'mumbai', 'AC', 11,7,14),

    ( 'mumbai', 'Ref', 7,2,10),

    ( 'Delhi', 'panel',20,4,17),

    ( 'Delhi', 'AC', 5,2,12),

    ( 'Delhi', 'Ref', 10,12,22)

;

Step1

In order to do pivot make values in the single column like below.

T- SQL code

Step 2

Let suppose category has more values so it is difficult to mention all values. So we have to use dynamic pivot.Stay tune for pivot example3 t-sql.

PIVOT EXAMPLE1 T-SQL

Problem

 Output

Solution


select code,
max(case when number=1 then pack end) pack1,
max(case when number=1 then uom end) uom1,
max(case when number=2 then pack end) pack2,
max(case when number=2 then uom end) uom2,
max(case when number=3 then pack end) pack3,
max(case when number=3 then uom end) uom3,
max(case when number=4 then pack end) pack4,
max(case when number=4 then uom end) uom4,
max(case when number=5 then pack end) pack5,
max(case when number=5 then uom end) uom5
from
(
SELECT  CODE,pack,uom ,row_number() over(partition by code order by pack) as number FROM [dbo].[UNPIVOT]
) a
group by code

UNPIVOT EXAMPLE2

INPUT

 OUTPUT FORMAT1

 OUTPUT FORMAT2

SOLUTION

Table2 =

FILTER(

UNION(

SELECTCOLUMNS('Table1',"Company",[Company],"Date",[Date],"Vendor",[Vendor 1],"Charge Type",[Charge Type 1],"Charge",[Charge 1]),

SELECTCOLUMNS('Table1',"Company",[Company],"Date",[Date],"Vendor",[Vendor 2],"Charge Type",[Charge Type 2],"Charge",[Charge 2]),

SELECTCOLUMNS('Table1',"Company",[Company],"Date",[Date],"Vendor",[Vendor 3],"Charge Type",[Charge Type 3],"Charge",[Charge 3])

),

"Vendor"<>""&&"Charge Type"<>""&&"Charge"<>"")

TO DOWNLOAD PBIX FILE CLICK ME

UNPIVOT MULTIPLE COLUMNS

How to do unpivot multiple columns?


Type1:

Input Table 

OUTPUT FORMAT

STEP1

 select the columns the columns which is constant in a table. Then select unpivot other columns

STEP2

Then split the value by the number of characters so Quater and  target seperated

 STEP3

 STEP4


Then Pivot the Attribute column2 based on the value column to get results

To download pbix files click me

Create Dynamic Header in power bi

How to create a dynamic header?

Let suppose table contains the product category and product sub category. Then header only to show the product category which is filtered.

This can be achieved by card visual and DAX expression.

Step1: Create new measure.

Step2: Write DAX expression like below.

header =
CALCULATE (
    IF (
        ISFILTERED ( DimProductCategory[EnglishProductCategoryName] ),
        "Results for Product Category name   "
            & CONCATENATEX (
                VALUES ( DimProductCategory[EnglishProductCategoryName] ),
                ( DimProductCategory[EnglishProductCategoryName] ),
                "|"
            ),
        "Showing results for all Categories"
    )
)

Step3: Take card visual and add this measure  

HOW TO FIND SUM OF LAST 5 MONTHS CUMULATIVE SALES

How to calculate last 5 months cumulative sales?

LAST 5 MONTHS CUMULATIVE SALES =
CALCULATE (
    [Total sales],
    DATESINPERIOD (
        DimDate[FullDateAlternateKey],
        LASTDATE ( DimDate[FullDateAlternateKey] ),
        -5,
        MONTH
    )
)

syntax DATESINPERIOD

DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)

we can also find last X months average by using the above function.

DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question

What is the difference between SamePeriodLastYear and ParallelPeriod?

The first difference is that ParallelPeriod gives you the option to go as many as intervals you want back or forward. If you want to get the sales for the last months; then ParallelPeriod is your friend. for calculating the sales of 2 years ago, then ParallelPeriod is your friend.

Dynamic Period is another difference between these two functions; If you think that the result of SamePeriodLastYear and the ParallelPeriod (when it is used with Year interval) are the same, continue reading. below is an example of these two measures:

For August 2006 for example; the SamePeriodLastYear gives us the sales of August 2005. However, the ParallelPeriod with year interval returns the sales for the entire year 2005.

DateAdd vs ParallelPeriod

Comparing these two functions with each other; you can see that DateAdd works on the period dynamically (like SamePeriodLastYear), but the ParallelPeriod works statically on the interval mentioned as the parameter.

That leads us to the conclusion that DateAdd(<date field>,-1, Year) is similar to SamePeriodLastYear, however, one difference is still there:

DateAdd vs SamePeriodLastYear

SamePeriodLastYear only goes one year back, DateAdd can go two years back or even more. DateAdd is a customized version of SamePeriodLastYear.

Conclusion

In summary, there are differences between these three functions:

• DateAdd and SamePeriodLastYear both work based on the DYNAMIC period in the filter context
• ParallelPeriod is working STATICALLY based on the interval selected in the parameter
• ParallelPeriod and DateAdd can go more than one interval back and forward, while SamePeriodLastYear only goes one year back.
• DateAdd works on the interval of DAY, as well as month, quarter and year, but ParallelPeriod only works on month, quarter, and year.
• Depends on the filter context you may get a different result from these functions. If you get the same result in a year level context, it doesn’t mean that all these functions are the same! Look more into the detailed context.

CALCULATE DIFFERENT MEASURES WITH DATE TABLE

i have used Adventureworks for all these examples.

How to calculate Total sale amount?

Total sales =
SUMX (
    FactInternetSales,
    FactInternetSales[SalesAmount] * FactInternetSales[OrderQuantity]
)

How to calculate YTD SALES?

Method1

TOTAL YEAR TO DATE SALES =

TOTALYTD ( [Total sales], DimDate[FullDateAlternateKey] )

Method2

TOTAL YTD USING CALCULATE FUNCTION =

CALCULATE ( [Total sales], DATESYTD ( DimDate[FullDateAlternateKey] ) )

How to calculate YTD SALES IF fiscal year doesn't

end with December 31?

 Method1

TOTAL YEAR TO DATE SALES =

TOTALYTD ( [Total sales], DimDate[FullDateAlternateKey],"06-30")

Method2

TOTAL YTD USING CALCULATE FUNCTION =

CALCULATE ( [Total sales], DATESYTD ( DimDate[FullDateAlternateKey] ,"06-30" )

How to get total sales for the previous year
for the same period?

 Method1

LAST YEAR SALES FOR the SAME PY =
CALCULATE (
    [Total sales],
  SAMEPERIODLASTYEAR ( DimDate[FullDateAlternateKey] )
)

Method2

TOTAL SALES LAST YEAR FOR THE SAME PY =
CALCULATE ( [Total sales], DATEADD ( DimDate[FullDateAlternateKey], -1, YEAR ) )

Method3

Total sales using parallel period =
CALCULATE (
    [Total sales],
    PARALLELPERIOD ( DimDate[FullDateAlternateKey], -1, YEAR )
)

DIFFERENCE BETWEEN CALENDAR AND CALENDARAUTO

CALENDAR FUNCTION

Syntax

CALENDAR(<start_date>, <end_date>)  
Examples



The following formula returns a table with dates between January 1st, 2005 and December 31st, 2015.

=CALENDAR (DATE (2005, 1, 1), DATE (2015, 12, 31))

For a data model which includes actual sales data and future sales forecasts. The following expression returns the date table covering the range of dates in these two tables.

=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))




CALENDERAUTO FUNCTION

 CALENDARAUTO  automatically finds the minimum and maximum year of all the date columns included across the whole data model, and generates all the dates included between these years.






Example

In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.

CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.

CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2012.

Aggregate Functions

DAX has a number of aggregate functions.

• MIN
• MAX
• Average
• SUM
• SUMX

Counting Functions

Other counting functions in DAX include −

• DISTINCTCOUNT
• COUNT
• COUNTA
• COUNTROWS
• COUNTBLANK

Logical Functions

Following are the collection of Logical functions −

• AND
• OR
• NOT
• IF
• IFERROR

 stringFunctions

• REPLACE
• SEARCH
• UPPER
• FIXED
• CONCATENATE

DATE Functions

• DATE
• HOUR
• WEEKDAY
• NOW
• EOMONTH

INFORMATION Functions

• ISBLANK
• ISNUMBER
• ISTEXT
• ISNONTEXT
• ISERROR