Scenario:I have formatted Excel files in ADLS, I want to access them in SSIS package and perform simple transformations and load them in to SQL DB. What connection manager to use for fetching Excel files since these are not CSV files.
Using the Flexible File Source component on the data flow, you can connect SSIS to Azure data lake storage.
Install Azure feature pack for Integration Services (SSIS) extension to get the components required for Azure resources.
Provide all the details in Flexible File Source Editor properties to connect to Azure data lake as mentioned here.
Currently the source file formats supported are Text, Avro, ORC, Parquet.
Also, refer to this MS document for Configure the Azure Data Lake Store Connection Manager and this link for example.