config file question

In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.

I just want to be sure I don't need both for my package to run successfully.

You can add only 1 or as many configuration values as you want in a single file. As a good practice you should have both values set via configurations; just to cover a possible change in the flat file location. You could also create a separate config file for each property.

Concatenating Fields With NULL Values

Hey everyone,

This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.

Here is my current SELECT statement:

SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.

The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.

Can someone tell me what I am missing? Is there a function that I can use for this?

Thanks,

Use ISNULL ( check_expression , replacement_value ) function

SELECT ISNULL(m.FName,'') + SPACE(1) + ISNULL(m.MName,'') + SPACE(1) + ISNULL(m.LName,'') + ', ' + ISNULL(m.Credentials,'') AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

|||

Hmm, I had never seen the SPACE function :) Only thing to suggest to your code is that you probably want to include the SPACE function calls in the ISNULL to get rid of extra useless spaces:

ISNULL(m.FName + SPACE(1),'')

|||

Thanks to both of you. I have even used ISNULL in the past. I feel pretty stupid now.

The reason I am using SPACE(1) is because I have seen SQL treat ' ' as an empty string. I think it has to do with sp_dbcmptlevel.

|||

Here's what I actually ended up doing:

SELECT REPLACE(RTRIM(m.FName + SPACE(1) + ISNULL(m.MName, '')) + SPACE(1) + m.LName + ', ' + ISNULL(m.Credentials, '*'), ', *', '') AS Name
FROM tblMembers m
WHERE m.DRGInclude = 1

The RTRIM gets rid of the extra space when MName is NULL, and to get rid of the ', ' when Credentials is null, I used the REPLACE function.

|||

Check out the property 'CONCAT_NULL_YIELDS_NULL'

you can turn it OFF by the following command

SET CONCAT_NULL_YIELDS_NULL OFF
GO

Thanks,
Loonysan