Best approach

I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.

Source server SQL 2000 database allows NULL values in columns.

Destination Server also SQL 2000 but the database required a value in each column.

So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.

I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.

Maybe there is a better way. Suggestion and comment appreciated.

Ryan

Try using the T-SQL ISNULL() function in your initial query, replacing any null values with an empty string or whatever...

ISNULL(myField,'')

Beginners Question: How to write this query

Hi -

I feel stupied b/c I can't figure this out but I hope it's just a few seconds for some of the more senior posters of this forum:

I have a table that looks like this:

Table:Request
ID: bigint
... (a bunch of table specific columns)
User_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)
Provider_Status_ID: bigint (holds the bigint ID of the status table, below; always holds the ID to the most recent ID, if several)

and then there is a second table that looks like this:

Table: Status
ID: bigint
RequestID: bigint (the request ID, so I can see all status messages for the request)
Time: Timestamp
Code: byte (there are only a few status messages)

The status table holds both the status for users and providers, as they use exactly the same status code. What I'd like to get is a table/view that looks like this:

RequestID:bigint (the request that this status belongs to)
User_Code: byte (the code of th most recent user status)
Provider_Code: byte (the code of the most recent provider status)

Somehow, I am blanking how to create a query that returns both user and provider codes in one row. Any help greatly appreciated!!!!

Oliver

Why has the Status table the column RequestID?

Otherwise the SELECT you want is along the lines of

SELECT RequestID, U.Code, P.Code FROM Request, Status U, Status P WHERE Request.User_Status_Id = U.Id AND Request.Provider_Code = P.Id

|||

Thanks - that improves my life. I had used multiple views before and it was getting messy ...