[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

the automation of SSIS packge

Posted on 2014-04-04
19
Medium Priority
?
383 Views
Last Modified: 2016-02-10
Dear all,

anyone know how we can SCHEDULE the execution of SSIS package 2008 R2 or 2012 edition once it is deployed to the SQL server?  like in SQL server 2012, once deployed to the Integration Services Catalogs ?

SQL agents know how to schedule it and execute it everyday/every hours?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
  • 2
  • +1
19 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 150 total points
ID: 39978408
Correct - use SQL Agent and on the New Step select the appropriate type "SQL Server Integration Services Package" from the "Type" dropdown list and "SSIS Package Store" under the "Package source" drop down then point to the location on that server where the SSIS was imported.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39980967
so we only need to specify the package name as the execution target ?

how can we input the parameter ?
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39985766
If you are using 2012 you get to create parameters for your job. You can supply them statically at run-time when you set up the job. There's some info here.

If you have items that depend on things like day or time of day (like, maybe, filenames) you can set variables to include that information inside the SSIS code. Of course, if what your job does depends on what some other job has already done then communicate via data in tables.

hth

Mike
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 1

Author Comment

by:marrowyung
ID: 39985953
"If you have items that depend on things like day or time of day (like, maybe, filenames) you can set variables to include that information inside the SSIS code. Of course, if what your job does depends on what some other job has already done then communicate via data in tables"

so I can't pass parameter from the SQL agent job ?

but it seems can't from the link you send me ??
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39987069
What are you trying to achieve? Could you describe that and maybe I can show you how to make it happen ...

Mike
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39987837
pass diff parameter from the SQL agent job to run the package
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39987850
Image that on diff time each today,  I will run the SAME package but the setting (parameter) will be diff each time, how can I control it by inputing diff parameter?

I check the SQL job UI if I'd like to rescheulde a job:

when scheduling a SSIS job.
so I can type in diff parameter for each job I setup using the same SSIS package?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39987856
Also I see this from the SQL job Windows:

SSIS job
I don't create this job, should it created by the first time SSIS package deployment ?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 39990392
I will run the SAME package but the setting (parameter) will be diff each time, how can I control it by inputing diff parameter?
You cannot do this from SQL Server Agent.  You will have to find some other way.  If you provide more details as to how the parameter changes than we can provide a better answer.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39990412
" If you provide more details as to how the parameter changes than we can provide a better answer"

ok, then I can't explain what I saw from the screenshot I provide, I try to type something in the parameter and it allows, this mean ......?

I am here asking how to change it and I don't know how..
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39990672
You can't automatically stuff values into the parameters in SSIS - they're there for running the package with values that you might want to change sometimes. What you should do is have your parameters in a table and use an Execute SQL Task in Control Flow to read in the information and keep it in variables in the SSIS package.

What we're trying to find out is enough info from you about the values you want to give the job so that we can suggest the best way of solving your problem.

The "SQL Agent Job" is just instructions to the Agent to load a package and run it. The Agent doesn't know anything much about what it's running.

What I referred you to was a description of how to create the parameters inside the package, and how to get at them after the package has been deployed. If you type in a value for a parameter you have created in the package and do something with, then that value will stay constant until you change it.

It seems like you want to have different values every run, and have them supplied automatically. If that's the case then you'll either have to create them (things like the date and time) or get them from tables. An earlier job can leave info in a table that this job can read ...

hth

Mike
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39990708
"You can't automatically stuff values into the parameters in SSIS - they're there for running the package with values that you might want to change sometimes. What you should do is have your parameters in a table and use an Execute SQL Task in Control Flow to read in the information and keep it in variables in the SSIS package."

ok, but this can make thing highly complex ? why don't we just do it from job level ! that's why we schedule it over SQL job agent, right? is there any other method to schedule that.

in the table, what should be the column? you mean read the first row then execute the first one ? second row is the parameter for the 2nd runs parameter for the same package?  

When I am learning SSIS parameter, I think we can only change parameter when we are building package.

So we build package with diff parameter and name it differently? this is not the concept of code reuse, right?

"The "SQL Agent Job" is just instructions to the Agent to load a package and run it. The Agent doesn't know anything much about what it's running.

yes you are right and that's why try to look at the SQL job screen and see what we can do when scheduling a SSIS package.

then I see sth like what I post. The logic is, is that mean we can INPUT parameter for the Package from there?

"If you type in a value for a parameter you have created in the package and do something with, then that value will stay constant until you change it.
"
Agree, until I change it, and how? that's why I take a look on the SQL job schedule job for package and you can see that I found out section for me the input/change parameter. So this mean something .... ?

I just want to change parameter EASLIER.

"It seems like you want to have different values every run, and have them supplied automatically. "

I want to supply  them in SQL job level so that, I can see everything in one console.

"get them from tables. An earlier job can leave info in a table that this job can read ...
"

not quite understand why make it so complex, you are programmer background ?
0
 
LVL 16

Accepted Solution

by:
DcpKing earned 1200 total points
ID: 39993142
" [...] I take a look on the SQL job schedule job for package and you can see that I found out section for me the input/change parameter.

I just want to change parameter EASLIER."

That's the way Microsoft has provided for changing the parameter values.

Change one of the values and then look up at the top of the window: is the Script button enabled? If so then use it and you'll get the SQL script for doing the change you just made by hand.

To make it easier for you to do you can maybe write a little program in VB.net or C# to present you with an interface where you can enter the parameter values. Then it can run the code you get from scripting your change, and then start the job (there's a stored proc. to do that).

hth

Mike
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39993254
"That's the way Microsoft has provided for changing the parameter values."

UI is easlier, right? or you still prefer save the step done in table ?


"Change one of the values and then look up at the top of the window: is the Script button enabled?"

I just tried to change from parameter and it allow me to change. The script button is not enabled at all, but is this one what you mean  ? the script one I mean:

SSIS scheudle
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39993267
"If so then use it and you'll get the SQL script for doing the change you just made by hand."

you want me to do it by script instead of relies on the UI ?

"To make it easier for you to do you can maybe write a little program in VB.net or C# to present you with an interface where you can enter the parameter values"

why do one more thing ?really can't see why !!
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 39993288
I'm sorry; I don't understand. You're asking for the process to be made easier and then telling me that the interface you found is the easier way. What are you asking for?

Mike
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39993303
ok , make it easlier.

I talk about that because you said:

 "To make it easier for you to do you can maybe write a little program in VB.net or C# to present you with an interface where you can enter the parameter values. Then it can run the code you get from scripting your change, and then start the job (there's a stored proc. to do that)."

so I am wondering why we seems doing more and more and I feel this will only make the whole idea complex.

so the script button is grey and I can't script that out, is that mean I can't make it automatable ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39995548
so the script button is grey and I can't script that out, is that mean I can't make it automatable ?
I suggest you read up on this and how it works.  There is clearly a disconnect between what you think it does or might do and its intended purpose.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

650 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