SSIS SQL char conversion to MS Access Yes/No field

Question

I have an SSIS package I am developing. I am attempting to write data from SQL Server 2005 to MS Access 2007.

I am pretty stumped on how to convert a SQL char(1) field to an Access Yes/No field.

From the information I have gathered, the SQL equivalent of an Access Yes/No field would be a bit field, with values of either 0 or 1.

My SQL char(1) field (which is my source field), contains "N" or "Y". I do not have the option of using a bit field, hence my dilemma. I have tried casting to booleans and integers and haven't had any success.

In my Data flow task, I have tried to create regular expressions to no avail, and derived columns, data transformations, etc to no avail. I am stuck.

I have seen some examples on building conditional statements that loop through the .Row collections. This seems a little overboard, to have to write conditions for NULL checks or string values.

In the MSDN link SSIS Equivalent to DTS Transform Data Task Properties ...it has some code somewhat similar to this:

If DTSSource("Col010") = "Y" Then
  DTSDestination("X") =  -1
 Else
  DTSDestination("X") =  0
End If

Should I be able to manipulate the row values directly, through code? Since the source field is a Yes/No type, does it accept integer values? negative values? Yes is actually "-1" in MS access, and I guess this is confusing me.

It seems that a regular expression would fix this problem but I am unsure how to use that too.

Is there something I am overlooking? There must be a way to convert "CHAR" to "YES/NO".

** I know it is easy to point out here that good ol' 2000 SQL DTS could handle this without any problem, by default, right out of the box, installed by a monkey. I am finding myself spending WAAAAY too much time on the very small quirks of SSIS, such as this Yes/No field problem.

I am unable to find any documentation on the subject of SSIS and MS Access Yes/No fields. MSDN will not allow me to post a question either. I know, poor me :(

Has anyone ever come across this quirk with SSIS and MS Access?

Answer 1

See this topic on MSDN for information about the Derived Column transformation.

It doesn't specifically address what you're trying to do, but some of the examples imply that you should be able to achieve your objective. Set the derived output column's type to DT_BOOL and use a one-line version of the code you have above as the Expression (perhaps with a ternary operator?).

And yes, Access Jet represents True as -1. False = 0, and True is Not False. If you apply a bitwise Not to 0, you get the twos complement version of -1.

Update: The Jet internal storage of a Boolean is as an Integer (go figure--but that's what they chose...). As such, Jet Yes/No fields accept zero (0) for False and non-zero (not just -1) for True. See Comments

Answer 2

To solve this problem, I created a Derived Column.

The regular expression for the column is:

[column] == "Y" ? -1 : 0

The datatype is:

single-byte signed integer [DT_I1]

and viola! Jet (aka MS Access, Thanks David W. Fenton!), now displays my Yes/No fields.

After noticing that only one record was appearing in the table, I noticed my test data was invalid and corrected the underlying issue.

This is good documentation on how to cast SQL char(1) fields (with "N" or "Y" values) to MS Access (oooops, I meant Jet) Yes/No fields.

Thank you for everyone's help. Stackoverflow is invaluable.