Getting CRM Data from Query in SSIS Package in Job Agent RRS feed

  • Question

  • Hi community,

    I created a SSIS package to obtain data from a custom entity, convert it to an xml file, store that file locally and then send it via a ftp task to our website service provider. The OLE DB adapter connects via Windows Authentication (not SQL - Authentication). From my work pc within Visual Studio this works perfectly.

    Now we want to run the package via the SQL JOB Agent directly from the server. The *.dtsx and xml files and directory structure has been duplicated on the server. Running the query in the SSMS under RUN AS "CRMAdmin" (Crm Admin user) works as expected, but running the same query when logged in SSMS as windows authentication returns NULL (the Windows Server Account is not an CRM user). In another MS forum the usage of a proxy account has been recommended. Can we execute the SSIS Package Execution in the Job Agent with the mentioned CRM user from above ("CRMAdmin")? Are there any security issues related using the latter? Unfortunately, we cannot use Scribe for this task, since we have to do several joins etc. The CRM Adapter in Scribe allows 2 parent relationships only.

    Thanks in advance

    Wednesday, September 21, 2016 3:20 PM