October 4, 2017 at 9:20 am
Here's what I'm not understanding. I setup both of these SSIS package a few weeks ago and they both ran fine using debugging ie could see all the green checkboxes and then the files being copied, updated etc. Take out the permission errors with the Windows folders, why all of a sudden can VS no longer run packages after I've typed in a SQL User and Password? Why do these exact same packages run fine with a SQL Agent job where I have setup using the same SQL User ID and password? This doesn't make any sense. If I can no longer test SSIS packages with VS, what is the point of debugging? The only way I can test now is to keep running the SQL Agent job manually, which doesn't help debug anything.
October 4, 2017 at 11:13 am
Luv SQL - Wednesday, October 4, 2017 9:20 AMHere's what I'm not understanding. I setup both of these SSIS package a few weeks ago and they both ran fine using debugging ie could see all the green checkboxes and then the files being copied, updated etc. Take out the permission errors with the Windows folders, why all of a sudden can VS no longer run packages after I've typed in a SQL User and Password? Why do these exact same packages run fine with a SQL Agent job where I have setup using the same SQL User ID and password? This doesn't make any sense. If I can no longer test SSIS packages with VS, what is the point of debugging? The only way I can test now is to keep running the SQL Agent job manually, which doesn't help debug anything.
Just an fyi, but VS is kinda funky at times. You can run just fine and then run into strange errors, and the only way to solve the problem is either close out and restart VS or sometimes, even re-boot.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 11:39 am
If your password parameter in the IDE is marked as sensitive and you have the security set to do not save sensitive then to be able to debug the package you will need to change the parameter to not sensitive and then change it back once you have finished debugging. If you have just opened the package and the parameter is set to sensitive then there will be no value in the parameter. You will have to re-enter it.
Once deployed how are you passing the sensitive parameter to the package?
October 5, 2017 at 8:48 am
I don't have a password parameter. I'm passing the password by editing the datasource in SQL Agent and entering Password= after UserID= so the password is stored. The debug was working fine 2 weeks ago with the exact packages as they were and now they don't work. How I am able to select tables/views with a SQL login IN VS by entering a SQL User ID and password, but the debug does not use it? One interesting change I noticed is that the packages didn't use to open the SQL datasource in offline mode (ie when I open them now there is a red x, I have to right-click and uncheck Offline, then re-enter the password). That was not like that a few weeks ago.
October 5, 2017 at 9:13 am
Luv SQL - Thursday, October 5, 2017 8:48 AMI don't have a password parameter. I'm passing the password by editing the datasource in SQL Agent and entering Password= after UserID= so the password is stored. The debug was working fine 2 weeks ago with the exact packages as they were and now they don't work. How I am able to select tables/views with a SQL login IN VS by entering a SQL User ID and password, but the debug does not use it? One interesting change I noticed is that the packages didn't use to open the SQL datasource in offline mode (ie when I open them now there is a red x, I have to right-click and uncheck Offline, then re-enter the password). That was not like that a few weeks ago.
This is why MS created the ability to have a configuration database and have SSIS packages read from it to "self configure", so that things like passwords were stored securely in a separate database. Usually, you end up storing the connecting string for any given data source, and that's a much better way to operate than trying to set Don'tSaveSensitive and NOT using a configuration database.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 6, 2017 at 2:38 am
I have had similar happen to me. As there are multiple developers we always use don't save sensitive, in which case it always comes down to passwords. I always turn off the sensitive flag during debugging and re-enter the password.
Even in past when I have used Encrypt with User Key these types of problem occur. Another reason why I no longer use it.
I recommend setting up parameters and environments in Visual Studio and using the Project Deployment model. It's so flexible and the execution reports in the catalogue make debugging in live easier.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply