ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

ColumnNamesInFirstDataRow Expression

I have a SSIS package I am trying to create that will accept two types of files. They are the same exact file except for one contains a header and one does not. So I setup a conneciton manager to the csv file. I then set a variable of bool type, and then assigned that to the ColumNamesInFirstDataRow expressions property of the conneciton manager.

So the pacakge runs. Loops a directory, runs a script that sets the Header variable to true/false based on the file name. But when it gets to the data flow it always ignores the property and never bypasses the header row. The variable is being set. I tried datarowstoskip and set it to 1 instead of the above mentioned property, and that does not work either.

How can I accomplish this?

There is a known bug in evaluation of flat file connection properties that might be causing this. IT is scheduled to be fixed for the next release. Unfortunately, I do not see an easy workaround...

-Bob

|||Is there any know work around to this with .net code or anything else?|||

You could set up two connection managers (one with headers, one without) and two dataflows to match, and use a script task to check the file for a header row. Then use precedence constraints to pick the data flow to execute.

Or you could parse the whole file in a script source, but that could be a lot of work, depending on the complexity of your file.

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column Visibility Expression

I'm trying to show a column only if a certain parameter contains a certain string of characters. So far I've got it working if the parameter is equal to the string of characters by doing

=IIF (Parameters!Param1.Value = "String1", False, True)

but I would like it to work if the Param1.Value contains "String1" ... I tried

=IIF (Parameters!Param1.Value like "%String1%", False, True)

but it doesn't work. Any suggestions?

TIA

Using the InStr function works!|||Hide the item if the string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , True, False)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , False, True)

Show item if string is found:
=IIf(InStr(Parameters!Param1.Value, "string") <> 0 , False, True)
or
=IIf(InStr(Parameters!Param1.Value, "string") = 0 , True, False)
|||

Hello,

I'm using the exact same syntax as given below and I get the error message

=IIf(InStr(Parameters!Measure.Value, "string") <> 0 , False,True)

Error : The Hidden expression for the table 'table1' contains an error: Conversion from Type 'Object()' to type 'String' is not valid

I'm not sure what is wrong here..

Any help would be appreciated!!

Thanks,

Column size

Hello World,
If Len (Expression) return the length of Expression
What will return the size of column ?
Thank's in advance.
MLHow do you define "size of column"? Storage size? If so, use the DATALENGTH(
) function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Michel" <m.landrain@.wanadoo.fr> wrote in message news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl
..
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||The size of a column when created?
use the view INFORMATION_SCHEMA.COLUMNS and look for column
CHARACTER_MAXIMUM_LENGTH
For the length of data stored in a field you can use DATALENGTH
select DATALENGTH( Fielname) ,FieldName from YourTable
http://sqlservercode.blogspot.com/
"Michel" wrote:

> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||A precision,
For exemple,
A column is defined as VARCHAR (10)
The size of column is 10 cars.
What will return 10 (the size of column) ?
"Michel" <m.landrain@.wanadoo.fr> a crit dans le message de
news:u$8G3hpxFHA.736@.tk2msftngp13.phx.gbl...
> Hello World,
> If Len (Expression) return the length of Expression
> What will return the size of column ?
>
> Thank's in advance.
> ML
>
>|||Maybe this example can be of help:
use pubs
select INFORMATION_SCHEMA.[COLUMNS].CHARACTER_MAXIMUM_LENGTH as DeclaredLength
,INFORMATION_SCHEMA.[COLUMNS].CHARACTER_OCTET_LENGTH as ActualLength
from INFORMATION_SCHEMA.[COLUMNS]
where (INFORMATION_SCHEMA.[COLUMNS].TABLE_NAME = 'authors')
CHARACTER_MAXIMUM_LENGTH is what you are looking for, yet consider also the
CHARACTER_OCTET_LENGTH column for unicode data types.
More on this here:
http://msdn.microsoft.com/library/d...br />
87w3.asp
ML
p.s. not the same ML obviously. :)

Column Aliases

I want to refere to an aliased column in an expression in another column. I
have one called get getforename and I want to test if it is null and display
a different value but all's I get is
Invalid column name 'getforerank'. Regards, Chris.
SELECT
dbo.tblCemeteries.CemeteryName,
Surname,
age,
dod,
Forename,
CASE WHEN tblNames.Forename='liz' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
END as getforerank,
case when getforerank is null then 1000
else getforerank
end,
CASE WHEN tblNames.Surname='smith' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
'12/06/2004')
order by getrank,forename
You simply can not use aliases in the manner in which you are trying.
"Chris Kennedy" wrote:

> I want to refere to an aliased column in an expression in another column. I
> have one called get getforename and I want to test if it is null and display
> a different value but all's I get is
> Invalid column name 'getforerank'. Regards, Chris.
> SELECT
> dbo.tblCemeteries.CemeteryName,
> Surname,
> age,
> dod,
> Forename,
> CASE WHEN tblNames.Forename='liz' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
> END as getforerank,
> case when getforerank is null then 1000
> else getforerank
> end,
> CASE WHEN tblNames.Surname='smith' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
> END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
> ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
> WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
> between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
> '12/06/2004')
> order by getrank,forename
>
>
|||Chris,
The SELECT list isn't materialized column-by-column from left to
right, so you can't use a name that's newly created in the select list
from elsewhere within the select list. You have two choices - either
replace getforerank with its definition, or use a nested derived table.
Since getforerank is messy, the derived table is probably an easier
solution. I've replaced your case statement by COALESCE, which should
be equivalent to what you have and less to type.
select
blah,
coalesce(getforerank, 1000),
blah
from (
-- your query except for the column using getforerank
select ...
this,
that,
messyexpression as getforerank
-- do not put coalesce(getforerank,1000) in this select
from ...
) D
Steve Kass
Drew University
Chris Kennedy wrote:

>I want to refere to an aliased column in an expression in another column. I
>have one called get getforename and I want to test if it is null and display
>a different value but all's I get is
>Invalid column name 'getforerank'. Regards, Chris.
>SELECT
>dbo.tblCemeteries.CemeteryName,
>Surname,
>age,
>dod,
>Forename,
>CASE WHEN tblNames.Forename='liz' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
>END as getforerank,
>case when getforerank is null then 1000
>else getforerank
>end,
>CASE WHEN tblNames.Surname='smith' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
>END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
>ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
>WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
>between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
>'12/06/2004')
>order by getrank,forename
>
>

Column Aliases

I want to refere to an aliased column in an expression in another column. I
have one called get getforename and I want to test if it is null and display
a different value but all's I get is
Invalid column name 'getforerank'. Regards, Chris.
SELECT
dbo.tblCemeteries.CemeteryName,
Surname,
age,
dod,
Forename,
CASE WHEN tblNames.Forename='liz' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
END as getforerank,
case when getforerank is null then 1000
else getforerank
end,
CASE WHEN tblNames.Surname='smith' THEN 0
else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
'12/06/2004')
order by getrank,forenameYou simply can not use aliases in the manner in which you are trying.
"Chris Kennedy" wrote:
> I want to refere to an aliased column in an expression in another column. I
> have one called get getforename and I want to test if it is null and display
> a different value but all's I get is
> Invalid column name 'getforerank'. Regards, Chris.
> SELECT
> dbo.tblCemeteries.CemeteryName,
> Surname,
> age,
> dod,
> Forename,
> CASE WHEN tblNames.Forename='liz' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
> END as getforerank,
> case when getforerank is null then 1000
> else getforerank
> end,
> CASE WHEN tblNames.Surname='smith' THEN 0
> else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
> END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
> ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
> WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
> between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
> '12/06/2004')
> order by getrank,forename
>
>|||Chris,
The SELECT list isn't materialized column-by-column from left to
right, so you can't use a name that's newly created in the select list
from elsewhere within the select list. You have two choices - either
replace getforerank with its definition, or use a nested derived table.
Since getforerank is messy, the derived table is probably an easier
solution. I've replaced your case statement by COALESCE, which should
be equivalent to what you have and less to type.
select
blah,
coalesce(getforerank, 1000),
blah
from (
-- your query except for the column using getforerank
select ...
this,
that,
messyexpression as getforerank
-- do not put coalesce(getforerank,1000) in this select
from ...
) D
Steve Kass
Drew University
Chris Kennedy wrote:
>I want to refere to an aliased column in an expression in another column. I
>have one called get getforename and I want to test if it is null and display
>a different value but all's I get is
>Invalid column name 'getforerank'. Regards, Chris.
>SELECT
>dbo.tblCemeteries.CemeteryName,
>Surname,
>age,
>dod,
>Forename,
>CASE WHEN tblNames.Forename='liz' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Forename))
>END as getforerank,
>case when getforerank is null then 1000
>else getforerank
>end,
>CASE WHEN tblNames.Surname='smith' THEN 0
>else(SELECT Rank FROM dbo.tblKeynames WHERE (KeyName = Surname))
>END as getrank FROM dbo.tblNames INNER JOIN dbo.tblCemeteries
>ON dbo.tblNames.Cemeteryid = dbo.tblCemeteries.CemeteryID
>WHERE (KeyNameGroupID = 1) and (dateadd(year,(age-(age*2)),dod )
>between '10/10/1753' and '12/06/2004') and (dod between '10/10/1753' and
>'12/06/2004')
>order by getrank,forename
>
>

Color Expressions

Hey everyone,

I know that you can make an expression that will make it one color if a certain condition is met and a different one if it is not but is there anyway to make it so that if a number is less than another it's one color, if it's greater it's a different color and if they're equal it's a third color? Thanks for the help.

-Keith

Yes. You want to use the switch command in your expression. You can find this and other examples in the following article:

http://technet.microsoft.com/en-us/library/ms157328.aspx

Simone