Configuration Manager ?

Each time you start a SSIS project you have Solutions Configuration showing "Development". In the drop down box you can select "Configuration Manager..."

Can someone tell me the use of this, and is it related to the package configurations somehow. I've tried to create new Solution Configurations like "Test" and "Production" with the purpose of binding different configuration files and deplymentfolders to each Solution Configuration but still it seems like this isn't the way i should be used...

This is something inherent to Visual Studio rather than BIDS or SSIS. It doesn't have any relevance to package configurations that we know and love (!!!).

I don't know how this VS feature is supposed to be used so I say whether it would be useful for SSIS or not. I'd like to know though!!!

-Jamie

|||

The only thing i could see any use of this was to create 3 enviroments - Development, Test and Production.

I each of those i'll set the outputpath to bin\Development, bin\Test, bin\Production

So when i build the package i can choose the enviroment to build it to so that i can keep those files sepperate....

|||

Is there a way to reference the current value within a variable ?

This would be helpfull for having the servername depend on the selected configuration.

Thanks in advance,

Geert

|||

What you are asking and what cpgl described seems totally unnecessary if you only use the built in Configuration support for SSIS. Things like file paths are server names should be changed through configurations, not coded into the package during "build".

I would expect each environment to have the required configiration resources in place, so there should be nothing to change in the package to prepare it for an environment. If you do change the package between environments it just defeats the purpose of having a test environment as what you tested is not what you will then promote to production. It may be a minor change, but being strict, no changes should be allowed.

The only exception I can see is when environments are combined on machines, and for that I'd recomend passing in a parameter through the execution host, e.g. DTEXEC /SET

Conditionally showing/hiding a parameter

I have a report that in most cases takes 4 parameters. In a few special cases, though, it takes 5 parameters. I have my code modified enough such that it can handle this exception, however I cannot figure out how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

how to conditionally show the 5th parameter based on the 4th parameter. Is this possible?

Yes, this is possible. Lets say your 5th parameter is driven off of a query, then you can pass what ever is selected in the 4th parameter as an input to the query. Also in the report parameter your 5th parameter should be below the 4th parameter meaning the order of the parameters is important.

Let me know if you would want me to elaborate this further.

|||

You're describing how to change the available values of the 5th parameter based on the choice made for the 4th parameter. I think the original question is it possible to show or hide the 5th parameter based on the choice made for the 4th parameter.

I have never found a way to conditionally hide parameters and don't believe it's possible. If I'm wrong then I'd love to hear how to do it. When we've encountered cases like this we've had to just change the values of the 5th parameter (as Techquest has described) to have only a single item available such as "n/a" and then defaulted to that value.

I'd also love to hear if there's a way to conditionally change the label for parameters. I don't believe this is possible either but there's a lot of smart people out there and it would be great if someone could show me that I'm wrong.

tia

-bruce

|||Bruce, that's what I wanted to do.

I haven't been working with SSRS for too long, I wonder if it's possible to write a script or extension to allow more flexibility in the parameters.

I like the N/A idea though, that'll work if I can't figure out anything new.

Thanks,

Erik

Conditional sum based on visibility

Hi,

I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.

My issue is that the non visible entries are still being added to my Sum statement at the end of the report.

I need a way to exclude an entry based on its visibility.

Any help would be greatly appreciated.

Are you using the Previous aggregate to get the previous entry? If you are not, then you can add a conditional, using the same expression for determining the visibility, to the SUM aggregate. For example, =SUM(IIF(HiddenExpression, 0, Fields!FieldName.Value))|||

Thanks for the response.

I am using the Previous function to control the visibility. The report needs to show the first entry for each particular company code, but not the duplicate entries.

For example: =Previous(Fields!Company_code.Value) = Fields!Company_code.Value

I tried adding a conditional with the same expression to determine if it should be included in the Sum, but that does not work.

Any other suggestions?

Thanks!

|||

Try handling it in the code (Report -> Report Properties -> Code)

Declare a public shared variable (integer/float) in the code and write a public function to sum up the values based on current company code and previous company code. Your code will look something like this in VB.Net:

Public Shared SumTotal as Integer

SumTotal = 0

Public Function CalculateSum(isCompanyCodeSame as Boolean, FieldValue as Integer) As String

If isCompanyCodeSame = False Then

SumTotal = SumTotal + FieldValue

End If

CalculateSum = ""

End Function

and

append this expression to any of the textboxes in your detail row:

Fields!FieldName.Value & Code.CalculateSum(Fields!Company_code.Value=Previous(Fields!Company_code.Value), Fields!FieldToBeSummed.Value)

And use Code.SumTotal to get the sum.

Shyam

|||

Thanks for the code!

It is working correctly now.

|||

I used the same method to stop displaying rows in a table after the 10th row. It works fine in VS 2005 but it acts wierd when I publish it to the production server. I count up the rows that are visible...

Public Shared VisibleRowTotal as Integer=0

Public Function CountVisibleRow(isVisible as Boolean) As String
If isVisible = False Then
VisibleRowTotal = VisibleRowTotal + 1
End If
CountVisibleRow = ""
End Function

Then I added a column in my report table to call the code...

=Code.CountVisibleRow(ReportItems!textbox54.Value) & " " & Code.VisibleRowTotal

Then I based my row visibility on the code value.

=IIF(Code.VisibleRowTotal>=10, True,False)

To get it to work the first time I had to rename the original report on the production server and then upload the new report. Once several users start hitting the report then no rows are visible or it's intermittent.

I'll admit I've have never used custom code in a report before. Is there something different I need to do when uploading an rdl with custom code? Am I handling the custom code properly?

|||
You should change the VisibleRowTotal variable to not be Shared. Having it be shared or static will cause each instance of the report to share the same total value. So, removing the modifier will allow each report instance to execute independently from one another.

Ian|||

Thanks Ian.

I had the issue where my totals were correct for the first time the report loaded, but the totals just kept incrementing when I used different filters on the report.

Taking Shared off of the variable resolved the issue.

Conditional statement with a cast from string to date

My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error.

[Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]

What's the error?|||

I modified it to the following but I get an error when I try to debug.

[Column 10] == "01/01/0001" ? (dt_date)" 01/01/1801" : [Column 10] <= "12/31/1801" ? (dt_date)"12/31/1801" : (dt_date)[Column 10]

Error message is:

...conversion between types dt_str and db_timestamp is not supported

|||What is the output column data type specified as in the derived column?|||Where do I check that? I only see the input defined in the derived column transformation which is dt_string 50. The column is defined as datetime in the table.|||http://ssistalk.blogspot.com/2007/01/derived-column.html

I would expect to see the data type of the derived column be DT_DBTIMESTAMP (or DT_DBDATE). The expression should be:

[column10] == "xxxxxx" ? "01/01/1801" : ......

Make sure "Derived Column" is set to "add as new column".|||I don't want to add it as a new column. I am using the conditional statement instead of the SQL case statement. I have dates from Oracle that are outside SQL's range that I need to convert.|||Right, but you can't replace the column because it's a DT_STR.... So if you want a date data type, you need to have a new column. This is the proper way to do it. Then in the data flow, you just ignore [column10] and use [DateColumn10], for instance.|||I tried that but now I get the same error for my new column10.|||

RMooreFL wrote:

I tried that but now I get the same error for my new column10.

Okay, I've posted a new example.

http://ssistalk.blogspot.com/2007/01/derived-column.html