Hello,
I am having some trouble parameterizing an OData connection to sharepoint.
I created an SSIS package that gets a list from sharepoint. I am using an OData conn manager, the auth type is Microsoft Online Services. In VS2019 I provide the User/Pass, set the correct Service Document Location, and everything works fine: the package connects to the sharepoint list and successfully loads the data into a SQL Server table.
Then I need to deploy the package to a SSIS catalog and parameterize its user/pass. I have an SSIS environment set up, so I create two varibales (user, pass) in it, and assign them to the package connection manager properties: UserName and Password respectively. I do not change anything else: just these two properties.
When I start the package from the SSIS catalog, it works with no errors, but it seems it does not respect the environement variables: if I change the pass variable with a wrong password, the package reports no errors and continues working fine.
There is also the ConnectionString property of the OData conn manager, I can see it when the package is deployed, and one of its key-value pairs is the Cookie="SPOIDCRL=77u/PD94bWwgdmVyc2lvbj0iMS4wIiBlbmN ...". I guess this cookie points to the sharepoint connection set up, thats why it does not respect User/Pass as parameters. If I parameterize this ConnectionString with a string variable with no Cookie, the package execution fails reporting it cannot connect to the sharepoint list, though the user/pass are correct. I cannot figure out how to parameterize the OData connection to sharepoint properly. Any help is appreciated.
PS. I am using both SQL Server and VS of version 2019.
PPS: this is the ConnetionString value that I have as a variable and pass as a parameter (package fails if I delete the Cookie from this string, but does not respect the user/pass variables if I leave it)
"Service Document Url=https://mysite.sharepoint.com/sites/mysite/_vti_bin/mylist.svc;Include Atom Elements=Auto;Include Expanded Entities=False;Integrated Security=False;Persist Security Info=False;Time Out=600;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=False;Max Received Message Size=4398046511104".