September 25, 2012 at 10:02 am
Hi all,
I have 100s of SSIS packages stored in (msdb of) SQL server 2008, and they are all mapped to config files stored in file system on the same server. I want to run these SSIS on 2008 box from 2012 box.
Any suggestions to achieve that? I tried #1 below, it dint work. I am going to #2 but wanted to check if there is a better solution.
1. I created a job on 2012, and a step in the job calls a SSIS from SQLserver 2008, but the execution failed with following error
Description: The attempted operation is not supported with this database version.
2. My next approach is to create a job in 2012 box, that calls an SSIS package creates in 2012 BIDS to kick off a job on Sqlserver 2008 that runs the SSIS on the local server (2008). I will use the same package to run multiple jobs ON 2008 by passing the job name in config file.
Any better ideas?
Thanks,
Kayal
September 25, 2012 at 11:40 am
I haven't used it so am not sure it fills all your requirements but it sounds like using a Multi-server Agent Job may work for you:
Automated Administration Across an Enterprise
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 25, 2012 at 1:41 pm
When performing an upgrade, one of the general ideas that underlies doing so is that you'll have to upgrade all the internal components to the new design. Now, I understand you're looking at a huge number of objects to do this for, but the principal remains the same.
To get those packages to work in 2012 you'll have to 'touch' the packages with VS 2010 to upgrade them, resave, and then redeploy to the 2012 server. It's the exact same pattern for going from SQL 2k5 to SQL 2k8. That's why you got the first error.
However, as a workaround you can directly use the SQL 2k8 DTSExec tool by directly pathing to it via the job scheduler on your 2012 box (make sure 2k8 was installed as well so you have the correct version of DTSExec installed). For more information about DTSExec: http://msdn.microsoft.com/en-us/library/ms162810(v=sql.105).aspx.
I personally would recommend initially setting up the DTSExec method and start upgrading the SSIS packages as time permits, but don't let it stop being a priority.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 26, 2012 at 12:28 pm
Thanks Craig!! That sounds like a better idea.
Thanks opc!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply