Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deploy package to multiple servers Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 5:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:47 AM
Points: 7, Visits: 18
Hi,

we currently have several servers running SSIS packages building tables for a data warehouse. These packages were created once and deployed to each server being extended server by server. During optimization process we want to unify the packages, so we have to maintain and upload only one package.
First we wanted to copy the T-SQL into tables that are copied from one source to all servers over night and execute the SQL in a SP. But this requires to re-create most of the package content (as there exist some dataflow tasks) having the column limitation (i.e. nvarchar(max)) in mind.
The best way would be some kind of broadcast to all servers when saving / deploying a package.
Any ideas?

thanks in advance
Martin
Post #1537261
Posted Monday, February 3, 2014 6:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 641, Visits: 2,149
I might be missing something, but is there a reason you can't deploy to ONE server (an SSIS server), and dynamically change the sources/targets, instead of deploying the same package multiple places?
Post #1537293
Posted Monday, February 3, 2014 7:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 288, Visits: 519
I have not found any broadcast mechanisms in SSIS. We have packages that deploy the same data to multiple servers by running the same package but overriding the connection string properties.
Post #1537299
Posted Monday, February 3, 2014 11:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:47 AM
Points: 7, Visits: 18
@ Nevyn: I've already thought about that, but how would I do this?

@ EricEyster: We have also some packages that cycle through different connections, executing the steps and overriding the connection. But in my case, we want to run the packages on servers with different time zones every day at the same time ...
Post #1537618
Posted Tuesday, February 4, 2014 5:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 641, Visits: 2,149
What version of ssis?
Post #1537704
Posted Tuesday, February 4, 2014 5:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:47 AM
Points: 7, Visits: 18
It is 2008 R2
Post #1537711
Posted Tuesday, February 4, 2014 7:01 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 641, Visits: 2,149
Well, it is tough to get too specific without knowing what your source/destination servers are or the particulars of your package.

But you can define an expression on a connection manager, and you can use those to dynamically switch the source and/or destination server. If the server uses windows authentication, you can do an expression on just servername (assuming the same user has access to all servers). If not, you can do an expression on the connection string.

You assign a variable inside that expression, and then you either pass in that variable when executing that package (you would need separate jobs set up for each set of target servers), or you can create a database table on the ssis server with a list of source/destination servers (and maybe the time zone for those servers if needed).




Post #1537744
Posted Wednesday, February 5, 2014 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:47 AM
Points: 7, Visits: 18
I think I've got it.
I just create a table on each server containing login information. With this table I will dynamically create my connection string for my packages that are all uploaded to one "master" server.
When creating a job on a server I will have to refer to the packages stored on the master server. Right?
But ...
I still have got some dataflow tasks. How does that behave? Is dataflow task data first transferred to the master server because the package is stored there? Or does my server just fetch the package from there?
Post #1538184
Posted Wednesday, February 5, 2014 12:42 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 641, Visits: 2,149
Not exactly.

Im talking more about having the master server be where you schedule and manage all the jobs. Any data flows would pass though the ssis server's memory before being written to the destination. Any tsql commands would run on the server you connected to.
Post #1538334
Posted Wednesday, February 5, 2014 11:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:47 AM
Points: 7, Visits: 18
In case of the dataflow task, this method is not the best one for us. Our dataflow tasks are just the tasks, where the most data is transferred... So jobs will take a very long (too long) time.

I was already thinking about a batch job, that uploads the packages to multiple servers using DTUTIL.exe
But at the moment I have no clue how to.

So far, thanks for you replies.
Post #1538483
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse