Integration Services & Multiple Developers

Question

Integration Services & Multiple Developers

ElNevera

SSC Veteran

Points: 237
More actions
January 7, 2016 at 8:01 am

#307572

Hello
I work in a newly created BI team. We have experience using Business Objects for ETL and some development experience using Visual Studio 2013 and TFS. We are using SQL Server 2014.
We have been tasked with moving a lot of ETL jobs from BO to SSIS and have so far been doing well but now we are collaborating on Integration Services solutions and adding more and more packages we are starting to hit problems.
Does anyone have a good guide to best practices in relation to building SSIS solutions? We have a solution with a package for each table being built but I am starting to worry it isn't the best way especially in regards to deploying the packages.
The problems we are experiencing are mainly down to connections. For instance, I can build a package, use shared project connections (ODBC and OLE DB) using SQL Accounts, execute it successfully and check it in. Then my colleague opens the same package and is faced with multiple errors which all point to a connection not being in place.
The SQL account used in the above connections gets locked out at that point.
I have tried searching on-line for similar problems but haven't found anything.
Can anyone help?
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

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

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

ElNevera (1/7/2016)
Hello
I work in a newly created BI team. We have experience using Business Objects for ETL and some development experience using Visual Studio 2013 and TFS. We are using SQL Server 2014.
We have been tasked with moving a lot of ETL jobs from BO to SSIS and have so far been doing well but now we are collaborating on Integration Services solutions and adding more and more packages we are starting to hit problems.
Does anyone have a good guide to best practices in relation to building SSIS solutions? We have a solution with a package for each table being built but I am starting to worry it isn't the best way especially in regards to deploying the packages.
The problems we are experiencing are mainly down to connections. For instance, I can build a package, use shared project connections (ODBC and OLE DB) using SQL Accounts, execute it successfully and check it in. Then my colleague opens the same package and is faced with multiple errors which all point to a connection not being in place.
The SQL account used in the above connections gets locked out at that point.
I have tried searching on-line for similar problems but haven't found anything.
Can anyone help?
Thanks

Is there a reason you're not using Windows Authentication?

ElNevera SSC Veteran Points: 237 More actions · Answer 2

Phil Parkin (1/7/2016)
Is there a reason you're not using Windows Authentication?

Whilst initially setting up the packages we were using Windows Authentication but when I deployed the packages to SQL Server they failed to run stating the account (A domain account I created) didn't have access. I triple checked the access but could not figure the access issue out.

I set the domain account as the owner and set the sql job to run as that account but it still failed. I swapped to a SQL account and it ran with no issues...

Phil Parkin SSC Guru Points: 247206 More actions · Answer 3

ElNevera (1/7/2016)
Phil Parkin (1/7/2016)
Is there a reason you're not using Windows Authentication?
Whilst initially setting up the packages we were using Windows Authentication but when I deployed the packages to SQL Server they failed to run stating the account (A domain account I created) didn't have access. I triple checked the access but could not figure the access issue out.
I set the domain account as the owner and set the sql job to run as that account but it still failed. I swapped to a SQL account and it ran with no issues...

So, do your development using Windows Auth and then use an SSISDB Environment to reconfigure the connections accordingly after deployment, so that they work on the server.

ElNevera SSC Veteran Points: 237 More actions · Answer 4

Phil Parkin (1/7/2016)
So, do your development using Windows Auth and then use an SSISDB Environment to reconfigure the connections accordingly after deployment, so that they work on the server.

Thanks Phil, that is how we started and only changed once we couldn't get the deployed versions running. I need to read up on Environments but is that a way of changing connections even after redeploying?

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

ElNevera (1/7/2016)
Phil Parkin (1/7/2016)
So, do your development using Windows Auth and then use an SSISDB Environment to reconfigure the connections accordingly after deployment, so that they work on the server.
Thanks Phil, that is how we started and only changed once we couldn't get the deployed versions running. I need to read up on Environments but is that a way of changing connections even after redeploying?

Yes. You can completely reconfigure the connection string from an Environment, which would be picked up by the Agent job.

Have a read here[/url] and post back with any further questions.

ElNevera SSC Veteran Points: 237 More actions · Answer 6

Thanks!

I have read through and understand the usefulness of the environments, we will be working towards that setup now but we still have 2 issues.

1: The ODBC connections keep locking out our sql account when one of the team opens a package as VS seems to try and check the connections without a password. This is quite annoying because we have 4 in the team having to dip in and out of the packages and having to re-apply the password and unlock the SQL account each time.

2: The OLE DB connections are now all back to Windows Authentication but as each developer checks in and out the connections are erroring saying the connection (usually some GUID type string does not exist. I find this confusing as we all use Windows Authentication on the same 4 OLE DB connections.

Help please! 🙂