Sort by Month and year in the table

 Solved: Sort by month in Power BI - Microsoft Power BI Community

Download file from web URL in SSIS usng Script task

Scenario.

i got a real time scenario like i want to download File from URL. 

URL is

https://accessgudid.nlm.nih.gov/release_files/download/gudid_daily_update_20200506.zip

in the above URL for every week entire path is same except it will datetime to file name.so task is to check file is there in the given URL.if exists then download to local path.

Solution

Step1

Create two variables like RemoteUrl and LocalFolder


Step2:

take script task and add given name spaces

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;

Step3:

under public void main() add below code

public void Main()
{
// TODO: Add your code here
                   ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;
   
                      Variables varCollection = null;

                   Dts.VariableDispenser.LockForRead("User::RemoteUri");
                  Dts.VariableDispenser.LockForRead("User::LocalFolder");
                  Dts.VariableDispenser.GetVariables(ref varCollection);

                System.Net.WebClient myWebClient = new System.Net.WebClient();
                 string webResource = varCollection["User::RemoteUri"].Value.ToString();
                string fileName = varCollection["User::LocalFolder"].Value.ToString() +                                              webResource.Substring(webResource.LastIndexOf('/') + 1);
                    myWebClient.DownloadFile(webResource, fileName);


Dts.TaskResult = (int)ScriptResults.Success;
}



See the output folder to check file is downloaded or not.

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