SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deploy package to multiple servers


Deploy package to multiple servers

Author
Message
martin.hock
martin.hock
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 28
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
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3636 Visits: 3149
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?
EricEyster
EricEyster
Right there with Babe
Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)Right there with Babe (770 reputation)

Group: General Forum Members
Points: 770 Visits: 520
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.
martin.hock
martin.hock
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 28
@ 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 ...
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3636 Visits: 3149
What version of ssis?
martin.hock
martin.hock
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 28
It is 2008 R2
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3636 Visits: 3149
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).
martin.hock
martin.hock
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 28
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?
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3636 Visits: 3149
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.
martin.hock
martin.hock
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 28
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search