Which connection manager in Script Task for SSIS 2012

Question

Which connection manager in Script Task for SSIS 2012

happy55

Ten Centuries

Points: 1243
More actions
July 21, 2015 at 5:30 am

#398773

I have to create a script task in SSIS 2012 .Which connection manager ADO.NET or OLE DB
shall I use?
I goggled and found ADO.NET is preferred for scripts .Then I want my connection manager to have an expression where I can specify the connection manager as per environment like for example if region is dev then a different connection string ,for production a different one.
Can I do that with ADO.NET .If so can anyone help how to do it ?
Thanks,

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

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

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

happy55 (7/21/2015)
I have to create a script task in SSIS 2012 .Which connection manager ADO.NET or OLE DB
shall I use?
I goggled and found ADO.NET is preferred for scripts .Then I want my connection manager to have an expression where I can specify the connection manager as per environment like for example if region is dev then a different connection string ,for production a different one.
Can I do that with ADO.NET .If so can anyone help how to do it ?
Thanks,

Use SSIS Environments to control connection manager settings. No need to hard-code stuff. See here[/url], for example.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

happy55 Ten Centuries Points: 1243 More actions · Answer 2

Thanks for the reply. Your example is talking about project deployment model.

If I am using package deployment model, then I would need my connection string to be dynamic using an expression depending on environment or is it that I should use project deployment model?

I have 15 SSIS packages that I have to create and if I use project deployment model I cannot deploy them individually .I am new to SSIS and not that familiar. Your help is much appreciated

If I go with package deployment then Can you help me the type of connection manager for script task in SSIS ?

SQL.AP Hall of Fame Points: 3244 More actions · Answer 3

In package deployment model you can use config table or config file instead.

____________________________________________________________

AP

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

here is a video explaining how to create package configuration file.

https://www.youtube.com/watch?v=Ap5Mc6lS_PI

Jayanth Kurup[/url]

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

happy55 (7/21/2015)
Thanks for the reply. Your example is talking about project deployment model.
If I am using package deployment model, then I would need my connection string to be dynamic using an expression depending on environment or is it that I should use project deployment model?
I have 15 SSIS packages that I have to create and if I use project deployment model I cannot deploy them individually .I am new to SSIS and not that familiar. Your help is much appreciated
If I go with package deployment then Can you help me the type of connection manager for script task in SSIS ?

15 packages is nothing to be worried about.

You are correct in thinking that you cannot deploy packages individually using the project deployment model.

You can, however, divide the packages into multiple projects and deploy those individually (if your packages perform unrelated functions).

I have not used the package deployment model for years now & so I'm not the best person to advise you, as I've tried to forget about it 🙂

Think of some of the benefits: shared connection managers, shared parameters, centralised configuration of parameters and connections, out-of-the-box logging.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin