Does anyone have a handy snippet of code in C# or VB.net to find and set the "Integrated Security" property of a connection? I used to do this stuff in DTS, but ... that was a long time ago! Thanks. Josh

I wouldn't mess with it in code, I would set the Connection properties using the Connection Manager, then just USE that conneciton in code. Why exactly do you need to change the Integrated Security of a connection? What is your code trying to accomplish. Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I wouldn't mess with it in code, I would set the Connection properties using the Connection Manager, then just USE that conneciton in code. Why exactly do you need to change the Integrated Security of a connection? What is your code trying to accomplish. I don't want to mess with it in code, I wish it were a setting in the config, but it doesn't seem that the connection exposes that, at least the config wizard doesn't present it to be clicked! The problem is when we deploy a package from dev to production, we (unfortunately) have to switch certain connections from integrated security to sql security. (I know this is undesirable, but we are stuck with it for a few months) I'm just trying to extend the config capability to include these connection properties. Am I missing an easier way? I suspect this is just two lines of code, but not certain exactly what those two lines are. I see snippets like: package.Connections["ado.WarehouseStaging"].ConnectionString = "asdafdsfd" But not sure how to set package, nor how to specify the property - square brackets, [Integrated Security]? Thanks. Josh ps - we *can* do it by setting the connection string from the config, but I don't like that solution for several reasons. setting the Integrated Security property seems to be the "right" way.

CONFIGURATIONS is your answer, but according you your last statement, you don't like doing that. Why build complex coded logic into the package when Microsoft has provided the perfect solution with this great bit of functionality? I use a combination of one XML Configuration entry that has the Connection String of my SSIS_Configuration database. And THAT has the Connection String of every OTHER database, shared folder, or setting that any SSIS package would ever need. I have a copy of that database in each of the environments: DEV, Test, and PROD, and each has different [ConfiguredValue] entires. And the XML file exists on the C:\ drive of each SQL server and/or application server that runs packages. It's been working great for 5 years for me without issue. Why don't you like using Configurations?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

I love configurations, I was responsible for putting them into all of our packages and they work great, but they won't touch the Integrated Security property directly. What I don't like is using the ConnectionString property, which is an aggregate of a bunch of other properties and encodes the provider name and all sorts of other nastiness. ConnectionStrings were great in VB3 but that's pretty retro to be using now. Is it that much rocket science to ask for two lines of code to get the package populated and the property set programmatically? Josh

What I don't like is using the ConnectionString property, which is an aggregate of a bunch of other properties and encodes the provider name and all sorts of other nastiness. It's one of the things I noticed about setting Connection Manager properties via a Configuration. You really can't go a-la-carte and pick and choose which properties you want to override. I settled on setting the entire "Connection String" property each and every time. "nastiness"? Really? My longest ConfiguredValue is 195 characters. If you go through the wizard to have it create the Config table for you, it sets the data length to NVARCHAR(255). Not un-manageable by a long shot (IMO). But back to your original issue, Sorry, I can't help you with code as I don't use it to manipulate my Connection Manager properties.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi Josh, The 'Integrated Security' is configured in the connection string directly. It is not really a property of a connection in SQL Server Integration Services(SSIS). So, in this case, as Todd mentioned, we don't need to use coded logic into the package. Please use the configuration directly. In order to change a connection to be SQL Server authentication from Windows Authentication, please set th 'Integrated Security' to be False, and configure the uid and pwd in the connection string. For example: Windows Authentication: Data Source=.;Initial Catalog=AdventureWorks;Integrated Security=True;Application Name=SSIS-Package-{D900CC64-0C37-42DE-8AF9-45C31AD74F33}LocalHost.AdventureWorks; to Data Source=.;User ID=sa;Initial Catalog=AdvancedWorks;Persist Security Info=True;Application Name=SSIS-Package-{D900CC64-0C37-42DE-8AF9-45C31AD74F33}LocalHost.AdventureWorks; Since the default value for 'Integrated Security' is false. If we are using SQL Server authentication, we can ignore it. If you want to use code to change the authentication, we also change the string directly. For more information about connection string, please see: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT

Jin, Thanks for your post. I still would like to do it by setting just that property - assuming that would do the job. I understand (that is, I guess) that SSIS itself communicates with the driver - by constructing the connection string, and that the connection string offers a bunch of different options that may be useful and SSIS does not expose in the dialog, so exposing the string property is a quick way to offer capability to access rare features. However, SSIS very usefully exposes, say, the servername (aka Data Source) directly to the configuration widget, and username, and password - and forgot to expose the IntegratedSecurity that would complete the minimum but very common set of properties! And, I presume it *can* be set programmatically to work around this Microsoft omission, by just the right two or three lines of code. So, I'd still like them. SSIS already knows how to read these properties and construct the connection string for us, and that is what it does most of the time, when we just set the properties in the dialog. It apparently already knows how to set the "integrated security" option. All we have to do is set the flag - it sure looks like. Until then, I guess we all just have to work around yet another kludgy feature in SSIS as best we can, and I need to engage in connection string construction for about the first time since VB3. Josh