January 9, 2020 at 5:44 pm
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?
January 9, 2020 at 5:47 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 9, 2020 at 6:14 pm
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.
January 9, 2020 at 7:18 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 9, 2020 at 9:57 pm
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?
January 9, 2020 at 10:08 pm
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).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2020 at 10:01 pm
This was removed by the editor as SPAM
January 12, 2020 at 12:18 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 14, 2020 at 10:57 pm
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.
January 14, 2020 at 11:28 pm
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