How to integrate SSIS 2012 with SQL 2008R2 Database

Hello there,

I have 2 instance of database on my machine,SQLServer 2012 and SQLServer 2008R2,Please see screenshot. I will be using the 2008R2 database for data and SQL2012 for SSIS,SSRS implementation. Now my packages are ready for deployment. But I am stuck from here. While deploying I need to refer the Server name  to which instance of SQL.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Vikas GargAssociate Principal EngineerCommented:

In the destination you have to give the server name where you want to deploy the SSIS package.

Here you need to select SQL2012 Instance.
zolfAuthor Commented:
Thanks for your comments.
SO the package will be using sql2012 instance and what about the data. will it use 2008r2??. how will it know to get data from 2008r2 instance
zolfAuthor Commented:
by the way i dont see manifest file in the bin folder when i build the project. In 2012 we dont have this manifest file like we had in 2008R2
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Vikas GargAssociate Principal EngineerCommented:

We don't have manifest file in 2012 but we do have ispac file in the 2012 which is used for deployment.

The destination folder shown in above screenshot will be used to deploy the package to the destination.

The package you created will be deployed on 2012 but the connection string will be used which you have used inside the package connection manager.
zolfAuthor Commented:
Thanks for your feedbacks.
I now want to deploy the packages from one machine to another on the internet. How can I do that??
Vikas GargAssociate Principal EngineerCommented:
Two options.

1. paste the ispac file to destination machine and then deploy using deployment wizard.

2. deploy from the machine where ispac file is there
but you should have access to the machine where you want to deploy the package. and that connection will be used at the destination as above image
zolfAuthor Commented:
OK, I pasted the ispac file to the server and deployed it to the SSIS Catalog DB. But I have issue executing them,as the connection string are different on this machine. How do I edit these connection strings so they reflect correctly to this machine
zolfAuthor Commented:
here are two packages as shown in the screenshot which i need to edit their connection properties

Vikas GargAssociate Principal EngineerCommented:

right click on the SSIS Project - FDO Report

and select Configure.

There you will find tab of connection managers

You can change you connection there

Experts Exchange Solution brought to you by

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
zolfAuthor Commented:
See shot

Vikas GargAssociate Principal EngineerCommented:

You can not change that and its not required to change also
zolfAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.