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?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
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
 
marrowyungSenior Technical architecture (Data)Author 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author Commented:
pass diff parameter from the SQL agent job to run the package
0
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
 
marrowyungSenior Technical architecture (Data)Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.