Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic database connection not working when run from a job

Posted on 2016-11-07
11
Medium Priority
?
180 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 35

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 16

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 52

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 2000 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 35

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 52

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

877 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