Issue: SSIS Packages run much slower after upgrading from an earlier version of DSP
Versions affected: DSP versions 6.7.x
Cause: DSP was changed in version 6.7 to build SSIS packages using ODBC rather than OleDB, which was used in earlier versions. This was done due to an announcement by Microsoft that they were deprecating OleDB. (They have now revised this stance.)
Resolution: SSIS packages are once again built using OleDB as of the 7.0 release of the DSP.
Workaround: There is a workaround available by creating and using Manual SSIS packages using the steps described below.
- Create a manual SSIS package with an OleDB connection. Details on how to create such a package can be found in this Microsoft created video or from other searchable online resources: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/cc952921(v=sql.100).
- Ensure that the created package is located in the configured file path. This is found in Common > Configuration > Modules > Collect > Parameters > Package Info tab:
- In Collect, navigate to your Target Source and click on Tables.
- Select “ManualSSIS” as the Package Type
- On the vertical of the package, under the Advanced Settings tab, enter the name of the created manual SSIS package (note: the package name will need to manually edited, it will not show in the dropdown menu):
- There is no need to build the package as it is already built when created. You can now run a refresh with this package.