Package Update and Build Process

My SSIS solution has about hundred packages and time to time I have to edit a package. I understand I could use 'Build' command to compile only updated package, as opposed to Rebuild which recomplies all of the packages.

Nevertheless, in both cases SSIS opens all of the packages in design environment before compilation. My packages are saved in SourceSafe and that process takes quite long and I was wondering if there was any other way to compile only updated package where none of the other packages are opened during Build/Rebuild process? For example we could use dtutil to deploy only updated packages without running Package Installation Wizard.

Turn the of the "Build deployment Utility" option ala http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=874332&SiteID=1. With this option disabled, each package will cease opening every time you debug just one package via F5 or select the build project or build solution menu items.

For that matter, turn off the Integration Services project "Build" option in Visual Studio's Configuration Manager. SSIS in BIDS doesn't compile/build anything, but rather, copies your hundred .dtsx files to the project relative "bin\" subdirectory. Its doubtful you need four copies of each of the hundred packages, one each in source control, and three each in your local workspace, two of which are superflous (e.g. those copies in bin\ and bin\Deployment)

As you mentioned, use dtutil, or xcopy for that matter (if appropriate) for deployment, rather than the Package Installation Wizard. For example see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1828408&SiteID=1, wherein dtutil is used for SQL server deployment.

|||

Thanks very much, your suggested approach would save me painful waiting time I had to endure before.

Asaf

sql

Package setup, configurations and testing

Hi,

I'm looing for advice on how to organize my SSIS packages. I'll quickly describe my requiremenst, and then outline how I see the solution.

1. Should work for development in BI Studio. Design time experience should be good, so static schema information should be available in all steps. Packages should run in debug mode inside BI Studio.

2. Should be able to run automated tests (which I gather is difficult. See proposed solution below). Tests should run both on a test machine, in batch mode, and from BI Studio.

3. Should work for deployment.

The packages should be fine-grained. Automated tests will be implemented as SSIS packages that call the packages to be tested via an Execute Package Task. There will be one test package per testcase. A test will look like: [Create Schema] -> [Load test data]->[Execute Package to be tested]->[Verify expected results]->[Teardown].

There will be one master test package that executes all the tests.

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

I welcome any feedback, suggestions of alternate strategies, etc.

/Anders

AndersI wrote:

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

One possible drawback is that when the developers run in BIDS (not the test case), the packages will use the connection strings coded in the packages. I like using configurations for my packages even in development.

Another approach might be to use another type of configuration for the packages in dev, but when you run them from the test pacakges, use Execute Process instead of Execute Package. You can call DTEXEC from Execute Process, and override the connection strings by using the /CONN switch, which should take precedence over the configurations.

AndersI wrote:

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

Can't you just put the test case packages in one project, and leave the real ones in another project?

|||

Thanks!

I'm using configurations for the master packages to set the connection variables, and then passing them down to child packages via parent package variable configurations.

The reason I want the packages-to-be-tested in the same project as the testcase packages is so that you can run them in the BI studio debugger. If the package called via Execute Package is not in the same project, then the debugger won't display it. [Edit] Scratch that, it works. Must have been a user error on my part... [/Edit]

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

/Anders

|||

AndersI wrote:

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

Yes, I've done exactly that. It works well.

Package not picking up latest Config File changes

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:\Files\source.txt" and this is changed to "C:\Files\source123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:\Files\source.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

Can you check if you file connection has any PropertyExpression that changes the ConnecftionString back to "C:\Files\source.txt"?

Thanks,
Ovidiu Burlacu

|||

Hi,

There aren't any expressions for the file connections. The way I have the package set up is like this:

STAGE_PACKAGE - Takes the data from a flat file and inserts it in to a staging table.

TRANS_PACKAGE - Takes the data from the staging table and in to the database.

PARENT_PACKAGE - First calls the STAGE_PACKAGE then the TRANS_PACKAGE. An XML config file has been created to capture the location of the file to pick up and the server name to connect to. These are then passed to the STAGE and TRANS packages via parent variables.

I've tried deleting the PARENT_PACKAGE and recreating it, but exactly the same happens.

Richie.

|||I ran into this problem with Parent Package Variables, and switched to Environment Variables instead. You might want to give this a try.|||

This is a known problem of parent package variables. Try to use other venues, like Enviroment variables or registry settings if possible.

Thanks,
Ovidiu Burlacu

|||

That is issue was already documented a while ago; so if somebody is affected by this, go there and vote:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Rafael Salas

|||

Thanks for the replies.

As a work around to this, I decided to do away with the parent variables and set up a config file for the children. I thought that when the parent package is executed, it would call the first child which would then call the config file. But it didn't.

When I run the child package it picks up the config file, but when I run it from a parent package it doesn't pick it up. Is this the same issue as with the parent variables ?

Thanks,

Richie.

|||Anyone ?|||

I am not sure how your packages are set up; but If any of the configuration managers in children packages rely on the value to be received from the parent package to get configured; that simply will no work because the issue I mentioned in my previous post.

How are you running the packages? BIDS, SQL AGENT, DTEXEC?

Take a look to the execution progress to see if there is any warning about configuration no taking place.

Rafael Salas

Package not picking up latest Config File changes

Hi,

I've created a solution with 5 packages in. I've scripted a config file for each package where I would be able to change the source file connection and the database servers (source and destination). On the first package, I am able to change the config attributes and the affects are picked up, but the rest of the packages do no pick up the changes.

For example, if the source file is "C:\Files\source.txt" and this is changed to "C:\Files\source123.txt" (Just to make sure it fails) it wont pick up the new changes and still uses "C:\Files\source.txt".

Also, I tried changing the name of config file itself and the package still ran as if nothing had changed. It's as if it's not recognising the config file.

Any ideas ?

Thanks, Richie.

Can you check if you file connection has any PropertyExpression that changes the ConnecftionString back to "C:\Files\source.txt"?

Thanks,
Ovidiu Burlacu

|||

Hi,

There aren't any expressions for the file connections. The way I have the package set up is like this:

STAGE_PACKAGE - Takes the data from a flat file and inserts it in to a staging table.

TRANS_PACKAGE - Takes the data from the staging table and in to the database.

PARENT_PACKAGE - First calls the STAGE_PACKAGE then the TRANS_PACKAGE. An XML config file has been created to capture the location of the file to pick up and the server name to connect to. These are then passed to the STAGE and TRANS packages via parent variables.

I've tried deleting the PARENT_PACKAGE and recreating it, but exactly the same happens.

Richie.

|||I ran into this problem with Parent Package Variables, and switched to Environment Variables instead. You might want to give this a try.|||

This is a known problem of parent package variables. Try to use other venues, like Enviroment variables or registry settings if possible.

Thanks,
Ovidiu Burlacu

|||

That is issue was already documented a while ago; so if somebody is affected by this, go there and vote:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

Rafael Salas

|||

Thanks for the replies.

As a work around to this, I decided to do away with the parent variables and set up a config file for the children. I thought that when the parent package is executed, it would call the first child which would then call the config file. But it didn't.

When I run the child package it picks up the config file, but when I run it from a parent package it doesn't pick it up. Is this the same issue as with the parent variables ?

Thanks,

Richie.

|||Anyone ?|||

I am not sure how your packages are set up; but If any of the configuration managers in children packages rely on the value to be received from the parent package to get configured; that simply will no work because the issue I mentioned in my previous post.

How are you running the packages? BIDS, SQL AGENT, DTEXEC?

Take a look to the execution progress to see if there is any warning about configuration no taking place.

Rafael Salas

ow to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.
Hi there,
you're stating that you use a datawarehouse. How is the structure in the DW
? Is it a relational star schema ? Some of the idea in using DW's is that
you transfer your data from your OLTP systems as they are not well suited
for reporting because you typically have to join, group and aggregate your
data in very complex ways. The star schema structure is relational as well
but far more intuitive and better suited for reporting. Why AS then ? AS
won't create new tables for you but place the data in cubes which is a
multidimensional storage. The point here is that you preaggreagate data and
by this speed up queries by actually querying your cubes, not the realtional
data. You could say that you're violating a lot of the rules for relational
design to get the better performance. In AS you will work with mesures and
dimensions. This could be Sales per customer. Sales is a measure and
customer is a dimension.
So the process is normally :
OLTP -> Staging -> DW -> Cube <- Report -- Your reports query A.S Cube
I can't figure out whether you are querying your OLTP system now. For
complex reports this is normally a bad idea. But it is hard for me to tell
if A.S is the right way to go not knowing your exact need for output.
Hope this enlighten things a little bit anyway.
Regards
Bobby Henningsen
<rsphere@.gmail.com> skrev i en meddelelse
news:1137857235.371713.224100@.g43g2000cwa.googlegr oups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>

ow to simplify what i'm doing (AS the solution?)

i need to do create reports that perform queries where the results are
percentages of total count divided by counts based on different
subquery where values.
a la ...
select 100 * (totalCount/ (select count where columnA value is x) as
partialCount1) as perc1,
100 * (totalCount/ (select count where columnA value is y) as
partialCount2) as perc2
etc
from myTable where (..bunch of where stuff..)
I have simplified the above example just for brevity here.
So, I hate these kinds of nested queries - and they get incredibly
complex to read. I could break them up into longer scripts to simplify
- but I am wondering if there's another way.
The data I am using is from a warehouse on SQL 2005. I have very little
experience with warehouses - and know little about analysis services
and OLAP.
My question is whether it is possible to use Analysis services to
create new tables that make this data far easier to query? That's the
path I am considering venturing down, but I don't want to hit a dead
end... and need just to know if this makes sense. That is to use
Analysis services to crunch out some new tables from the tables I am
using which would have the results neatly packaged in a table or 2 that
is fast and simple to query. Is that the idea of AS?
thx for helping a learner here. let me know if i am making enough sense
in the question.Hi there,
you're stating that you use a datawarehouse. How is the structure in the DW
? Is it a relational star schema ? Some of the idea in using DW's is that
you transfer your data from your OLTP systems as they are not well suited
for reporting because you typically have to join, group and aggregate your
data in very complex ways. The star schema structure is relational as well
but far more intuitive and better suited for reporting. Why AS then ? AS
won't create new tables for you but place the data in cubes which is a
multidimensional storage. The point here is that you preaggreagate data and
by this speed up queries by actually querying your cubes, not the realtional
data. You could say that you're violating a lot of the rules for relational
design to get the better performance. In AS you will work with mesures and
dimensions. This could be Sales per customer. Sales is a measure and
customer is a dimension.
So the process is normally :
OLTP -> Staging -> DW -> Cube <- Report -- Your reports query A.S Cube
I can't figure out whether you are querying your OLTP system now. For
complex reports this is normally a bad idea. But it is hard for me to tell
if A.S is the right way to go not knowing your exact need for output.
Hope this enlighten things a little bit anyway.
Regards
Bobby Henningsen
<rsphere@.gmail.com> skrev i en meddelelse
news:1137857235.371713.224100@.g43g2000cwa.googlegroups.com...
>i need to do create reports that perform queries where the results are
> percentages of total count divided by counts based on different
> subquery where values.
> a la ...
> select 100 * (totalCount/ (select count where columnA value is x) as
> partialCount1) as perc1,
> 100 * (totalCount/ (select count where columnA value is y) as
> partialCount2) as perc2
> etc
> from myTable where (..bunch of where stuff..)
> I have simplified the above example just for brevity here.
> So, I hate these kinds of nested queries - and they get incredibly
> complex to read. I could break them up into longer scripts to simplify
> - but I am wondering if there's another way.
> The data I am using is from a warehouse on SQL 2005. I have very little
> experience with warehouses - and know little about analysis services
> and OLAP.
> My question is whether it is possible to use Analysis services to
> create new tables that make this data far easier to query? That's the
> path I am considering venturing down, but I don't want to hit a dead
> end... and need just to know if this makes sense. That is to use
> Analysis services to crunch out some new tables from the tables I am
> using which would have the results neatly packaged in a table or 2 that
> is fast and simple to query. Is that the idea of AS?
> thx for helping a learner here. let me know if i am making enough sense
> in the question.
>