Folks, How do I remotely use 2012 Runtime client to run my SSIS dtsx package developed in BIDS 2010 and connect it to my production SQL Server 2008 r2?

Let's get things sorted out first: * 2012 runtime client? What exactly do you mean with that? * there's no BIDS 2010. There's BIDS 2005 and BIDS 2008 who use Visual Studio 2005 and 2008 respectively. You have SSDT 2012 (SQL Server Data Tools, which incorporates BIDS) which uses Visual Studio 2010. On which server are you trying to run the packages? 2008R2 or 2012?MCTS, MCITP - Please mark posts as answered where appropriate.

Thanks for the reply. I do apologize for the hatcheting of the SQL Server Lingo, i feel the same way when people 'talk' to me about Oracle. Firstly, I stand corrected, when I say BIDS -- i really mean Visual Studio 2010. In the past, they both seemingly shared the same executable. Basically, I have developed my SSIS packages in Visual Studio 2010 and it connects to and runs off of SQL Server 2012 in my TEST environment. My PRODUCTION environment is unfortunately still at SQL Server 2008 R2. I am unable to get my DTSX packages imported into my PRODUCTION environment's Integration Services, MSDB. I did some research and was informed that the DTSX package can be assimilated onto a remote server harbouring SQL Server 2012 libraries and then, using a SQL Server job off of my production environment, I can connect to the remotely stored DTSX package and execute that same DTSX package against my PRODUCTION environment. I hope that this clears it up. Phill

BIDS is a set of templates used within Visual Studio to develop BI solutions. If you don't have VS installed, BIDS installs a shell of VS so you can still develop your BI solutions. Anyway, SSIS packages developed in SQL Server 2012 cannot run on a SQL Server 2008R2 machine. You need to develop those packages using the BIDS 2008 environment. If you don't have VS 2008 installed, you can simply install the BIDS 2008 shell from the SQL Server installation media. You can however run the packages on a SQL Server 2012 server and connect inside those packages to the SQL Server 2008R2 machine (read and write data). You just cannot run those packages directly on the 2008R2 server.MCTS, MCITP - Please mark posts as answered where appropriate.

You can however run the packages on a SQL Server 2012 server and connect inside those packages to the SQL Server 2008R2 machine (read and write data). You just cannot run those packages directly on the 2008R2 server. Thanks for the response. I believe the quoted text above captures what I am interested in doing. Basically, i have articulated what my current environments look like. I don't want to downgrade any of my DTSX SSIS packages that have been developed against SQL Server 2012 using Visual Studio 2010. Basically, in order to leverage my environment, I can do the following: 1) Store the packages on my DEVELOPMENT environment which has Execute SQL and Data Flow tasks that connect to PRODUCTION. 2) Create a Database Job from my PRODUCTION environment that points to that package. Thanks, Phill

Phil, Since your production environment is SQL Server 2008 R2 you will not be able to execute SSIS 2012 version packages utilizing this environment.Arthur My Blog

2) Create a Database Job from my PRODUCTION environment that points to that package. Unfortunately, no. Since the job is executed on the SQL Server 2008R2 environment, the packages themselves are copied to this environment and they cannot run there. You'd need to schedule the Agent job on the 2012 environment. There's a difference between storing and executing the packages. The place of execution is not necessarily the same as storage location.MCTS, MCITP - Please mark posts as answered where appropriate.

You can however run the packages on a SQL Server 2012 server and connect inside those packages to the SQL Server 2008R2 machine (read and write data). You just cannot run those packages directly on the 2008R2 server. Thanks for the response. I believe the quoted text above captures what I am interested in doing. Basically, i have articulated what my current environments look like. I don't want to downgrade any of my DTSX SSIS packages that have been developed against SQL Server 2012 using Visual Studio 2010. Basically, in order to leverage my environment, I can do the following: 1) Store the packages on my DEVELOPMENT environment which has Execute SQL and Data Flow tasks that connect to PRODUCTION. 2) Create a Database Job from my PRODUCTION environment that points to that package. Thanks, Phill Koen is right. A package hosted on remote machine doesn't mean when you execute it, it will use the environment of the remote machine to do the execution. The remote machine is simply storage device, that's it! However there might be a way to accomplish what you are trying to do. I would recommend you research PsExec tool, which allows execution on remote systems. Report back how your experiment goes.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

PsExec will require some OS level permissions. I would advocate to create SQL Agent job on SQL Server 2012 and then start this job from 2008 R2 machine using Linked Server or Service Broker.

I actually did this prior to seeing your message and it worked... I loaded dtsx into the MSDB on the 2012 server, then i created an unscheduled job on the 2012 server and then on the 2008R2 server, i created another scheduled job which calls the 2012 server's job. Thanks folks! All about solutions... The words "can't" and "won't" are temporary hiccups! :-) Cheers!

Is it possible to do the opposite? Run a package developed in 2008 R2 on SQL Server 2012 Agent? Thanks

Is it possible to do the opposite? Run a package developed in 2008 R2 on SQL Server 2012 Agent? Thanks Yes, but the package will be upgraded to a 2012 version before it is run, which can give issues of course.MCTS, MCITP - Please mark posts as answered where appropriate.