Login failed. The login is from an untrusted domain

If you started seeing this message;

One of the reason might be that your Azure AD password has been expired. Try to login here;

https://myapps.microsoft.com/

User Function – Self service password reset

Click on upper right corner on your user icon and select “View Account”. Click on “Change Password” tile. Enter your old password and new password. Logout and Log back in with your new password. Microsoft Authenticator or Google Authenticator is required to authenticate.

If the computers are not joined to domain then user has to open Credential Manager on their computers to store new credentials if they are using windows authentication for any cloud services.

If user has forgotten his/her password, then admin can follow Admin Function and reset user password to a temporary password. User has to go through same steps again to reset the password.

Admin Function – To Reset the password for other User

Click on Admin;

Click on Reset password under user management. Select your user, change password. This is a temporary password. Communicate this password with user and ask them to follow self service password reset.

Return a Json Object from another function call

When we try to capture a value from called method we get undefined. The reason is that data hasn’t been fetched yet. Fetch takes some time to make the call and send back the result, and it’s asynchronous, Here is an example;

function getApi() {
  var obj;

  fetch("https://foo.apicode.com/posts/1")
    .then((res) => res.json())
    .then((data) => (obj = data));

  return obj;
}

let x = getApi();
console.log(x);

When we console.log the value, getApi() didn’t finish yet, therefore didn’t set the obj

The fetch method is asynchronous, so obj is undefined because the code is going to the next instruction without waiting the fetch. We can simply use async/await method that is a great way to make asynchronous calls because await will wait for the result before going to the next instruction.

async function getApi() {

      const response = await fetch("https://foo.apicode.com/posts/1")
        
      const obj = await response.json()
      
      return obj;
    }

(async() => {
   let x = await getApi();
   console.log(x);
})()

Resources

https://stackoverflow.com/questions/49432579/await-is-only-valid-in-async-function

SSIS Excel Data Source Column overriding

When an excel data source is used in SSIS, the data types of each individual column are derived from the data in the columns. How do we override this behaviour?

Ideally we would like every column delivered from the excel source to be string data type, so that data validation can be performed on the data received from the source in a later step in the data flow.

Just go into the output column list on the Excel source and set the type for each of the columns. Let excel do its guessing game. We are interested in output column and type. This works.

To get to the input columns list right click on the Excel source, select ‘Show Advanced Editor’, click the tab labeled ‘Input and Output Properties’.

A potentially better solution is to use the derived column component where you can actually build “new” columns for each column in Excel. This has the benefits of

  1. You have more control over what you convert to.
  2. You can put in rules that control the change (i.e. if null give me an empty string, but if there is data then give me the data as a string)
  3. Your data source is not tied directly to the rest of the process (i.e. you can change the source and the only place you will need to do work is in the derived column)

This is another work around but it does not work at run time. You can see the data at design time though;

Write your SQL command and convert columns to text.

You can verify this on Advance tab / Input and Output Properties tab of Excel source. All of converted columns under External Column would be changed to “Unicode text stream” data type

SSIS Expression Sample List

Sometime its kind a hard to remember different SSIS syntax and how to use them. I build up a list to help me out. Expressions used are from AdventureWorks sample provided with Microsoft SQL Server.

Converting String to Guid in Derived column expression;

(DT_GUID)("{" + [ColumnName] + "}")

If using dynamic Sql in script component or variables, get Guid from database as String;

CAST([GuidColumn] AS NVARCHAR(60)) AS GuidColumn

Database will convert string to Guid on query submission.

Boolean expression

If incoming data type is text then use this for Boolean conversion;

(DT_BOOL)((DT_WSTR,1)Rejected == “1” ? TRUE : FALSE )

This will also handle null values in incoming data.

How to Get file name and file extension in SSIS Expression?

Suppose this is the file name;

@FileName = 6be8bf19-b715-ec11-b1cb-000d3adde0a7.xlsx

This is how we will get extension and file name;

--get file extensions
REVERSE(left(REVERSE(@[User::FileName]), FINDSTRING(REVERSE(@[User::FileName]) , "." , 1 ) - 1))

Result
------
xlsx

------get file name
SUBSTRING(@[User::FileName], 1, FINDSTRING(@[User::FileName] , "." , 1 ) - 1)

Result
------
6be8bf19-b715-ec11-b1cb-000d3adde0a7

--same result can be achieved by using this statement

REVERSE(LEFT(@[User::FileName], FINDSTRING(@[User::FileName] , "." , 1 ) - 1))

Result
------
6be8bf19-b715-ec11-b1cb-000d3adde0a7

How to get only Date from DateTime variable?

SUBSTRING( (DT_STR,50, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 10)