How do i implement If Then Else Conditions

Hi All,

I'm very new to Integration Services. Self Learned this ETL tool based on my prior ETL tool knowledge.

Can you tell me how do write IF Then Else conditions for every column in my source and redirect to a single output?

Hey correction here ... i'm using flat file as my source

Thanks in Advance,

Suresh N

It would help if you could provide a more explicit example of the type of logic you're hoping to implement, but here is a response based on my best guess at what you're trying to accomplish.

FIrst, start with the Derived Column transformation in your data flow: http://msdn2.microsoft.com/en-us/library/ms141069.aspx. This transform is the tool of choice for adding new columns - including new columns based on conditional logic and the values in existing columns - to your data flow.

Next, build an expression using the SSIS conditional operator to implement the If Then Else logic you need: http://msdn2.microsoft.com/en-us/library/ms141680.aspx.

You can also use the Script Transformation to perform more complex If Then Else logic in the data flow, but I personally try to avoid this if I can, as it makes your packages more difficult to maintain.

Please let us know if this gives you what you need.

|||

Thanks for reply Matthew.

I'm not able to do. Still needed help.

I have a flat file as a source, for each column i need to detect for NULL values. If it is NULL then i need to populate "99" Else Value.

I'm trying the same in Derived column transformation, not succeeded.

Do SSIS have something where i could write If Then Else ?

thanks..

Suresh N

|||

Suresh,

You can use a derived column transformation in the data flow with an conditional expression like

ISNULL(column1) ? 99 : column1

http://msdn2.microsoft.com/en-us/library/ms137538.aspx

|||

Suresh N wrote:

Thanks for reply Matthew.

I'm not able to do. Still needed help.

I have a flat file as a source, for each column i need to detect for NULL values. If it is NULL then i need to populate "99" Else Value.

I'm trying the same in Derived column transformation, not succeeded.

Do SSIS have something where i could write If Then Else ?

thanks..

Suresh N

This is exactly what the Derived Column transform is designed to do. Can you please post some specific information about what you are doing and what results you're seeing. There's not a lot of helpful information in your post to assist in troubleshooting.

|||

Hi Rafael,

Thanks much for your answer.

This has made my job done.

Regards,

Suresh N

|||

If you have to do this for several files accross a couple of packages then this when I think you should consider a custom transform. Faster development and maintenance as well as the ability to control custom business rules in a single (external to packages) assembly. The use of 99 in a local business rule I believe.

How do I find I am administrator?

This is the first time I will be using SQL server although I have good
experience in FoxPro and ACCESS database, I have limited knowledge of SQL
server.
We have installed SQL server 2000 on WIN2K. Our IT people has installed
Enterprise Manager on my win2000 workstation. How do I find I am in admin
group or not? How do I find what rights I am given?
Can I make changes on package without admin rights or do I have to change
package physically from server where sql server is installed? Is there any
limitation in enterprise manager as client tools and enterprise manager on
server where sql 2000 server is installed?Hi,
How do I find I am in admin group or not? How do I find what rights I am
given?
That depends up on the type of authentication you are using to access SQL
server. If you are normal user and you are using
SQL Authentication, then you can run sp_helplogins to get all the
informations;
How to check:
Login to query analyzer and go to the database you have prev and execute
sp_helpuser <user_name>
If you have admin rights then execute the below command from Master
database.
sp_helplogins <login_name>
Can I make changes on package without admin rights ?
You can create packages, but you can modify the pckage with out knowwing the
package admin password.
Is there any limitation in enterprise manager as client tools and
enterprise manager on server where sql 2000 server is installed?
No, there is no limitations.
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:uimzBx0GEHA.2436@.TK2MSFTNGP09.phx.gbl...
> This is the first time I will be using SQL server although I have good
> experience in FoxPro and ACCESS database, I have limited knowledge of SQL
> server.
> We have installed SQL server 2000 on WIN2K. Our IT people has installed
> Enterprise Manager on my win2000 workstation. How do I find I am in admin
> group or not? How do I find what rights I am given?
> Can I make changes on package without admin rights or do I have to change
> package physically from server where sql server is installed? Is there any
> limitation in enterprise manager as client tools and enterprise manager on
> server where sql 2000 server is installed?
>|||"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:#CzPhl4GEHA.3772@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How do I find I am in admin group or not? How do I find what rights I am
> given?
> That depends up on the type of authentication you are using to access SQL
> server. If you are normal user and you are using
> SQL Authentication, then you can run sp_helplogins to get all the
> informations;
> How to check:
> Login to query analyzer and go to the database you have prev and execute
> sp_helpuser <user_name>
I selected MyTestDB and ran the store proc and it returns 3 records,
extracts from the query results:
GroupName DefDBName
db_owner master
db_accessadmin master
db_ddladmin master
I believe I have admin rights to MyTestDb.
> If you have admin rights then execute the below command from Master
> database.
> sp_helplogins <login_name>
Then I selected master database and ran same sp. I got message "User does
not have permission to perform this action."
What is that means. I do not have admin rights to whole server?

> Can I make changes on package without admin rights ?
> You can create packages, but you can modify the pckage with out knowwing
the
> package admin password.
Is package admin is different than server adminn?
> Is there any limitation in enterprise manager as client tools and
> enterprise manager on server where sql 2000 server is installed?
> No, there is no limitations.
> Thanks
> Hari
> MCDBA
Thank you very much for providing detail information.
>
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:uimzBx0GEHA.2436@.TK2MSFTNGP09.phx.gbl...
SQL
admin
change
any
on
>|||Hi Sunny,
1.What is that means. I do not have admin rights to whole server?
Based on the output you fell inside the 'db_owner' role in your database.
This ensure that you can do any activiites inside that database.
But you not in the part of 'SYSADMIN' role, which is the server wide role.
Due to that your SP_HELPLOGINS failed. This command will list the details of
all Logins who can access SQL server.
2.What is that means. I do not have admin rights to whole server?
Yes, You have full access to only ur database.
3.Is package admin is different than server adminn?
Yes, While saving the package you can mention a Owner password. That might
be needed while accessing the exiting package.
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:#CzPhl4GEHA.3772@.TK2MSFTNGP12.phx.gbl...
SQL
> I selected MyTestDB and ran the store proc and it returns 3 records,
> extracts from the query results:
> GroupName DefDBName
> db_owner master
> db_accessadmin master
> db_ddladmin master
> I believe I have admin rights to MyTestDb.
> Then I selected master database and ran same sp. I got message "User does
> not have permission to perform this action."
> What is that means. I do not have admin rights to whole server?
>
> the
> Is package admin is different than server adminn?
> Thank you very much for providing detail information.
> SQL
installed
> admin
> change
> any
manager
> on
>|||Thanks Hari.
Since I am db_owner, why I am not able to create new store procedure, view
and table? It gives me error "CREATE TABLE permission denied in database
'myTestDB'.
Your suggestion would be great help.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi Sunny,
> 1.What is that means. I do not have admin rights to whole server?
> Based on the output you fell inside the 'db_owner' role in your database.
> This ensure that you can do any activiites inside that database.
> But you not in the part of 'SYSADMIN' role, which is the server wide
role.
> Due to that your SP_HELPLOGINS failed. This command will list the details
of
> all Logins who can access SQL server.
> 2.What is that means. I do not have admin rights to whole server?
> Yes, You have full access to only ur database.
> 3.Is package admin is different than server adminn?
> Yes, While saving the package you can mention a Owner password. That might
> be needed while accessing the exiting package.
> Thanks
> Hari
> MCDBA
>
> "Sunny" <sunny_1178@.hotmail.com> wrote in message
> news:OupOe69GEHA.1368@.TK2MSFTNGP11.phx.gbl...
am
> SQL
execute
does
knowwing
good
of
> installed
there
> manager
>|||Hi Sunny,
It seems, the Administrator has denied access to "Create table" , "Create
Procedure" and "Create View" for your SQL server user using the
DENY statement;
deny create table to <user>
go
deny create procedure to <user>
go
deny create view to <user>
In this case even if you are db_owner for a database you will not able to do
Create table / Create View or Create Procedure.
If required ask your administrator to Grant back those previlages using
GRANT statement.
The below previlages can be denied from a DB_OWNER by administartor;
CREATE FUNCTION
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
BACKUP DATABASE
BACKUP LOG
Thanks
Hari
MCDBA
"Sunny" <sunny_1178@.hotmail.com> wrote in message
news:eiDTikBHEHA.324@.tk2msftngp13.phx.gbl...
> Thanks Hari.
> Since I am db_owner, why I am not able to create new store procedure, view
> and table? It gives me error "CREATE TABLE permission denied in database
> 'myTestDB'.
> Your suggestion would be great help.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eVNtu1$GEHA.1528@.TK2MSFTNGP09.phx.gbl...
database.
> role.
details
> of
might
I
> am
access
> execute
> does
> knowwing
> good
> of
in
> there
>