June 25, 2008 at 11:02 am
Well Guys, Im back with good news for me.
I closed my BIDS and then opened it again, and created again the xml file and voila, it worked.
Im going to see if the schedule Job works now in the sql server agent.I'll post back with the result
October 9, 2008 at 9:32 am
Good video tutorial. However, I work in an environment where I am not allowed to have a password stored as cleartext in a file. So, do you have any suggestion for this? In reading all of the MSDN topics, I thought I could address this when deploying to SQL Agent by using EncryptSensitiveWithPassword or EncryptAllWithPassword, and when I configure the SQL Agent step, it asks me for the password. However, when the package executes, it fails with a login failure. It works fine when running from BIDS.
Any ideas?
November 19, 2008 at 2:32 pm
Can we use one xml config file for multiple packages? If yes, how are we going to store the different connection strings in one file and how to idenetify that which connection string will be used by which package?
Also, If we created one xml config file with connection strings for one package then how and where do we specify that the other package can use the same file and how to add 2nd package connection strings?
Any help is appreciated.
Thanks
November 19, 2008 at 9:44 pm
OK, I didn't get any responses to my question, but I figured it out myself. If you want to use package encryption and run the package on another server using the SQL Agent login (as a step in a SQL Agent job), then after you create the step in SQL Agent, you Edit the step and open the tab that says "Command Line", which will display the command line statement and arguments that will be used to execute the package. You will see (if your package was saved with "EncryptAllWithPassword") a /ENCRYPT option in the command line. If you edit the command line and add your package password after the /ENCRYPT argument and then SAVE the step, it will save the password (but not display it) in command statement that gets executed, and the package runs successfully.
You can do this instead of storing passwords in an XML configuration file. Just another approach.
November 21, 2008 at 2:06 pm
dekhle (11/19/2008)
Can we use one xml config file for multiple packages? If yes, how are we going to store the different connection strings in one file and how to idenetify that which connection string will be used by which package?Also, If we created one xml config file with connection strings for one package then how and where do we specify that the other package can use the same file and how to add 2nd package connection strings?
Any help is appreciated.
Thanks
You can use a single XML config file for multiple packages, but you might want to set the SuppressConfigurationWarnings property to True, to keep from getting warnings if a package points to a config file which attempts to set values that it doesn't need.
Each XML config variable, in addition to the value, will store the XML metadata pertaining to the variable it is configuring. This will keep them separate for you. For example, if I configure a connection string, the XML actually stores this:
ConfiguredType="Property" Path="\Package.Variables[User::cm_OLE].Properties[Value]" ValueType="String" Data Source=DEV;Initial Catalog=Some_Schema;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
As for their creation, when I am developing packages in BIDS, I have a master configuration package that I include in the solution, define all of the connection managers for all of my other packages, then copy and paste them into their actual locations. From this master package, I generate the XML config file that contains all of the variables. Then I detach the master config before building. To get the resulting XML, I open the config files using XML Notepad 2007 (available free from MS), and copy the configs out of the master file into the actual package config file.
Clear as mud? :ermm: I guess it's actually easier to do than to describe.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply