Instances of 2008R2 and up to 2016

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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

  • 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!

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

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