boomed - I took ages to do aptitude test so client offers another $5000

[Stan.goodvibes]

Good luck with it. I consider myself quite patient with technology, but SSIS often makes me want to smash things.

man that sounds like pretty well every MS Dev product. I used DTS quite a bit a while back, and a previous clientco sent me on the 2008 courses for SSRS.SSIS, and SSAS but I ended up doing a lot of reporting and not much SSIS. I'm keen to learn it inside out as the holy grail of contracting for me is to do overnight ETL for a UK company while being here in Oz. Plus it's kind of fun - all those little coloured boxes LOL

Thanks for the tips guys I have copied and pasted and filed away...

[Spacecadet]

This is exactly what I was wrestling with lately. "There must be a simple way to specify which database I want to run the package into when I call it", I was thinking.

Much trial and error and google eventually came up with:

dtexec /SET "\Package.Variables[User::TargetConnectionString].Properties[Value]";\""cnn_string_goes_here"\"

Thats OK for setting up the connections at run time for a single package, although I wouldn't recommend it
If you have a package calling lots of other packages then that method will fail
Much better to user either XML config files stored in the same location in each server
or
An environment variable which points the packages to a database table which contains the configurations.

If you use consistent naming in the configurations and the packages then multiple packages can use the same configurations

[2BIT]

Unless you have specific needs which require a SQL Server deployment then its much more straightforward to just leave the packages in the file system and run from there.
The biggest headache though can be in making sure the connections are all pointing to the right places when you move packages from dev to test to prod. Configurations are your friends here. A well set up system will allow you to move and run packages without having to do anything with the packages apart from move them.

this is it, I had to do some quick and dirty automation to fix a job done by a consultant that was so atrocious it needed doing from scratch- all was well on my dev environment but as soon as I tried to schedule it on the server as a deployed package ran into complications.... I didn't have the time to learn it all just utilised the efficient GUI for replacing the ETL jobs- got 99% of the way there very quickly and thought 'oh sh*t!' - so ended up just running the jobs from my local machine - wish I'd had the time to learn it properly

[thunderlizard]

The biggest headache though can be in making sure the connections are all pointing to the right places when you move packages from dev to test to prod.

This is exactly what I was wrestling with lately. "There must be a simple way to specify which database I want to run the package into when I call it", I was thinking.

Much trial and error and google eventually came up with:

dtexec /SET "\Package.Variables[User::TargetConnectionString].Properties[Value]";\""cnn_string_goes_here"\"

[Spacecadet]

found it ok to use until I wanted to deploy a job and then struggled a bit

Unless you have specific needs which require a SQL Server deployment then its much more straightforward to just leave the packages in the file system and run from there.
The biggest headache though can be in making sure the connections are all pointing to the right places when you move packages from dev to test to prod. Configurations are your friends here. A well set up system will allow you to move and run packages without having to do anything with the packages apart from move them.

[2BIT]

There is a definite learning curve to SSIS, but once you get your head round it then it all makes sense

found it ok to use until I wanted to deploy a job and then struggled a bit

[Spacecadet]

Good luck with it. I consider myself quite patient with technology, but SSIS often makes me want to smash things.

There is a definite learning curve to SSIS, but once you get your head round it then it all makes sense

[thunderlizard]

Good luck with it. I consider myself quite patient with technology, but SSIS often makes me want to smash things.

[Stan.goodvibes]

Going for a one-year fixed term role (bank - good money - SQL Server, SSRS, SSIS skillset upgrade) and they wanted me to complete an online aptitude test at home. Took me about a week to get round to it and the clientco thought I was getting cold feet so they offered another $5k more, and I was already at the top of the pay band. WIN!

Mind you aptitude test was a bstard of a thing when I finally did Monday night.