[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

the automation of SSIS packge

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
marrowyung
Asked:
marrowyung
  • 10
  • 5
  • 2
  • +1
3 Solutions
 
lcohanDatabase AnalystCommented:
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
 
marrowyungAuthor Commented:
so we only need to specify the package name as the execution target ?

how can we input the parameter ?
0
 
DcpKingCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
marrowyungAuthor Commented:
"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
 
DcpKingCommented:
What are you trying to achieve? Could you describe that and maybe I can show you how to make it happen ...

Mike
0
 
marrowyungAuthor Commented:
pass diff parameter from the SQL agent job to run the package
0
 
marrowyungAuthor Commented:
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
 
marrowyungAuthor Commented:
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
 
Anthony PerkinsCommented:
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
 
marrowyungAuthor Commented:
" 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
 
DcpKingCommented:
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
 
marrowyungAuthor Commented:
"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
 
DcpKingCommented:
" [...] 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
 
marrowyungAuthor Commented:
"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
 
marrowyungAuthor Commented:
"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
 
DcpKingCommented:
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
 
marrowyungAuthor Commented:
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
 
Anthony PerkinsCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 10
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now