derived column transformation expression

Hi.

I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string:

SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]

This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric

I had something like this in mind:

SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno]

but the syntax is incorrect.

Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax!

Thanks

CODEPOINT(@.[User::CCC]) >= 47 && CODEPOINT(@.[User::CCC]) <= 57 ? @.[User::CCC] : SUBSTRING(@.[User::CCC], 2, LEN(@.[User::CCC]) - 1)|||

Excellent, works a treat!

Thanks very much

sql

Deployment problems - no components for DataFlow

I’ve created a SSIS package that has 2 tasks. The first is selecting some information from a SQL Server table and the second is exporting this information to a flat file.

I created the package on my desktop client and I am able to run it successfully in Debug mode from my client. The information is selected from the LIVE SQL Server database and the file is created on the LIVE SQL Server.

I have built the package for deployment, copied it to the LIVE SQL Server and have run the manifest file to deploy it.

I’ve logged into the SSIS on the LIVE server and the package is listed there. However when I right click on it and run it I get some error messages indicating that the dataflow tasks have no components, presumably the select statement and the export to the flat file.

It’s as if when I build the package the data components are not being included or when I deploy it on the LIVE server the data components are not being deployed.

Does anyone have suggestions?

Could you report the actual error message you are getting? Empty data flows only generate warnings, not errors.|||

The exact error is:

DocumentL Listing

Validation has started.

Export Document Listing

Validation has started

Warning: The DataFlow task has no components. Add components or remove the task

Validation is completed

Validation has started

Warning: The DataFlow task has no components. Add components or remove the task

...

|||

Rob,

I know about this problem only too well. Are you using a source control system?

-Jamie

|||

We are using Visual source Safe, but I do not believe that it is installed on any of the computers discussed here.

Are you saying that this may be a problem with the Source Safe integration with SQL 2005?

|||

Rob Woods wrote:

We are using Visual source Safe, but I do not believe that it is installed on any of the computers discussed here.

Rob Woods wrote:

So that package that we're talking about. Does it ever get stored in SourceSafe?

Are you saying that this may be a problem with the Source Safe integration with SQL 2005?

Not exactly. There's a problem with all source control systems when used in conjunction with the SSIS Designer - I have seen it happen with SourceSafe and TFS. The SSIS team are aware of it and are investigating. Long answer is that the contents of the data-flow doesn't get serialized correctly into the .dtsx file due to some sort of problem when used with a source control system. Short answer is that there is a bug.

I really should blog this

-Jamie

|||So is there a work around?|||

Rob Woods wrote:

So is there a work around?

Best I can offer is that seeing as you are using a source control system you can go back and find the most recent version that still contains all the components, and redo all of your changes. Not great!

-Jamie

|||

Jamie Thomson wrote:

I really should blog this

And now I have done: http://blogs.conchango.com/jamiethomson/archive/2007/04/24/SSIS_3A00_-Bug-when-using-source-control-systems.aspx

-Jamie

Deny Create Database in SQL Server 2000

Hello again, everyone!
I know it's possible to deny users to create databases in SQL Server 2005 by
looking at the server permssions and selecting "Deny" next ot "Create
databases".
I was wondering, is there any way to deny public the ability to create
databases in SQL Server 2000? I can see where you can revoke soem options on
the database level, but not at the instance level.
Thanks in advance!> I know it's possible to deny users to create databases in SQL Server 2005
> by
> looking at the server permssions and selecting "Deny" next ot "Create
> databases".
Users do not have CREATE DATABASE permission unless you GRANT that
permission, either explicitly or by role membership. In SQL 2005, CREATE
DATABASE is implied by CREATE ANY DATABASE and ALTER ANY DATABASE.
Be mindful that DENY and REVOKE are different things. DENY prevents a
previously granted permission from being inherited. When both GRANT and
DENY permissions are present, DENY takes precedence. REVOKE removes all
previous GRANTs and DENYs on the principal so the result is no permissions.
It's usually best to use only GRANTs unless you have a specific reason to do
otherwise.
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam St. Pierre" <AdamStPierre@.discussions.microsoft.com> wrote in message
news:DF656F09-EC02-4C4E-9785-C255CC075706@.microsoft.com...
> Hello again, everyone!
> I know it's possible to deny users to create databases in SQL Server 2005
> by
> looking at the server permssions and selecting "Deny" next ot "Create
> databases".
> I was wondering, is there any way to deny public the ability to create
> databases in SQL Server 2000? I can see where you can revoke soem options
> on
> the database level, but not at the instance level.
> Thanks in advance!|||Thank you, Dan.
As I didn't set up this 2000 instance, some else must have granted
permission. And correct me if I'm wrong, but didn't the default install of
2000 grant permission to everyone, and it's 2005 that you have to grant that
permission explicitly?
And my apologies for my poor choice of wording. I didn't mean "Revoke" as in
the permission setting - I meant the verb, as in to take away. "Deny" would
have been a more descriptive word choice. :D
- Adam
"Dan Guzman" wrote:

> Users do not have CREATE DATABASE permission unless you GRANT that
> permission, either explicitly or by role membership. In SQL 2005, CREATE
> DATABASE is implied by CREATE ANY DATABASE and ALTER ANY DATABASE.
> Be mindful that DENY and REVOKE are different things. DENY prevents a
> previously granted permission from being inherited. When both GRANT and
> DENY permissions are present, DENY takes precedence. REVOKE removes all
> previous GRANTs and DENYs on the principal so the result is no permissions
.
> It's usually best to use only GRANTs unless you have a specific reason to
do
> otherwise.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||> And correct me if I'm wrong, but didn't the default install of
> 2000 grant permission to everyone, and it's 2005 that you have to grant
> that
> permission explicitly?
In SQL 2000, only sysadmin and dbcreator role members have CREATE DATABASE
permission by default.

> And my apologies for my poor choice of wording. I didn't mean "Revoke" as
> in
> the permission setting - I meant the verb, as in to take away. "Deny"
> would
> have been a more descriptive word choice.
Gotcha.
Hope this helps.
Dan Guzman
SQL Server MVP
"Adam St. Pierre" <AdamStPierre@.discussions.microsoft.com> wrote in message
news:8634A52A-D30A-4310-BBD8-6FC2CA316E06@.microsoft.com...[vbcol=seagreen]
> Thank you, Dan.
> As I didn't set up this 2000 instance, some else must have granted
> permission. And correct me if I'm wrong, but didn't the default install of
> 2000 grant permission to everyone, and it's 2005 that you have to grant
> that
> permission explicitly?
> And my apologies for my poor choice of wording. I didn't mean "Revoke" as
> in
> the permission setting - I meant the verb, as in to take away. "Deny"
> would
> have been a more descriptive word choice. :D
> - Adam
>
> "Dan Guzman" wrote:
>