Share via

how to schedule a VS2019 SSIS package when only database available is SQL Server 2012 and Cannot upgrade

Desai, Rohitkumar (FAA) 11 Reputation points
2022-09-29T17:07:50.247+00:00

I have 2012 SQL server and not in position to upgrade. I need to import data from SharePoint List into this database. Here is what I have done so far:

  1. Installed VS2019 and developed SSIS package, which uses OData component in SSIS. This package works fine and gets data from Sharepoint list just fine. However, I cannot save this package into MSDB of SQL 2012 but only save in file system. I could not schedule it to run as a job in SQL Server 2012. I tried to run from command line using VS2019 command line utility DTExec.exe but no scuccess.
  2. I attempted to follow the following series of article but cannot find json reference. Also, not sure if it is available in SQL server 2012 as I think the article written by Arvind Kushwaha in the link below is for SQL server 2017.

https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=&cad=rja&uact=8&ved=2ahUKEwjB6vXVpbr6AhV3jokEHaGPBu0QFnoECAwQAQ&url=https%3A%2F%2Fwww.c-sharpcorner.com%2Farticle%2Fload-data-to-a-sql-table-from-sharepoint-list-using-ssis-part-three%2F&usg=AOvVaw1POqbO_cR9FFmKQ7jI-e9R.

So, I cannot fully developed custom modules required to get the data.

I am stuck! Thanks in advance for your help!

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Michael Taylor 51,346 Reputation points
    2022-09-29T17:41:53.997+00:00

    In VS 2019 you should have installed the SQL Integration Services extension to enable SSIS support. Then open the project in VS 2019. Go to the project's properties and then to the Configuration Properties \ General section. Set the TargetServerVersion to SQL Server 2012. This should allow you to build and deploy to SQL 2012 normally.

    JSON is not formally supported in SQL until at least 2016. Prior to that version you cannot query JSON within the DB directly. You'll have to treat such data as regular strings and do string searching as you would anything else.

    0 comments
  2. Desai, Rohitkumar (FAA) 11 Reputation points
    2022-09-29T18:20:03.777+00:00

    I had tried that already. After your message, I tried it again but wasn't successful. I verified that "SQL Server Integration Services Project" extension is already installed in VS2019. I went again to Configuration Properties and changed target database version to SQL Server 2012. I accepted warning re downgrade from 2019 to 2012 - The package broke as 2012 does not have OData component. I changed back to SQL server 2019 and package remained broke as all OData connection properties were wiped out during downgrade-upgrade process.

    Wonder if there is a way to make whatever the runtime VS2019 is using and schedule the VS2019 package to get data in SQL Server 2012..

  3. Desai, Rohitkumar (FAA) 11 Reputation points
    2022-09-29T18:40:16.65+00:00

    Here is the precise message when target database version 2012 is chosen:

    TITLE: Package Validation Error

    ------------------------------

    Package Validation Error

    ------------------------------

    ADDITIONAL INFORMATION:

    Error at Data Flow Task [SSIS.Pipeline]: Cannot find the connection manager with ID "{CAA9D47D-2FE4-4533-A0C1-C6E73DD601BE}" in the connection manager collection due to error code 0xC0010009. That connection manager is needed by "ZOA PDWB Log OData Source.Connections[Connection]" in the connection manager collection of "ZOA PDWB Log OData Source". Verify that a connection manager in the connection manager collection, Connections, has been created with that ID.

    Error at Data Flow Task [SSIS.Pipeline]: ZOA PDWB Log OData Source failed validation and returned error code 0xC004800B.

    Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.

    Error at Data Flow Task: There were errors during task validation.

    (Microsoft.DataTransformationServices.VsIntegration)

    ------------------------------

    It appears that OData component does not exist in SSIS ToolBox - Sources as soon as Database version 2012 is chosen..

  4. Desai, Rohitkumar (FAA) 11 Reputation points
    2022-09-29T19:45:24.607+00:00

    Thanks for quick suggestions. I will research but if you have a download link for "MS OData Source for SQL Server 2012" please let me know. Thanks again!

  5. Desai, Rohitkumar (FAA) 11 Reputation points
    2022-09-29T21:21:39.03+00:00

    You have very helpful. I had found the download link and installed it. The DTS package with OData connection now works just fine. In VS2019 though I cannot save the package in MSDB of 2012. It does not even bring the form where you can choose FIle System or Package Store..which I have been using say VS2010 for years. Not sure what configuration change I will need. On the other hand I quickly tested in VS2010 and had successfully created a test package which uses the same OData source configuration and there I have been saving packages to MSDB of SQL Server 2012 - so in the worse case, if I cannot save package from VS2019, all I will have to do is re-create same package and save it in MSDB & schedule it..