Is there a way to process "any file" with SSIS on a Azure Data Lake Store?

Question

I have working SSIS script to load a Json File from Data Lake. But the SSIS component named "Azure Data Lake Store Source Editor" doesn't accept file name from a variable, only hard-coded. Since I don't control the creation of new files on the Data Lake I am having to change the SSIS script every time a new file is landed.

I understand SSIS gives you access to the Connection Manager components programmatically inside the Script Component (I am using to parse the Json File).

I can obtain all file names of a Data Lake repository using For Each Component into an SSIS variable. And I can call that variable inside the Script Component.

What I have to do is to overwrite the hardcoded file name of the "Source Editor" with the one in the variable programatically.

I also understand that I could do this with USQL but that is a completely different development path. I am saving it for the last.

I appreciate any kind of guidance in the right direction.

Thanks.

Answer 1

I am not able to test this, but you should be able to parameterise the Data Source component at the Data Flow level, instead of at the Component Level.

If you look in the Properties for the Data Flow that contains your Azure Data Lake Store Source component, you should see a property values collection called Expressions, within which you will be able to set the Component level property values from variables.

There is a summary of this process available here.