Package doesn't run from SQL Server Agent (ConnString/Password Issue)
-
Hi Guys,
Im going to bring up a problem that has been shared by others before but it seems there's not standard solution for it.
It has to do with the PropertyLevel of the SSIS Packages and the the SQL Server Agent inability to easily run packages that have a Connection String configured.
Here's my scenario:
I'm connecting to Sap with MySap .net Data Provider.
When i open the ConnManager in the package and i put the values for the connManager (username, password,server, etc) and test the connection it works fine. After that, i go ahead and debug the Package it Works Fine.
Now, if i go ahead and open the ConnManager again i see there's no password there (it seem that's the way it should work).
If I go and add the package in SQL to the File System or MSDB and then schedule a Job from the SQL Server Agent to run this packages it doesn't run. IT Gives a DTSER_FAILURE (1)
I tried changing the Package ProtectionLevel to EncryptAllWithPassword or EncryptSensativeWithPassword and run it and it Fails.
I tried this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105100
and it doesnt work.
Actually the last solution gives me and error on the dts command line saying the dts arguments are not correct :
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /DTS
"\MSDB\YOURPACKAGEHERE" /SERVER ServerName /DECRYPT YOURPASSWORDHERE /MAXCONCURRENT
" -1 " /CHECKPOINTING OFF /REPORTING V
I also tried adding the password value directly to the connstring in the Package -that is adding the PSSWD="" parametre to the string and nothing there.
I've seen there's a suggestion about using xml for the connstring but couldnt find any example on how to do this.
I ran out of options and i have googled this for two days and still haven't found anything that works.
If somebody has a solution or a different approach to this problem it will be welcomed.
-
xml is definitely the way you wanna go.
To do this, go to SSIS\Package Configurations in the top menu.
Then you'll follow the prompts to create the file and choose the items you want in the config file.
I will be happy to make you a video on how to do this, but I wouldn't be able to post it until tomorrow. But let me know if you need it and I'll try to bang it out tonight.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
crever,
What objects do i need to save in order for this to work? and what am i supposed to do after that?
About the Video i believe it could be very helpful for some of us that arent so expert here, only if you have the time of course. BTW, saw some of your videos and they are great.
-
OK, tell ya what dude... I'll put that vid together tonight and send you the link in the morning. If I forget to send
you the link just remind me and I'll shoot it on over.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
hey,
Package Configurations will definitely resolve your problem.
Really there isnt much to it.
You need 1 variable telling SQL where your configurations reside. This must be an environment variable.
If you choose xml, it needs to tell SQL where the xml file resides, if you choose SQL server (my personal choice) it needs to tell SQL the connectionstring of your database
Readup on environment variables
You need 1 variable in your configurations containing the connectionstring (with userid and password).
For your immediate pains, SaveSensitiveWithPassword will save all sensitive information on the package (such as userid and password).
Once you have configurations going, DontSaveSensitive - everything will load at runtime in any way
Crever,
I think that video is a great idea man. A lot of people are asking the same kinda questions with package sensitivity and configurations (how to enable).
Would be nice if you can make one for xml and one for SQL
~PD
-
Ya definitely u should have resolved ur problem using indirect configuration via envi variable and xml config file. But dont ever forget to set ProtectionLevel property to 'dontSaveSensitive' otherwise the package would take credentials from the dtsx file and not the config file.
-
OK man, that video's ready for you...
This shows you exactly what you need to do to setup a conn string in
an xml file...
http://midnightdba.itbookworm.com/SSISConfigFileConnString/SSISConfigFileConnString.html
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Awesome
:D:D:D
I am bookmarking this posting and referring anyone with package configuration questions here.
Are you going to do one for SQL configs as well?
~PD
-
Oh, I suppose I could make my way through all the options in the package config menu... make it a little series on package configs or something... sure, I'll see what I can do.
Let me know what you think of the vid and if I left anything out. And be honest. I want to give you guys the vids you need.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
My problem is that working in South Africa we get attrocious bandwidth, and the other vid I tried didnt even open in my browser.
Will try again, but please dont shoot me if I cant open it
~PD
-
One trick to my vids is if you right-click on the control and view the properties, you can pull the filename and download it by hand. I'm trying to get a direct link put up on all the vid pages so you can download them all w/o having to watch them from the server. But that's a good workaround for now.
However, for now, here's the direct link to this file, and you can just right-click on this link and pick 'save target as'...
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
First i want to thank crever for the video which is great.
Second, im having a problem following the steps from the video.
After i create my xml file and go directly to the connstring.dtsConfig and open it to edit it in notepad so i can add the password, i saved the notepad and ran the package and for some reason the xml file creates again the old xml file without the password.
That is: i created the xml connstring file at 11:00, i go ahead and edit it at 11:05, save it, run it from SSIS and when it runs i go ahead and see that the xml connstring file says it was last modified at 11:00.
Therefore it fails and gives the login error (since the 11:00 xml doesnt have the password).
Am I missing something?
(The ProtectionLevel is set to DontSaveSensitive, but i tried with EcryptSensitiveWithPassword and got the same error)
-
I'm not entirely sure what to say to that. I've never seen that. The way I did it in the vid is exactly how I've done it for years. Perhaps you don't have permissions to alter the file? Maybe it's in a protected location on the drive?
And you definitely want to set the package to 'dont save sensitive'. But it sounds like you're doing what you're supposed to though.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Im noticing that when i update the xml file on notepad it is saved with the File Attribute "A".
If im not wrong that attribute is a flag that tells if a file was changed after the last backup.
What i believe is happening is that when i run the package it goes and verifies if the file has that attribute, if if does it restores the last backup.
Could any of you verify if your xml files are saved with this attribute when you modify them?
-
The only thing the A attribute does to a file is flag it so that windows can do a differential backup. It has nothing to do with any apps.
A friend of mine over here says he prefers Word to edit his xml files. And while this won't help with your A flag issue, it's a nicer editor.
Personally, I prefer either PrimalScript, or XMLSpy.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply