SSIS Package when executed via agent job or manually on SQL Server does nothing, but shows it completed...












1














I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.



enter image description here






sql-server ssis-2016







share|improve this question
























  • It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed.
    – Shooter McGavin
    Dec 21 '18 at 17:59










  • Shooter - I see where you are going with this... however I had a copy file command in the first script task and it doesn't even copy the file like it is supposed to when executed on the SQL Server. So it is as if it ignoring all the tasks in control flow!
    – Eric Swiggum
    Dec 21 '18 at 20:01










  • I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one).
    – Shooter McGavin
    Dec 21 '18 at 20:23










  • Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer.
    – Shooter McGavin
    Dec 21 '18 at 20:24










  • Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it.
    – Shooter McGavin
    Dec 25 '18 at 19:40
















1














I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.



enter image description here






sql-server ssis-2016







share|improve this question
























  • It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed.
    – Shooter McGavin
    Dec 21 '18 at 17:59










  • Shooter - I see where you are going with this... however I had a copy file command in the first script task and it doesn't even copy the file like it is supposed to when executed on the SQL Server. So it is as if it ignoring all the tasks in control flow!
    – Eric Swiggum
    Dec 21 '18 at 20:01










  • I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one).
    – Shooter McGavin
    Dec 21 '18 at 20:23










  • Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer.
    – Shooter McGavin
    Dec 21 '18 at 20:24










  • Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it.
    – Shooter McGavin
    Dec 25 '18 at 19:40














1












1








1







I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.



enter image description here






sql-server ssis-2016







share|improve this question















I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.



enter image description here






sql-server ssis-2016




sql-server ssis-2016






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 21 '18 at 19:58







Eric Swiggum

















asked Dec 21 '18 at 14:34









Eric SwiggumEric Swiggum

11913




11913












  • It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed.
    – Shooter McGavin
    Dec 21 '18 at 17:59










  • Shooter - I see where you are going with this... however I had a copy file command in the first script task and it doesn't even copy the file like it is supposed to when executed on the SQL Server. So it is as if it ignoring all the tasks in control flow!
    – Eric Swiggum
    Dec 21 '18 at 20:01










  • I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one).
    – Shooter McGavin
    Dec 21 '18 at 20:23










  • Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer.
    – Shooter McGavin
    Dec 21 '18 at 20:24










  • Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it.
    – Shooter McGavin
    Dec 25 '18 at 19:40


















  • It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed.
    – Shooter McGavin
    Dec 21 '18 at 17:59










  • Shooter - I see where you are going with this... however I had a copy file command in the first script task and it doesn't even copy the file like it is supposed to when executed on the SQL Server. So it is as if it ignoring all the tasks in control flow!
    – Eric Swiggum
    Dec 21 '18 at 20:01










  • I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one).
    – Shooter McGavin
    Dec 21 '18 at 20:23










  • Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer.
    – Shooter McGavin
    Dec 21 '18 at 20:24










  • Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it.
    – Shooter McGavin
    Dec 25 '18 at 19:40
















It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed.
– Shooter McGavin
Dec 21 '18 at 17:59




It's likely that SSIS is simply reporting that the Script Task completed successfully. Instead consider coding something into the script task that looks to see if the data actually transferred successfully, then report status failure or success using Dts.TaskResult = (int)ScriptResults.Failure or Success as needed.
– Shooter McGavin
Dec 21 '18 at 17:59












Shooter - I see where you are going with this... however I had a copy file command in the first script task and it doesn't even copy the file like it is supposed to when executed on the SQL Server. So it is as if it ignoring all the tasks in control flow!
– Eric Swiggum
Dec 21 '18 at 20:01




Shooter - I see where you are going with this... however I had a copy file command in the first script task and it doesn't even copy the file like it is supposed to when executed on the SQL Server. So it is as if it ignoring all the tasks in control flow!
– Eric Swiggum
Dec 21 '18 at 20:01












I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one).
– Shooter McGavin
Dec 21 '18 at 20:23




I think the issue is, even if the script task throws an error during execution, it still completes. I would wrap any commands in the script with a try{}catch(){}. If it ends up in the catch, pass the Dts.TaskResult = (int)ScriptResults.Failure. That will at least ensure you will get the correct failed status. Also you should be able to catch and report or log the exception(assuming there is one).
– Shooter McGavin
Dec 21 '18 at 20:23












Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer.
– Shooter McGavin
Dec 21 '18 at 20:24




Also, since you said it seems the script task is what is actually failing, it would probably be a good idea to paste the contents of the script task in your question. That might shed some more light on the answer.
– Shooter McGavin
Dec 21 '18 at 20:24












Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it.
– Shooter McGavin
Dec 25 '18 at 19:40




Wrap the copy operation in the script task in a try catch block. Then place the failure status in the catch block. If the copy operation fails the try catch should catch it.
– Shooter McGavin
Dec 25 '18 at 19:40










1 Answer
1






active

oldest

votes


















0














I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.



[https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]





share





















    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225567%2fssis-package-when-executed-via-agent-job-or-manually-on-sql-server-does-nothing%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.



    [https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]





    share


























      0














      I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.



      [https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]





      share
























        0












        0








        0






        I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.



        [https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]





        share












        I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.



        [https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]






        share











        share


        share










        answered 5 mins ago









        Eric SwiggumEric Swiggum

        11913




        11913






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f225567%2fssis-package-when-executed-via-agent-job-or-manually-on-sql-server-does-nothing%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Ronny Ackermann

            Image
            Ronny Ackermann Nation Deutschland  Deutschland Geburtstag 16. Mai 1977 Geburtsort Bad Salzungen, DDR Größe 184 cm Gewicht ca. 69 kg Beruf Hauptfeldwebel bei der Bundeswehr Karriere Verein Rhöner WSV Dermbach Status zurückgetreten Karriereende 16. März 2011 Medaillenspiegel Olympische Medaillen 0 × 3 × 0 × WM-Medaillen 4 × 4 × 1 ×  Olympische Winterspiele 0 Silber 0 Salt Lake City 2002 Sprint 0 Silber 0 Salt Lake City 2002 Team 0 Silber 0 Turin 2006 Team  Nordische Skiweltmeisterschaften 0 Bronze 0 Lahti 2001 Einzel 0 Gold 0 Val di Fiemme 2003 Einzel 0 Silber 0 Val di Fiemme 2003 Sprint 0 Silber 0 Val di Fiemme 2003 Team 0 Gold 0 Oberstdorf 2005 Einzel 0 Gold 0 Oberstdorf 2005 Sprint 0 Silber 0 Oberstdorf 2005 Team 0 Gold 0 Sapporo 2007 Einzel 0 Silber 0 Sapporo 200...
            Read more

            Köttigit

            Image
            Köttigit Büscheliges Mineral-Aggregat aus weißen, nadeligen Köttigitkristallen; Fundort: Grube Silbereckle, Reichenbach, Lahr/Schwarzwald, Baden-Württemberg (Sichtfeld 8 mm) Allgemeines und Klassifikation Andere Namen Zinkarseniat Chemische Formel Zn 3 [AsO 4 ] 2 ·8H 2 O [1] Mineralklasse (und ggf. Abteilung) Phosphate, Arsenate und Vanadate System-Nr. nach Strunz und nach Dana 8.CE.40 (8. Auflage: VII/C.13) 40.03.06.05 Kristallographische Daten Kristallsystem monoklin Kristallklasse; Symbol monoklin-prismatisch; 2/ m Raumgruppe C 2/ m (Nr. 12) Vorlage:Raumgruppe/12 Gitterparameter a  = 10,24 Å; b  = 13,41 Å; c  = 4,76 Å β  = 105,2° [1] Formeleinheiten Z  = 2 [1] Häufige Kristallflächen {010}, {201}, Physikalische Eigenschaften Mohshärte 2 bis 3 [2] Dichte (g/cm 3 ) gemessen: 3,33; berechnet: 3.24 [2] Spaltbarkeit vollkommen nach {010} [2] Bruch; Tenazität faserig [3] ...
            Read more

            MySQL 8.0.15 starts normally but any connection hangs

            Image
            .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0; } 0 I dropped a large table (80G) Friday morning and got a crash: InnoDB: ###### Diagnostic info printed to the standard error stream 2019-04-19T18:48:55.445507Z 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 37617336 seconds. We intentionally crash the server because it appears to be hung.[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung. 2019-04-19T18:48:55.445541Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:625 thread 140252955932416 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you g...
            Read more