March 24, 2014 at 3:17 am
Guys,
We are about to embark on a large scale data warehousing project which will be run mainly on SSIS 2012. I am insiting that we use some form of source code control. The company standard is Mercurial because we have a number of development languages, but the DW team have elected to the TFS.
I am familiar with Hg but not TFS. In Hg you create a central repository and each developer pulls the repo down to their local machine (I.e. each dev has a complete copy of the project) - I put mine in a folder on the C drive. Due to the challenges of merging XML files, it is assumed that developers will work on discreet dtsx packages.
My question is this: Should I keep my 'working' copy of the solution in a separate location (default is
C:\users\user.name\microsoft\visualStudio\Projects) and move the files to be controlled to the repo when I am ready, or should I use the repo copy of the solution as my development folder. With the second method, changes to packages are immediately reflected in the repo, but there are also a lot of files which don't need to be controlled (.sou, build files, .manifest etc..) and controlling these actually makes things more complicated because they will contain settings that are personal to me and may not match those of other developers.
This is the first time I have used SCC in a multi-developer environment. Previously I was the only dev so booking in all files was not an issue.
March 24, 2014 at 3:21 am
When I worked with SSIS and TFS, I used the second option.
I'm no TFS expert, but can't you configure it to ignore some extensions? I don't recall to have much issues with the personal settings files.
ps: you are correct in your assumption that devs should work on individual packages
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 24, 2014 at 3:29 am
Thanks Koen, If its good enough for you, then I will take that option 🙂
Next question. Which files need to be in SCC and which one don't. Obvioulsy the .sln, .proj, .dtsx and .config files should be in.
I am presuming the build directory and any log files should be out. I am hoping to persuade the powers that be that each dev should have their own copy of the database, so db setting will be personal to the developer (database objects will also get controlled) so how would we manage that in the SCC.
The databases will be called DevDB_Bill, DevDB_John, DevDB_Clara. So we would just have a personal config XML that appends the userID to the database name (derive the dbname from an expression with a variable) but this seems a bit of a kludge.
March 24, 2014 at 3:33 am
aaron.reese (3/24/2014)
Thanks Koen, If its good enough for you, then I will take that option 🙂Next question. Which files need to be in SCC and which one don't. Obvioulsy the .sln, .proj, .dtsx and .config files should be in.
I am presuming the build directory and any log files should be out. I am hoping to persuade the powers that be that each dev should have their own copy of the database, so db setting will be personal to the developer (database objects will also get controlled) so how would we manage that in the SCC.
The databases will be called DevDB_Bill, DevDB_John, DevDB_Clara. So we would just have a personal config XML that appends the userID to the database name (derive the dbname from an expression with a variable) but this seems a bit of a kludge.
That's already out of my comfort zone of TFS, sorry 😀
The most important files are indeed .sln, .proj, .config and .dtsx, but if you have SSIS 2012, there can also be .params (project parameters) and .conmgr (project connection managers). There can also possibly be miscellaneous files, such as biml files for exampel if you use that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 24, 2014 at 3:55 am
Koen Verbeeck (3/24/2014)
aaron.reese (3/24/2014)
Thanks Koen, If its good enough for you, then I will take that option 🙂Next question. Which files need to be in SCC and which one don't. Obvioulsy the .sln, .proj, .dtsx and .config files should be in.
I am presuming the build directory and any log files should be out. I am hoping to persuade the powers that be that each dev should have their own copy of the database, so db setting will be personal to the developer (database objects will also get controlled) so how would we manage that in the SCC.
The databases will be called DevDB_Bill, DevDB_John, DevDB_Clara. So we would just have a personal config XML that appends the userID to the database name (derive the dbname from an expression with a variable) but this seems a bit of a kludge.
That's already out of my comfort zone of TFS, sorry 😀
The most important files are indeed .sln, .proj, .config and .dtsx, but if you have SSIS 2012, there can also be .params (project parameters) and .conmgr (project connection managers). There can also possibly be miscellaneous files, such as biml files for exampel if you use that.
We include connection manager files and project parameter files. Apart from that, you are on the right track with solution, project, .dtsx
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
April 2, 2014 at 8:53 am
Aaron,
If I am right, you can let Visual Studio do this for you.
When you add a solution to TFS, it should take care of all this for you.
Eric
April 2, 2014 at 9:00 am
We include connection manager files and project parameter files. Apart from that, you are on the right track with solution, project, .dtsx
Phil,
In previous versions, I found that SSIS was a pain in the... neck with TFS in multi user environments because if different developers were trying to point their package to local connections, this, by default, was provoking a checkout of the entire SSIS package from TFS.
I suppose this (long overdue) shifting of connection data to a separate file is meant to fix this problem.
I would therefore be tempted to either not put the .conmgr files in TFS, or put one version in TFS and "cheat" :Whistling: on local developers box by making the file R/W outside TFS (to prevent the automatic checkout) so that we can all work with different connections without bothering each other.
What do you think?
Am I missing something (it has happened before...)?
Eric
April 3, 2014 at 9:45 am
Eric Mamet (4/2/2014)
We include connection manager files and project parameter files. Apart from that, you are on the right track with solution, project, .dtsx
Phil,
In previous versions, I found that SSIS was a pain in the... neck with TFS in multi user environments because if different developers were trying to point their package to local connections, this, by default, was provoking a checkout of the entire SSIS package from TFS.
I suppose this (long overdue) shifting of connection data to a separate file is meant to fix this problem.
I would therefore be tempted to either not put the .conmgr files in TFS, or put one version in TFS and "cheat" :Whistling: on local developers box by making the file R/W outside TFS (to prevent the automatic checkout) so that we can all work with different connections without bothering each other.
What do you think?
Am I missing something (it has happened before...)?
Eric
We have connection managers defined with connections to a dev environment. In non-dev environments, we use SSISDB environment variables to override the values in the connection managers.
If you're using multiple local dev environments, you can cheat by getting all of the devs to create an instance alias on their PCs (DEV_SQL, or whatever) which points to their local dev instance and check that in to your TFS connections.
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
April 3, 2014 at 9:51 am
I like the idea of the Alias... Do you do that through .../Windows/system32/drivers/etc/hosts file?
For SSISDB, do you just not define it in your dev environment, or get it pointing at your alias?
April 4, 2014 at 3:19 am
Phil,
One more question if I may...
I can get this Alias to work when I connect through ODBC (like through MS Access or an ODBC connection through an SSIS Package) but not directly through SSMS or an SSIS OLE DB Connection...
Through SSMS or OLE DB, it just hangs for 30 seconds before giving up on connection timeout. 🙁
Is it meant to be so or am I missing something?
Cheers
Eric
April 4, 2014 at 3:23 am
Eric Mamet (4/4/2014)
Phil,One more question if I may...
I can get this Alias to work when I connect through ODBC (like through MS Access or an ODBC connection through an SSIS Package) but not directly through SSMS or an SSIS OLE DB Connection...
Through SSMS or OLE DB, it just hangs for 30 seconds before giving up on connection timeout. 🙁
Is it meant to be so or am I missing something?
Cheers
Eric
Should work. To solve this, you may need to create both 32- and 64-bit versions with the same alias name (haven't done it for a while).
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
April 4, 2014 at 3:33 am
I am indeed using SQL Server Developer edition 64 bits and I do get through once I have created a 32 bits alias.
I did not realise that the 32 or 64 bits implementation had an impact on the communication between client and server apps...
Does that mean that OLE Db is 32 bits only?
Why would I want to create a 64 bits alias (the default), then?
Thanks for your help. We now have a solution anyway! 🙂
April 4, 2014 at 3:58 am
Eric Mamet (4/4/2014)
I am indeed using SQL Server Developer edition 64 bits and I do get through once I have created a 32 bits alias.I did not realise that the 32 or 64 bits implementation had an impact on the communication between client and server apps...
Does that mean that OLE Db is 32 bits only?
Why would I want to create a 64 bits alias (the default), then?
Thanks for your help. We now have a solution anyway! 🙂
Nice. If you're developing packages within VS (devenv.exe), you are in a 32-bit environment, so it wouldn't surprise me. When they run via SQL Agent, they will flip to 64-bit by default. SSMS is 32-bit.
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply