SSIS package succeeds but fails

  • Here's an odd one for you. SSIS 2012 package put into SSIS 2019 (SQL Server, not Catalog or file system). It's called by a job and has 3 tasks in it. Execute SQL, File System Task and Send Email task (in that order). The File System Task copies a file from one directory to another using a source variable, with expressions that were populated by the Execute SQL task, and a destination variable.

    No changes have been made to this package in at least a year. Works fine in 2012 and ... sort of works fine in 2019?

    The package finds the file, moves the file and sends a notification to the users that the file is now in their folder. Weirdly, though, the SQL Agent job fails on the File System Task with the error "An error occurred with the following error message: "The filename, directory name, or volume label syntax is incorrect.".

    I can run the Execute SQL code in SSMS and see the proper data. The file does actually get found and moved. The users get their email. But every single time, we get this error as if something is wrong with the source or destination. Every other time I've seen this error, it doesn't move anything or continue on with the rest of the package because of the failure.

    Has anyone seen such weird behavior from SSIS 2019 before? I'm at a loss as to why we're getting the error when everything is working fine. 

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • If you check the All Executions SSISDB report, does that provide any additional information?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • The report returns nothing. It's completely empty despite our QA team having run several of the jobs that call the packages. But again, we're not using SSIS Catalog and the only place I see that report is under the heading "Integration Services Catalogs". So I don't think it's a true metric of what we're doing.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • event viewer - see this link for some info https://docs.microsoft.com/en-us/sql/integration-services/performance/events-logged-by-an-integration-services-package?view=sql-server-ver15

    better to run packages on filesystem or catalog - easier to check errors and easier to change logging level

  • frederico_fonseca wrote:

    event viewer - see this link for some info https://docs.microsoft.com/en-us/sql/integration-services/performance/events-logged-by-an-integration-services-package?view=sql-server-ver15

    better to run packages on filesystem or catalog - easier to check errors and easier to change logging level

    Thank you, but none of that information actually helps me with the current issue.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin wrote:

    frederico_fonseca wrote:

    event viewer - see this link for some info https://docs.microsoft.com/en-us/sql/integration-services/performance/events-logged-by-an-integration-services-package?view=sql-server-ver15

    better to run packages on filesystem or catalog - easier to check errors and easier to change logging level

    Thank you, but none of that information actually helps me with the current issue.

    Well, it might help you troubleshoot, if you put the package in the catalog and try running it again.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Brandie Tarvin wrote:

    frederico_fonseca wrote:

    event viewer - see this link for some info https://docs.microsoft.com/en-us/sql/integration-services/performance/events-logged-by-an-integration-services-package?view=sql-server-ver15

    better to run packages on filesystem or catalog - easier to check errors and easier to change logging level

    Thank you, but none of that information actually helps me with the current issue.

    so you did go to windows event viewer and looked at all the messages logged there and none had any useful info?

  • We don't have catalogs set up and we've got such a tight deadline on the server upgrade that I don't have time to set them up right now. Catalogs are a "nice to have as soon as we've verified current upgrades work".

    This false alarm is driving me nuts, but it's a minor issue that we'll just have to live with if no one has any thoughts on it. I just wish I knew why it was failing after all the work was done.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Did you upgrade the package in Visual Studio?  How did you move the package from one instance to the other - was it done through VS?

    If this is really stored in SQL Server - it would be in the msdb database, and if it exists there you should be able to query the ssis tables in that database.  Not sure if the sysssislog table will have anything - but you can check.

    Without further information - there really isn't any way for us to help identify the problem.  To troubleshoot - the first thing I would do is open the package in Visual Studio and run it manually.  Most likely just opening it in VS will upgrade it and that might solve the issues - and if you do open in VS then it would be worth the time to convert to project deployment and deploy to the catalog.

    Setting up the catalog takes minutes - and there is no reason not to get it done on the new server.  Even if you push of migration of packages to the catalog, it should still be setup and ready on the new system.  That at least allows for future deployments and builds to be done to the catalog.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Despite the above reply, if anyone has ever seen this behavior before or has any ideas (aside from telling me to change how I run the package), please let me know.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin wrote:

    Despite the above reply, if anyone has ever seen this behavior before or has any ideas (aside from telling me to change how I run the package), please let me know.

    I have seen similar issues - where the package was not upgraded or deployed appropriately.  That is why I asked how this was deployed to the new server - because it does matter.

    Has this package been opened in VS and tested?  If not - that is the next step to figuring out the problem.

    As for the catalog, there is absolutely no reason not to set it up - even if you don't plan on using it right away.  Pushing it off to later - just pushes it off to the 'next' server upgrade (which means it doesn't get done) and you cannot even start planning new projects to use the catalog because it hasn't been installed and configured.  So new projects use the 'current' methodology and you end up with the same issues - again.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I wasn't actually the one who pushed the SSIS packages to this particular server. I'm not sure if it was just imported straight from 2012 to 2019 or if it was upgraded. I need to ask my coworker. What I do know is that we have other file system tasks in other packages on the same server and none of them are having this particular issue. But I will check the MSDB tables and see if there's a clue there. Windows Event monitor as well, though I don't actually expect to see anything there given past experience with those logs. I've never seen this specific of an error show up on the Event Monitor or anything from an SSIS package even under the Application Log.

    Regarding SSIS Catalog, I'm about to rant. Please don't take it personally.

    I'm starting to understand why some users of this site get annoyed with those of us who have been here a long time. Some of us spend so much time preaching about what their systems should be like and not enough time trying to help them resolve the problems they currently have that we're literally driving them away. Sometimes there are reasons why people can't instantly fix their systems to be the ideal we want them to be.

    If the only answer I'm going to get is "upgrade to SSIS Catalog," please don't respond. You don't know our environment, you don't know the pressures we're currently under, you don't know our workload or procedures. I literally cannot pull the rug out from underneath our QA team right now or we blow our entire deadline and have to start testing the upgrade from scratch. That's months of work down the drain and we're already behind because of a ton of other things outside of our control. And yes, our QA team will not let us make this change without going back to square one. That's a GOOD THING.

    Regarding this issue, all I'm trying to do is understand one very strange thing so I can try to resolve it. I appreciate your perspectives. I do actually want to do SSIS Catalog. But I cannot right now because of multiple reasons. "A few minutes" doesn't mean the same thing to me as it does to you because of those reason, including the fact that those few minutes will cause a minimum of another several weeks of upgrade delay. I have to look at this change holistically, not as a one-off quick fix.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Packages reference binaries tied to specific versions. So it might be possible Jeffrey is onto something.  Imagine that the message is related not to actual package failure, but those references not being what the binaries expected to see.

    Since it appears to run fine, but generate an error that seems wrong, I’d think this needs to be noted but is not a show stopper. If packages were upgraded should be easy to answer. And needs to be known whether or not you move forward - because at some point it either eliminates the possibility, or becomes an avenue to test.

    You are pressured by time - also trying to understand and resolve something that is not what it appears. You are correct - I have no clues as to your time frame, nor what constitutes a change that is a show stopper. And no idea of the concept of why a few minutes results in weeks of delay. All I can do is suggest a simple check to verify - you can ignore or rant, I’m just passing on what I see. I can say that in the past, I have seen error messages that are not what they should be. And this would be my first thing to verify - that all packages were opened and upgraded before testing.

  • Brandie Tarvin wrote:

    I wasn't actually the one who pushed the SSIS packages to this particular server. I'm not sure if it was just imported straight from 2012 to 2019 or if it was upgraded. I need to ask my coworker. What I do know is that we have other file system tasks in other packages on the same server and none of them are having this particular issue. But I will check the MSDB tables and see if there's a clue there. Windows Event monitor as well, though I don't actually expect to see anything there given past experience with those logs. I've never seen this specific of an error show up on the Event Monitor or anything from an SSIS package even under the Application Log.

    If you cannot see the actual error generated by SSIS - how do you expect us to help?  I am not saying you must implement the catalog - but that would be the easiest method of capturing any errors.  I am recommending that you install the catalog now to prepare for the migration - or do as you have already done and kick the can down the road (which inevitable means you won't migrate - because there is *always* something else that will be more important).

    Regarding this issue, all I'm trying to do is understand one very strange thing so I can try to resolve it. I appreciate your perspectives. I do actually want to do SSIS Catalog. But I cannot right now because of multiple reasons. "A few minutes" doesn't mean the same thing to me as it does to you because of those reason, including the fact that those few minutes will cause a minimum of another several weeks of upgrade delay. I have to look at this change holistically, not as a one-off quick fix.

    Okay - you don't want a one-off quick fix...but that is exactly what you are asking us to provide, which we cannot even begin to help with because we have no way of identifying the problem.

     

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Brandy - you were asked to look at Windows Event Viewer and at msdb sysssislog  - do you have any of the messages logged there? if not can you change your job execution to add some logging - some can be done even if package does not have any log active at all.

     

    but both places mentioned will have SSIS messages - and some may contain what you need to help you.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply