Solved

Dynamic database connection not working when run from a job

Posted on 2016-11-07
11
33 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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Can you post the code used in the job ?
0
 

Author Comment

by:amukta
Comment Utility
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
Comment Utility
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 13

Expert Comment

by:Megan Brooks
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Accepted Solution

by:
Vikas Garg earned 500 total points
Comment Utility
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
Comment Utility
Hi! LVL14,

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

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much! This solution works the best!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 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

8 Experts available now in Live!

Get 1:1 Help Now