Solved

Dynamic database connection not working when run from a job

Posted on 2016-11-07
11
44 Views
Last Modified: 2016-11-11
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
Comment
Question by:amukta
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41878070
Can you post the code used in the job ?
0
 

Author Comment

by:amukta
ID: 41878082
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
 

Author Comment

by:amukta
ID: 41878084
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
 
LVL 14

Expert Comment

by:Megan Brooks
ID: 41878133
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
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41878499
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 14

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 41878511
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
 

Author Comment

by:amukta
ID: 41879123
Hi! LVL14,

Do we need to set DelayValidation to true on the connection MANAGER properties?
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41879134
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
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41880007
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
 

Author Comment

by:amukta
ID: 41883748
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
 

Author Closing Comment

by:amukta
ID: 41883750
Thank you very much! This solution works the best!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package). The latter is certainly a headache for developers, particularly for me. We had to ma…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now