Solved

Dynamic database connection not working when run from a job

Posted on 2016-11-07
11
69 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 28

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 48

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
 
LVL 15

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 28

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 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 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