As being an involuntary SSIS developer and no-one to ask, but you 
- How do you work with development, test, production and SSIS packages?
The background:
I'm having my solution in one path on my harddrive, and the target structure is on another path at my harddrive. And sourcecode in a third path. I know. In there I see the problem already.
The second path is similar to the path-structure on the server where the packages are to be executed in production environment.
But I want to keep my stumbling efforts of half-baked files and experimental ones I have in the solution path clean from the ones I really going to distribute. And I sure do not want to compromise TFS ones.
The packages I run is part of a framework (you probably have a similar one in your shop) that they have bought and implemented at the company I work for.
One solution per source system and some solutions just putting different source systems together to marts (i.e. one of them has the mart part the other just the part up to the dw).
They are quite generic, not so many things to change between two projects.
One master package per source system orchestrates the information loading from the source in that runs initial audit info and then Stage, DW, Mart and cube loading packages.
Each package on second level runs the same third level package, but gathers different configurations parameters and send forward to that generic package structure level:
"<sourcesystemabbreviation>LoadTables.dtsx"
That package look in metadata and from arguments of parent package, to know what kind of loading is to take place, for example:
* For stage: one/many "from file to table" and/or one/many "table to table" and/or one/many "sharepoint list to table".
* For dw: one/many stage-view to dw-table.
* For mart: one/many dw-view to mart-table,
* For cube: one/many cube(s) and what to process in the cube.
And then loops single objekt generic package with that info (per file/table/list/cube) that is actually doing any real data moving work.
The template for next source system build is to copy one of them to a new solution, changing name of packages on the fly, regenerate package id, adding the new packages configurations to metadata tables and there you go!
They all have package configurations referencing metadata tables telling where subpackages are located and which connections to use for sources and destinations of the respective SSIS packages.
That part of the setup is really smooth.
The problem:
But when I debug them (often errors are somewhere in the metadata tables insert rows part of a new source system solution) I get a bit confused of my own doing
The master package is the only one loaded in Visual Studio (SSDT-BI 2013) when I debug (maybe I should open all of them first?). I press "Start" and when it all works it opens all packages one by one (from the metadata path, i.e the production-similar one) and runs them.
If I want to test-run the masterpackage I run that one i a SQL Server job which will pick up the masterpackage from the production-similar one as well.
Now - if I get errors - I push end debug and if it's a SSIS sub-package error, and not a metadata-table-error, I start fixing the errors in the respective packages, often enough forgetting that it is the production-similar copy of the package I then change...
Then I have so many times copied the one from my solutions path to the production-similar path, and by that accidentily overwritten my own recent done changes, found that at re-runs of the packages, and
only then find that I still have the same errors. And realise I have re-do changes in my solutions-path (and copy and rerun...) I do that all the time! Embarrasing!
It's to time-consuming to change the package-path package configurations back and forth from my solution to the production-like one, but I don't want to distribute the package there until it's developer-tested so to speak.
We have TFS/Team explorer as well. It's no friend of mine yet, but I would probably need to be working in that path (in my setup a third path to handle and synch).
For now I check out and copy that outchecked code to my solution path, and when bug free copies it back and checks it in.
But it should probably be all the same, all three of them paths, shouldn't it?
The Question:
What do you do/would you do? Work in the production-similar path all the time, and not "train to distribute" until project is bug-free.
Kind Regards
JB
- How do you work with development, test, production and SSIS packages?
The background:
I'm having my solution in one path on my harddrive, and the target structure is on another path at my harddrive. And sourcecode in a third path. I know. In there I see the problem already.
The second path is similar to the path-structure on the server where the packages are to be executed in production environment.
But I want to keep my stumbling efforts of half-baked files and experimental ones I have in the solution path clean from the ones I really going to distribute. And I sure do not want to compromise TFS ones.
The packages I run is part of a framework (you probably have a similar one in your shop) that they have bought and implemented at the company I work for.
One solution per source system and some solutions just putting different source systems together to marts (i.e. one of them has the mart part the other just the part up to the dw).
They are quite generic, not so many things to change between two projects.
One master package per source system orchestrates the information loading from the source in that runs initial audit info and then Stage, DW, Mart and cube loading packages.
Each package on second level runs the same third level package, but gathers different configurations parameters and send forward to that generic package structure level:
"<sourcesystemabbreviation>LoadTables.dtsx"
That package look in metadata and from arguments of parent package, to know what kind of loading is to take place, for example:
* For stage: one/many "from file to table" and/or one/many "table to table" and/or one/many "sharepoint list to table".
* For dw: one/many stage-view to dw-table.
* For mart: one/many dw-view to mart-table,
* For cube: one/many cube(s) and what to process in the cube.
And then loops single objekt generic package with that info (per file/table/list/cube) that is actually doing any real data moving work.
The template for next source system build is to copy one of them to a new solution, changing name of packages on the fly, regenerate package id, adding the new packages configurations to metadata tables and there you go!
They all have package configurations referencing metadata tables telling where subpackages are located and which connections to use for sources and destinations of the respective SSIS packages.
That part of the setup is really smooth.
The problem:
But when I debug them (often errors are somewhere in the metadata tables insert rows part of a new source system solution) I get a bit confused of my own doing
The master package is the only one loaded in Visual Studio (SSDT-BI 2013) when I debug (maybe I should open all of them first?). I press "Start" and when it all works it opens all packages one by one (from the metadata path, i.e the production-similar one) and runs them.
If I want to test-run the masterpackage I run that one i a SQL Server job which will pick up the masterpackage from the production-similar one as well.
Now - if I get errors - I push end debug and if it's a SSIS sub-package error, and not a metadata-table-error, I start fixing the errors in the respective packages, often enough forgetting that it is the production-similar copy of the package I then change...
Then I have so many times copied the one from my solutions path to the production-similar path, and by that accidentily overwritten my own recent done changes, found that at re-runs of the packages, and
only then find that I still have the same errors. And realise I have re-do changes in my solutions-path (and copy and rerun...) I do that all the time! Embarrasing!
It's to time-consuming to change the package-path package configurations back and forth from my solution to the production-like one, but I don't want to distribute the package there until it's developer-tested so to speak.
We have TFS/Team explorer as well. It's no friend of mine yet, but I would probably need to be working in that path (in my setup a third path to handle and synch).
For now I check out and copy that outchecked code to my solution path, and when bug free copies it back and checks it in.
But it should probably be all the same, all three of them paths, shouldn't it?
The Question:
What do you do/would you do? Work in the production-similar path all the time, and not "train to distribute" until project is bug-free.
Kind Regards
JB