September 25, 2017 at 1:45 pm
I have an SSIS package that was executing successfully for some time but last week started erroring. The error on the SQL Agent Job was "Access to the path is denied." Nothing has changed to any folder permissions. I opened the package then tried to debug it and got the message "There were build errors. Would you like to continue and run the latest build" to which I said No. Through checking a few other things in SSIS there was an issue with the project and package not having the same protection level, so I fixed that, saved it, then was able to run it without that message anywhere.
I can run the package in SQL Agent and SSIS without any errors, however, it's not doing anything. There is a Foreach loop that checks a folder, grabs any file with a particular string pattern (New_GL_Summary*.csv) then imports those into a SQL table, then moves those csv files into an Archive folder. There no errors so it's like it doesn't even see these files at all in SSIS anymore. I've tried changing the file properties but it still doesn't do anything.
Is there a way to show a degug in detail to show each step, results of each task etc, so I can see if it's actually reading those files? The only thing I see is that it started and ended successfully.
There is a third line "The program '[11936] DtsDebugHost.exe: DTS' has exited with code 0 (0x0)" but don't think this is related to the actual tasks.
September 25, 2017 at 2:18 pm
I have taken the csv files that SSIS is supposed to import and manually imported into SQL. I have a second SSIS pcakge that takes this data, manipulates it, then exports it to a folder on the same server as the one that is failing (different folder but main top-level folder) and this one works fine ie it exports data to a csv file. I can run this via SQL Agent, but not via SSIS debug as it keeps giving me a login failed even though I've typed in the password and tested connection many times.
September 26, 2017 at 9:05 am
Some things to try, make sure the For Each Loop is setting the file name in your variable correctly. Also ensure that the file name parameter is being used by the Flat File Connection Manager.
September 26, 2017 at 9:50 am
I have not modified the variables or filenames or folder locations since it was last working last week. The file name is setup in the For Loop and now with the variable since my for loop is setup to pull any file in the folder (could be 2-3 csv files there). My variable CurrentFile is set to index 0 in the for loop. As for the Flat File connection manager, it is not set to pull the file name since the file name changes as per the for loop (ie New_GL_Summary*.csv). This was working fine a week ago and has not been changed.
I don't know as to what step is not actually working as it's not logging any details in the log ie any of the steps, no results with record numbers etc so I have no idea where it's failing, as it says success.
September 26, 2017 at 10:04 am
The debug no longer shows checkmarks either. Progress tab only shows:
Validation has started
Validation is completed
Start, 11:57:18 AM
Finished, 11:57:18 AM, Elapsed time: 00:00:00.141
WHy is it not showing each step?
September 26, 2017 at 10:39 am
If nothing inside the For Each File container is logging any progress, the most likely explanation is that it isn't finding any files.
You can get more details by adding a script task (inside the For Each FIle container) with calls to either Dts.FireInformation or Dts.Log. You can pass the file variable into the script task as read-only and generate a message.
Your logging setup must include Information events. Dts.Log is simpler to use, but it requires that you turn on the ScriptTaskLogEvent for each script task.
September 26, 2017 at 12:23 pm
If I use the actual filename in the folder of the foreach loop, it still does not do anything. Even if I manually select one of the csv files in the flat file source it does not work. I have another SSIS package that exports data to this same server and it works fine. I don't understand how a package that I've not touched can just stop working. I will have to manually recreate it and test at each step to find out why this no longer works.
September 26, 2017 at 12:59 pm
I've recreated the package and am now at least getting some debugging info. I am testing this inside SSIS. I have typed in the SQL user ID and password and test connection (thus was able to select the table and link it in dataflow) yet it will NOT run. The errors showing in SQL Server's error logs is that the password does not match, yet it clearly does as I was able to browse the table list and see the tables. I have set both the project and the package's protection level to "donotsavesensitive" as my other one is setup that way.
[OLE DB Destination [119]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "SERVERNAME.USERID" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
With just the data flow I can run this successfully so it's not a sql issue. Wow SSIS is picky and buggy.
September 26, 2017 at 1:21 pm
When I run this package from within SQL Server Agent Jobs, I get "Description: An error occurred with the following error message: "Access to the path is denied."
Since I can't run this in SSIS to see the details in debug, I have no idea why this is failing since I setup the path in SSIS by BROWSING it so why can't it access it from within SQL?
September 26, 2017 at 1:37 pm
Luv SQL - Tuesday, September 26, 2017 1:21 PMWhen I run this package from within SQL Server Agent Jobs, I get "Description: An error occurred with the following error message: "Access to the path is denied."Since I can't run this in SSIS to see the details in debug, I have no idea why this is failing since I setup the path in SSIS by BROWSING it so why can't it access it from within SQL?
Is the package deployed to SSISDB using the project deployment model? If so, you have more options with regard to logging.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 26, 2017 at 1:38 pm
Luv SQL - Tuesday, September 26, 2017 12:59 PMI've recreated the package and am now at least getting some debugging info. I am testing this inside SSIS. I have typed in the SQL user ID and password and test connection (thus was able to select the table and link it in dataflow) yet it will NOT run. The errors showing in SQL Server's error logs is that the password does not match, yet it clearly does as I was able to browse the table list and see the tables. I have set both the project and the package's protection level to "donotsavesensitive" as my other one is setup that way.[OLE DB Destination [119]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "SERVERNAME.USERID" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.With just the data flow I can run this successfully so it's not a sql issue. Wow SSIS is picky and buggy.
As you have DontSaveSensitive turned on, how are you passing the password to the package at runtime?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 26, 2017 at 1:46 pm
The package is deployed to a file and not the server. The area it's failing on because of the error descriptions is the File System Task which takes the files it just imported and moved it into another folder. I've checked the permissions on that folder and network service, which is used to run SQL Agent, has modify and write permissions.
The integration is grabbing the first csv file and importing into sql, but not the second, so it's not looping either. Without it running within SSIS I cannot verify anything.
September 26, 2017 at 1:48 pm
This has been working for a few weeks successfully so I don't understand why it would just stop working. I used this https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/ to create the integration which was working great until something unknown killed it.
September 26, 2017 at 2:10 pm
Luv SQL - Tuesday, September 26, 2017 1:48 PMThis has been working for a few weeks successfully so I don't understand why it would just stop working. I used this https://www.mssqltips.com/sqlservertip/2874/loop-through-flat-files-in-sql-server-integration-services/ to create the integration which was working great until something unknown killed it.
This has happened to us all. There is always a reason, but sometimes it's very difficult to track down. For future reference, if you can make the switch to SSISDB, you get detailed logging built in by default.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 28, 2017 at 8:45 am
Based on what I've read so far, it sounds like the execution context has actually changed, and it may have been one of those things someone did but is not willing to admit. Maybe the execution context is no longer the Network Service, as that's not usually a good idea from a security perspective. The package should probably be using an SSIS Configuration database to get the user id and password for any needed connectivity to SQL Server. Lacking that, you'd be stuck unable to use "Don'tSaveSensitive".
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply