Instances of 2008R2 and up to 2016

Question

Instances of 2008R2 and up to 2016

Adam Sandler

SSCrazy

Points: 2697
More actions
March 29, 2017 at 5:25 am

#342077

I have a database on isntances of 2008R2 and upwards.
I would like to produce some SISS packages that would hopefully run on all these versions.
What development tool must I use ( a specific version?) - ie i have SSDT 2015 installed. And what deployment options do I have - I envisage they will all be run via scheduled SQl Agent jobs?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

The database to which you connect from a package isn't important. All that you need to make sure is that the network library you use to connect to SQL Server will connect to each database server version. And, of course, the T-SQL that you execute needs to be compatible with all versions. The choice of development tool is more likely to be dictated by the version of SSIS on which the package will run.

In terms of deployment, what I usually do is schedule the package using SQL Server Agent on the same server that SSIS is running on.

John

Adam Sandler SSCrazy Points: 2697 More actions · Answer 2

Thanks John

This will be an idiots question , wont the SISS version be the same as the version hsoting the db. ie I'm limited to whats available at each site. Wont I just be adding a SISS component alongside where the db to be integrated with is?

Paul

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Paul

Yes, if you're going to deploy each individual package on the server that hosts the database it connects to. But you might consider instead putting all the packages on one server for ease of development and administration.

John

Adam Sandler SSCrazy Points: 2697 More actions · Answer 4

John Mitchell-245523 - Wednesday, March 29, 2017 7:04 AM
Paul
Yes, if you're going to deploy each individual package on the server that hosts the database it connects to. But you might consider instead putting all the packages on one server for ease of development and administration.
John

Thanks John - these are remote servers, so I'm guessing my only real choice is to go with whatever version they have and deploy it on the remote instances? And so how would be best to deploy to muitli version remote instances?

Should I be looking to deploy them all as if the version was 2008 to keep them all the same.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

You mean remote in that none of them communicate with any of the others? In that case, yes, you'll need to deploy individually. You can either develop a package for each version, or write the package once on the lowest version. You'll lose out on new features if you do the latter. You can automate the deployments with PowerShell or your favourite other scripting language. These are the steps you'll need to script:
(1) Create the package on the remote server, either by copying the .dtsx and .dtsConfig files, or by adding it to the msdb/SSISDB database
(2) Create and schedule a job to run the package

John

Adam Sandler SSCrazy Points: 2697 More actions · Answer 6

Thanks John - I'll give it a go - the first few are very instance specific - so I'll set them up first, and then see what the best solution si for future deployment to other sites. Thanks

I may come back to tyhirs thread - if that is OK.

Thankyou for your help.

If they were all using 2012 enterprise - it would be so much easier!