Troubleshooting the extract process

Moving databases to a different server
Edit section

After the CRM database has been moved to a different server along with all of the CRM pieces, these steps should be taken to move the warehouse to the same server:

  1. Move the warehouse database to the server.
  2. Change the backup jobs to account for the new warehouse location.
  3. Move the extract 1 and extract 2 jobs for this client to the new server.
    1. Move any associated custom jobs as well.
    2. If the new server's SQL Server Agent account does not have the necessary permissions, set a proxy account with the correct permissions on any operating system steps in the jobs (for stage III, this is steps 6-8 in extract 2).
  4. Rebuild the standard export package for the new warehouse location (see standard export package details).
  5. Recompile the analytics view on the report server for the new warehouse location. (The analytics view is 422rpt001.[client]_Analytics.dbo.vw_DescAnalytics_Complete.)


Remapping the SSIS standard export packages after changes to the Xport definition
Edit section

See standard export package technical information for instructions on rebuilding the packages.



Enabling and disabling extract jobs
Edit section

 The Extract 1 and Extract 2 jobs both must be enabled or disabled to enable or disable the full process.  (Extract 3 is called by Extract 2, so it will only run if Extract 2 is enabled.)  There is a project in place to make Extract 2 respect the enable state of Extract 1, so that only Extract 1 needs to be toggled to control the entire extraction process.


No data is exported
Edit section

If the entry term year and ordinal are unset in the CRM database or if the custom setting for active entry term is null in the CRM database then the standard export (Xport) will be empty.



No data appears in descriptive analytics
Edit section

The first thing to check is whether the client has an valid status changes. Unlike the standard export, the descriptive analytics requires the status change log data, so a client without status changes will show no records in vw_DescAnalytics_Complete even though there are records in AnalyticsMaster;  check in the warehouse the table StatusChanges or the view vw_DescAnalytics_StatusChange.


Job step does not have file system permissions
Edit section

The job steps on the 422sql002 server which execute operating system commands (these are the steps which handle the standard export CSV files) must run under the C422Admin proxy; the SQL Agent account does not have the correct permissions.
This is also related to the inability to write to the step output file.