Solved

the automation of SSIS packge

Posted on 2014-04-04
19
363 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
  • 10
  • 5
  • 2
  • +1
19 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
"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
Comment Utility
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
Comment Utility
pass diff parameter from the SQL agent job to run the package
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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
Comment Utility
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 50 total points
Comment Utility
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
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 1

Author Comment

by:marrowyung
Comment Utility
" 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
Comment Utility
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
Comment Utility
"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 400 total points
Comment Utility
" [...] 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
Comment Utility
"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
Comment Utility
"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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Lessons learned during ten years of interviewing for SQL Server Integration Services (SSIS) and other Extract-Transform-Load (ETL) contract roles and two years of staff manager interviewing contractors.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

743 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

10 Experts available now in Live!

Get 1:1 Help Now