SSIS OData conn parameterized

Richardo Roias 6 Reputation points
2020-09-07T14:22:32.097+00:00

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".

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,502 questions
0 comments No comments
{count} vote

1 answer

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2020-09-08T07:42:28.883+00:00

    Hi Richardo,

    Could you please share the screenshot of the Odata Connection Manager Editor ?

    Please refer to the following links:

    OData Connection Manager

    Parameterizing Database Connection in SQL Server Integration Services

    Best Regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.