[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Dynamic database connection not working when run from a job

I had this question after viewing Dynamic Database connection in SSIS 2012.

Hi!

I'm using property expressions for servername and database name in the connection that get passed via environment variables. as Megan suggested in her post. However, when I run the package it works perfectly fine from VS 2012, but when I run via  SQL Agent/Control-M job, it does not do anything. The job says success, but none of the tables got loaded. I'm doing changing anything such as Integrated security and other properties.

A few suggested script task, but I'm really not aware of how that works as well.

It's a bit urgent!

Greatly Appreciate your help!

Best regards,
Amukta
0
amukta
Asked:
amukta
  • 5
  • 2
  • 2
  • +2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
Can you post the code used in the job ?
0
 
amuktaAuthor Commented:
I'm not using any code. I'll I'm doing is creating project Params for server name and database name and in the Connection Manager, I'm using property expressions for Servername property to point to the project param that has the Servername assigned to it and  Initial catalog property to point to the project param that has the  database name assigned to it. The servername and database name are hardcoded in the project params. The Servername and database name are passed via environment variables. They are hardcoded in the environment variables as well.


Thank you!
0
 
amuktaAuthor Commented:
I'm calling the SSIS Package in the job. I'm not using any other code. I'm not using any script task to override the connections.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Megan BrooksSQL Server ConsultantCommented:
Looking back at your earlier question, it appears that you have set up the project to use an SSIS 2012 environment. One other thing you can do with the SSIS 2012 project deployment model is to define project parameters and associate them with environment values. As I recall, there is a tab in the SQL Agent Step dialog for making these assignments.

Unfortunately, at the moment I don't have any SSIS jobs in my test environment that I could look at to remember all the details. But it sounds like you have already gone through the work of setting up environments, and all you need to do is add a Servername project parameter and assign it an environment value. You can then reference the parameter much like you would a package variable; it's just a slightly different syntax and the expression editor can provide it for you.

Alternately, if you can script the SQL Agent job and attach it here as a file, maybe we can figure out why the SET isn't working.

Also, have you checked the SQL Agent job history for details? If you are executing from the SSIS Catalog (and it sounds like you are) then you may find something in the execution log as well.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
When you specify the SSIS package in the job step you have the Configuration tab where you can provide the new connection info (instance name, database and user). The SQL job will use these new info in the place of the ones that are stored in the ssis package.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,

You can go like this ..

1 Create connection Manager with fix values.

2. Right click on the connection manager and select properties.

3. Copy the text which is there in connectionstring property

4. Click on expression - and in property select connectionString

5. in expression paste the value which you copies from previous connection property inside " " mark.

6. Now remove the hardcoded server name and drag variable from the variable and parameters tab

>> Use + symbol for joining the string with the variable.

click on evaluate expression if variable has right value it will go fine.
1
 
amuktaAuthor Commented:
Hi! LVL14,

Do we need to set DelayValidation to true on the connection MANAGER properties?
0
 
Pawan KumarDatabase ExpertCommented:
Yes  since we are validating things later. When we set this property to True, We are enforcing our SSIS Package not to validate that Task, Connection Manager or entire Package at start but validate at run time.

Hope it helps !!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do we need to set DelayValidation to true on the connection MANAGER properties?
Aren't the files already there? If so then no need to delay validations. This is only if you need to create the files during the process so SSIS will avoid to check for their existence.
1
 
amuktaAuthor Commented:
It is working without the delay validation set to true. I delayvalidation has the default value-"false". It's working!

Thank you all! Greatly Appreciate your help!
0
 
amuktaAuthor Commented:
Thank you very much! This solution works the best!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now