none
SSIS package won't run On server but it will in SSDT RRS feed

  • Question

  • Can someone please help me? I've worked on this for several days now.

    Situation: I have an SSIS package from MSSQL 2008, written in BIDS FOR 2005 that I need to get running on MSSQL 2016.

    Status: I have SSDT for VS2017 running the package which I imported and it's running on the server in SSDT and it works fine. SSDT incidentally is running On the MSSQL server.

    Problem: It's not running FROM MSSQL 2016 in the package store (from when I "deployed it" from SSDT).

    I've tried running it by right-clicking, I've tried creating a "job", I've tried changing to "32 bit" on the server side, I have currently disabled the 64bit runtime inside VS2017-SSDT. Still not working.

    Note: I am "encrypting sensitive" with a password and I've change the SQL job to CmdExec and added /DECRYPT mypass

    but still not working.

    Please help. thanks.

    I'mgetting these two errors (see attached image)

    Tuesday, November 14, 2017 4:08 PM

All replies

  • Looks like path is not accessible

    Since you say SSDT was running in server I take that path exists on server

    If thats the case please give executing account access to the path. By default it will be SQL Server Agent service account that job uses unless you've created a new proxy account. Give it write access to the path


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Tuesday, November 14, 2017 4:14 PM
    Tuesday, November 14, 2017 4:14 PM
  • No read the First error. The second error is caused because the Excel file can't be created. But that's because of Step 1 error.

    What does the Excel Error mean? Do I need to install some driver?  

    This is MSSQL 2016 running on Server 2016.

    Tuesday, November 14, 2017 4:54 PM
  • I gave Full Control to "EVERYONE" and still same error.

    Do I need a special driver so Excel works inside of MSSQL 2016? Please help.

    Tuesday, November 14, 2017 5:07 PM
  • I gave Full Control to "EVERYONE" and still same error.

    Do I need a special driver so Excel works inside of MSSQL 2016? Please help.

    Because you use JET it should be run in 32 bit

    If you're already doing it, then it might the known issue caused by below KB

    https://blogs.msdn.microsoft.com/dataaccesstechnologies/2017/10/18/unexpected-error-from-external-database-driver-1-microsoft-jet-database-engine-after-applying-october-security-updates/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 5:19 PM
  • How do I switch to ACE? Do I need to install a driver or just pick it in SSDT (VS2017)?
    Tuesday, November 14, 2017 5:28 PM
  • How do I switch to ACE? Do I need to install a driver or just pick it in SSDT (VS2017)?

    you need to install it

    see

    https://www.microsoft.com/en-us/download/details.aspx?id=54920


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 5:41 PM
  • Do I need to reboot the server afterwards?

    Do I need to choose the ACE connection just to replace the "DestinationConnectionExcel" or to replace the "SourceConnectionOLEDB" module in my project?

    Tuesday, November 14, 2017 5:50 PM
  • It says  SSIS is not supported?????????

    This isn't "play time" this is a business! Can someone please help me here?

    SSIS is not supported?

    WHat is your SSIS version?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 5:56 PM
  • Whatever SSIS is called. I'm using this:

    1.) MSSQL 2016

    2.) On Server 2016

    3.) I'm using SSDT (VS 2017) stand alone.

    4.) The package (which I call SSIS, who knows what MSFT Calls it today) is working ON the server inside of the VS2017....but NOT WHEN I "DEPLOY" it to the SQL server itself.

    5.) I am deploying it to something called "Integration Services Catalog" SSISDB is the next folder down, then I made a folder myself.

    The image I posted above is the error I get when I try to run it from there. What do I do now to get my package to work ON MSSQL just not inside of  SSDT?

    Tuesday, November 14, 2017 5:59 PM
  • The URL you posted says this:

    . The ACE provider will work, however it is not supported for use with programs such as SSIS, Management Studio, or other applications. 

    Do I use it or not? What exactly Do I need to do to my package that is using: 

    DestinationConnectionExcel" or to replace the "SourceConnectionOLEDB" Curently ???

    Tuesday, November 14, 2017 6:00 PM
  • And why "Excel Source" when I am EXPORTING not IMPORTING to Excel?
    Tuesday, November 14, 2017 6:02 PM
  • The URL you posted says this:

    . The ACE provider will work, however it is not supported for use with programs such as SSIS, Management Studio, or other applications. 

    Do I use it or not? What exactly Do I need to do to my package that is using: 

    DestinationConnectionExcel" or to replace the "SourceConnectionOLEDB" Curently ???

    Sorry to say

    But I dont see such a description

    All I see is this

    The Office System Drivers are only supported under certain scenarios, including: 

    1. Desktop applications which read from and write to various files formats including Microsoft Office Access, Microsoft Office Excel and text files.
    2. To transfer data between supported file formats and a database repository, such as SQL Server. For example, to transfer data from an Excel workbook into SQL Server using the SQL Server Import and Export Wizard or SQL Server Integration Services (provided the SSIS jobs run in the context of a logged-on user with a valid HKEY_CURRENT_USER registry hive).

    Which says its used by SSIS.

    Also I have been using ACE provider since SSIS 2008 days


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, November 14, 2017 6:05 PM
  • What do I do? Big picture... Back to my original question. Is there a patch coming out today??? What do I do?
    Tuesday, November 14, 2017 6:11 PM
  • ?????????????
    Tuesday, November 14, 2017 8:27 PM
  • Hi commandlinekid,

    -->> The path is not a valid path.

    Please try using UNC path and check if this issue persist.

    Besides, it's recommended to upgrade your SSIS 2008 packages to SSIS 2016 since you run it on SQL Server 2016.

    Regards,

    Pirlo Zhang  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 15, 2017 6:35 AM
    Moderator
  • This issue is resolved. It was operator error. I was told I was on the local server but actually the database was running on another server which is why it kept erroring. So it didn't' have anything to do with the issue herein.

    That said, SSDT/SSIS/BIDS...... NEEDS AUTHENTICATION WORK SO PEOPLE DON'T HAVE TO SPEND DAYS TROUBLESHOOTING SUB PAR, NON DESCRIPTIVE ERROR MESSAGES.

    Thank you for your help


    Friday, November 17, 2017 10:20 PM
  • Hi commandlinekid,

    Thanks for sharing the information with us.

    Thanks,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 20, 2017 9:57 AM
    Moderator