SSIS Logging variant

  • I have a development server and a production server, when I run the SSIS Package on the Dev server, it reads the file correctly, logs correctly as seen below:

    #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

    PackageStart,CCS-SQL-DBA,CCS\AllenN,TransferHistory_SCC,{B13A3493-4BEB-4A7C-97EA-9D9D980C7838},{AC51C0C2-840A-42C5-8B63-F901E359E052},1/9/2020 8:26:43 AM,1/9/2020 8:26:43 AM,0,0x,Beginning of package execution.

    User:ScriptTaskLogEntry,CCS-SQL-DBA,CCS\AllenN,See if the file exists,{8DF6028E-CE32-4765-9BF2-01AAF864C5B2},{AC51C0C2-840A-42C5-8B63-F901E359E052},1/9/2020 8:26:44 AM,1/9/2020 8:26:44 AM,0,0x,FileExists: True
    User:ScriptTaskLogEntry,CCS-SQL-DBA,CCS\AllenN,See if the file exists,{8DF6028E-CE32-4765-9BF2-01AAF864C5B2},{AC51C0C2-840A-42C5-8B63-F901E359E052},1/9/2020 8:26:44 AM,1/9/2020 8:26:44 AM,0,0x,FilePathAndName: \\CCS-SQL-IR\FileDrop\Transfers\scc.txt
    PackageEnd,CCS-SQL-DBA,CCS\AllenN,TransferHistory_SCC,{B13A3493-4BEB-4A7C-97EA-9D9D980C7838},{AC51C0C2-840A-42C5-8B63-F901E359E052},1/9/2020 8:27:55 AM,1/9/2020 8:27:55 AM,0,0x,End of package execution.

    EB19-4FE6-B901-BBA7E1D6B249},1/9/2020 9:14:20 AM,1/9/2020 9:14:20 AM,0,0x,End of package execution.
    (not all of log)

    Once installed and ran from the Agent (which has permissions to the file location, and is on a local drive (E:), generates the following output:

    #Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

    PackageStart,CCS-SQL-STAGE,CCS\SQL-Stage-Agent,TransferHistory_SCC,{B13A3493-4BEB-4A7C-97EA-9D9D980C7838},{B1B81A80-DFB1-41F5-9A2D-C8918E8E3238},1/9/2020 9:21:12 AM,1/9/2020 9:21:12 AM,0,0x,Beginning of package execution.

    PackageEnd,CCS-SQL-STAGE,CCS\SQL-Stage-Agent,TransferHistory_SCC,{B13A3493-4BEB-4A7C-97EA-9D9D980C7838},{B1B81A80-DFB1-41F5-9A2D-C8918E8E3238},1/9/2020 9:21:12 AM,1/9/2020 9:21:12 AM,0,0x,End of package execution.

    (not all of log)

    which you can see isn't showing the variables.   I'm not quite understanding why this is going on.   This suggest permissions, from the Agent vs. the AllenN permissions, but i've check the folder and the Agent as well as the owner have permissions to modify.

    There is no error, it just doesn't seem to see the file.   Thoughts on things to check?

  • How is that logging information being created? It looks non-standard.

    Have you checked the All Executions logs to see whether there is any additional information there?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • script task, using Dts.log

                Dts.Log("FileExists: " + Dts.Variables["User::FileExistsResult"].Value.ToString(), 0, emptyBytes);
    Dts.Log("FilePathAndName: " + Dts.Variables["User::FilePathAndName"].Value.ToString(), 0, emptyBytes);

    This isn't using the Catalog, just installed to server (not file system), so i'm not sure if the all executions report is available.

  • It's difficult to troubleshoot this further without knowing more. I recommend that you do start using SSISDB - the 'free' logging that comes with doing this is enough on its own to justify its use.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Thanks for the recommendation.   I'll see if i can use it temporarily, as it's not something that is just 'available' here, and I don't get to set the environment rules.   Any other locations i can check?

    My main questions were:

    A) why the variation in the logging?  I would guess this has something to do with permissions, however, logging is occurring, as shown, just variants per instance, which shouldn't be happening.    Why does the Dts.Log seemingly work in one place and not in the other (with no error)?

    B) from the package it runs correctly, succeeding and processing the file, from the server, it succeeds, but never actually finds the file, so isn't processed, seemingly returning a false to the File.FileExists check.  Is there another solid location that i can check for such information?

  • A) I've been using SSISDB logging for years now, so can't help you with that.

    B) Not that I know of. You could add more debugging collection to the package itself (eg, writing log messages to a database table somewhere).

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • This was removed by the editor as SPAM

  • MARCOCH wrote:

    Once installed and ran from the Agent (which has permissions to the file location, and is on a local drive (E:), generates the following output:

    I see no output.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I never did figure out why it would work in one place and then not installed on the server.   I'm still thinking there is potentially a permissions issue.   I did find an alternate route, I was successful using an Execute Process Task, and creating a simple Powershell script to check, and setting the SSIS variable, which then was used successfully by the SSIS package.

  • richard.noordam wrote:

    I never did figure out why it would work in one place and then not installed on the server.   I'm still thinking there is potentially a permissions issue.   I did find an alternate route, I was successful using an Execute Process Task, and creating a simple Powershell script to check, and setting the SSIS variable, which then was used successfully by the SSIS package.

    you didn't tell us that your SSIS expected a outside variable to be set in order to produce your required output - your issue is that you most likely aren't setting it up as it should be on the SQL Agent step so if you tell us what you are trying to do as well as how you did setup the step that executed the package maybe we can help you with it.

Viewing 10 posts - 1 through 9 (of 9 total)

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