SSIS Logging variant

  • richard.noordam

    SSCrazy

    Points: 2688

    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?

  • Phil Parkin

    SSC Guru

    Points: 243910

    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 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.

  • richard.noordam

    SSCrazy

    Points: 2688

    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.

  • Phil Parkin

    SSC Guru

    Points: 243910

    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 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.

  • richard.noordam

    SSCrazy

    Points: 2688

    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?

  • Phil Parkin

    SSC Guru

    Points: 243910

    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 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.

  • MARCOCH

    Grasshopper

    Points: 12

    Once installed and r https://wordunscrambler.onl/an from the Agent  https://autoclicker.pro/ (which has  https://jumblesolver.pro/permissions to the file location, and is on a local drive (E:), generates the following output:

    • This reply was modified 6 hours, 16 minutes ago by  MARCOCH.
    • This reply was modified 6 hours, 15 minutes ago by  MARCOCH.
  • Phil Parkin

    SSC Guru

    Points: 243910

    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 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.

  • richard.noordam

    SSCrazy

    Points: 2688

    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.

  • frederico_fonseca

    SSChampion

    Points: 14297

    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 10 (of 10 total)

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