| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-08-30 : 10:34:51
|
| So my boss wants me to use the SSIS utility to import some data to existing tables instead of scripting a query to do an "insert into". I built my SSIS packages on the production server, but i have a seperate validation server, with ALL the same db's and tables as the production server. When i move those SSIS packages to the validation server and try to run them, they bomb out in about a million different places ultimately not getting me anywhere. Is it not possible to build these on one server and execute them on another with the same db's and table structures? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-30 : 10:42:33
|
| Yes.Create variables to hold the server name, database name and file name and file folder.Set these variables when calling the package and use them to configure the connections (look at expressions).Then when yoou move between servers or move a folder you just change the call.You shoould never have hard coded connections in a package as it means you have to redevelop when moving from dev to production and at some point someone will copy the wrong package.You can also use configuations (also better to set variables from them) but I prefer to set variables on the callAlso shoould leave the package with the variables set to an invalid value so that the package fails if the variables are not set.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-08-30 : 11:41:19
|
| Ok, in an effort to avoid the process above:on an SSIS package to import data, when i open the saved package, and under Execution Options, if i choose "Validate package without Executing", will it simulate execution of the package against the relevant table(s) without actually inserting the data? And will it show if any errors would occur in the actual insert?? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-08-30 : 11:50:16
|
to paraphrase a something else:You had one problem.Then you used SSIS.Now you have 2 problems.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-08-30 : 11:51:38
|
| i have NO say in this issue. I would much prefer to script a simple insert into query that i could run on the validation server, but i'm being told not to, for no good reason whatsoever. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-08-30 : 12:14:49
|
quote:
Originally posted by Transact Charlie
to paraphrase a something else:You had one problem.Then you used SSIS.Now you have 2 problems.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/
That is total nonsense and it doesn't help. |
|
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-08-30 : 12:16:20
|
| Well, he's right. I would be much more productive on the current task if i could kick it old school. But i can't. Still, i wait for an answer. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-08-30 : 12:20:17
|
| To expand on what nigelrivett and visakh16 said. There are a bunch of ways to set up configurations in your packages. BTW, what version of SSIS are you using?You should use your favorite search engine and search for package configuration. There a quie a few options. The two I use most with pre-2012 packages are Config files and Config via sql agent job (parameters). |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-08-30 : 12:21:17
|
| I refer yoou to my previous post.You need something to make the package configurable for the environment.It's worth spending time over as this will be needed for all packages you build.As I said I prefer to use variables in the package.I prefer to set them in the dtexec statement but you can use a configuration file or table if you wish.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-08-30 : 12:22:41
|
quote:
Originally posted by WJHamel
Well, he's right. I would be much more productive on the current task if i could kick it old school. But i can't. Still, i wait for an answer.
I suspect this isn't going to be like your other SQL tasks were we spoon feed you the answer. You're going to have to do some work/research on your own. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-30 : 15:44:40
|
quote:
Originally posted by WJHamel
Well, he's right. I would be much more productive on the current task if i could kick it old school. But i can't. Still, i wait for an answer.
you've two optionsEither stick with SSIS as your boss suggests and use configurations or variables to make it grab dynamic property values at runtime or use your way of scripting out the inserts unless you're dealing with heterogenous sources or dealing with rather huge tables------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
SSIS package versus scripted insert