Converting empty string to Null when inserting/updating

I am using the following query to calculate date differences:
select ........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM ....

I have stored all my dates as NVARCHAR because of the issues with localization.
If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table areNULL and they produce the correct result.

Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.
I'd like to use it as a trigger when inserting or updating to convert empty strings to NULL
before the values are inserted.

Thanks guys.

You REALLY should store your dates as a datetime. There is no localization "Problem" with datetimes if you use them correctly, and you can't sort and/or generate good indexes if they are stored in a nvarchar field (Unless you specifically use the YYYYMMDD or YYYY-MM-DD format).

That aside, yes, try NULLIF() like:

INSERT INTO MyTable(col1) VALUES (NULLIF(@.val1,''))

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

ConvertEmptyStringToNull

I have a DB table with a varchar field that doesn't allow nulls, but the SqlDataSource I use to update the row wants to put a null in in exchange of an empty string when the field is empty.

<asp:Parameter Name="comment" ConvertEmptyStringToNull="false" Type="string" /
I even tried settting ConvertEmptyStringToNull to "false" but got nowhere.

I can do this by directly by handling the Updating event of the SqlDataSource, but that's a lot of code. Is there a better way?

I don't know whether this is what you are looking for. Use a space for the DefaultValue.

<UpdateParameters>

<asp:ParameterName="comment"DefaultValue=" "Type="String"/></UpdateParameters>|||

You have to set to ConvertEmptyStringToNull="false" on both the Parameter, and the BoundField control.

For example:

<

asp:BoundFieldDataField="locationName"HeaderText="Location Name"SortExpression="locationName"ConvertEmptyStringToNull="false"/>

<

asp:ParameterName="locationName"Type="String"ConvertEmptyStringToNull="false"/>|||

Hi,

This was a great help, I have been teaching myself ASP.NET and it was the first stumbling block. I was following "SAMS Teach Yourself ASP.NET (24Hrs)" and was on the datagrid view section. It was advising me to set the column value (which was set to not allow nulls) to still be able to except a blank entry. This was supposedly achieved by editing the value via the smart tag and then setting the ConvertEmptyStringToNull, which I did. But still failed with the error "Null expected".

I followed the manual method described above and edited the source code and it worked!

My question is if this is a bug with Visual Studio Express? That is the smart tag function is not updating the Parameter. I had to manually edit the source code in order to get it to work as above. Are there other areas that Visual Studio does not via Design view update the source code side of things?

Regards X

|||

Hi exup,

There are a lot of demos showing how complex web pages can be done without digging into code. The problem is that, unless you want your pages to perform exactly like the demos, you will need to dig into code.

"Teach yourself asp.net in 24 hours" as a book title sets up a somewhat unrealistic expectation. Asp.net is complicated, and needs time to master. I'd give yourself a few months to get comfortable with all of the quirks.

John

convert unicode to greek

hello
i have a table like this
CREATE TABLE [dbo].[test1] (
[nom] [nchar] (10) COLLATE Greek_CI_AI NULL ,
[prenom] [nchar] (10) COLLATE Greek_BIN NULL
) ON [PRIMARY]
GO
i insert some string in greek caracter in it but when a execute a select a
have a result in latin
could you help mepierreyves,
What are you using to view the data?
Is your tool what is mapping the Greek Characters to Latin when you look at
them?
Russell Fields
"pierreyves" <pierreyves@.digitallife.be> wrote in message
news:%23G7FzVTfDHA.1764@.TK2MSFTNGP09.phx.gbl...
> hello
> i have a table like this
> CREATE TABLE [dbo].[test1] (
> [nom] [nchar] (10) COLLATE Greek_CI_AI NULL ,
> [prenom] [nchar] (10) COLLATE Greek_BIN NULL
> ) ON [PRIMARY]
> GO
> i insert some string in greek caracter in it but when a execute a select a
> have a result in latin
> could you help me
>

Convert Null Values to 0

Hi

I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.

In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])

This does not seem to work in MS SQL. Is there some equivalent function I could use?

Thanks

David

David:

The best way to convert a null to a zero is to use ISNULL( [Client Count], 0 ) or COALESCE( [Client Count], 0 ).

|||How to convert null to zero ina query.

Convert Null Values to 0

Hi

I've got a view which returns Null values due to an left outer join. The field which sometimes returns Nulls is called ClientCount. I want to create a new field which displays the value of ClientCount if ClientCount is not null and 0 if it is null.

In MS Access I use the following: IIF([ClientCount] Is Null,0,[ClientCount])

This does not seem to work in MS SQL. Is there some equivalent function I could use?

Thanks

David

David:

The best way to convert a null to a zero is to use ISNULL( [Client Count], 0 ) or COALESCE( [Client Count], 0 ).

|||How to convert null to zero ina query.

Convert NULL Values

Hi all,

I am trying to convert all the NULL values in a column to "Open". Any
ideas??

TIATry:

UPDATE MyTable
SET MyColumn = 'Open'
WHERE MyColumn IS NULL

--
Hope this helps.

Dan Guzman
SQL Server MVP

"GuyInTn" <christopher@.NOSPAMreardenweb.com> wrote in message
news:iijstv4n70v7tcqac4t67o5a5k8mpjaopg@.4ax.com...
> Hi all,
> I am trying to convert all the NULL values in a column to "Open". Any
> ideas??
> TIA|||Thanks, I knew it was something simple.

On Tue, 16 Dec 2003 01:22:30 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>Try:
> UPDATE MyTable
> SET MyColumn = 'Open'
> WHERE MyColumn IS NULL

Convert NULL value to INTEGER

hi all,
i have a problem with converting NULL value INTEGER.
i need to convert NULL value to 0 (zero)
this is my sample query :
SELECT SUM( qty ) AS qty FROM products WHERE id = 12121
i've tried these to
SELECT CONVERT( INT, SUM( qty ) ) AS qty FROM products WHERE id = 12121
SELECT CAST( SUM( qty ) AS INTEGER ) AS qty FROM products WHERE id = 12121
the problem came up when querying unexisting data ( 12121 is not
exists ).
can you help me.
thx,
aCeSELECT COALESCE(SUM( qty ),0) AS qty FROM products WHERE id = 12121
SELECT ISNULL(SUM( qty ),0) AS qty FROM products WHERE id = 12121
COALESCE is preferred, as it is standard SQL. It is also more
flexible than ISNULL as it can except more than two parameters; it
returns the first non-NULL parameter.
Roy Harvey
Beacon Falls, CT
On Wed, 24 Oct 2007 11:12:26 -0700, aCe <acerahmat@.gmail.com> wrote:
>hi all,
>i have a problem with converting NULL value INTEGER.
>i need to convert NULL value to 0 (zero)
>this is my sample query :
>SELECT SUM( qty ) AS qty FROM products WHERE id = 12121
>i've tried these to
>SELECT CONVERT( INT, SUM( qty ) ) AS qty FROM products WHERE id =>12121
>SELECT CAST( SUM( qty ) AS INTEGER ) AS qty FROM products WHERE id =>12121
>the problem came up when querying unexisting data ( 12121 is not
>exists ).
>can you help me.
>thx,
>aCe

Convert NULL to zero

One table I am working with has several money columns. Some are null. I need
to add the columns, but the result is NULL since some of the columns are
NULL.
How can I write the query to convert to zeroes?
Thanks.SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
FROM Sometable
--
David Portas
--
Please reply only to the newsgroup
--
"Paul" <nospam@.please.com> wrote in message
news:%23nSQbLK1DHA.2336@.TK2MSFTNGP09.phx.gbl...
> One table I am working with has several money columns. Some are null. I
need
> to add the columns, but the result is NULL since some of the columns are
> NULL.
> How can I write the query to convert to zeroes?
> Thanks.
>|||Thank you. I'll try that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> FROM Sometable
> --
> David Portas
> --
> Please reply only to the newsgroup|||You can also do it with ISNULL:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
--
Rohtash Kapoor
http://www.sqlmantra.com
"Paul" <nospam@.please.com> wrote in message
news:OtDfAjK1DHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thank you. I'll try that.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:AL-dnQNnTNpeqWai4p2dnA@.giganews.com...
> > SELECT COLAESCE(col1,0) + COALESCE(col2,0) + ...
> > FROM Sometable
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
>|||Since the SUM aggregate ignores NULLs anyway you will save some processor
cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with the
specific case where all values in the column are NULL.
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
COALESCE is an ANSI Standard SQL function which is why I usually prefer to
use it in preference to the proprietary ISNULL function.
--
David Portas
--
Please reply only to the newsgroup
--|||You are very right.
Instead of this:
SELECT SUM(ISNULL(col1,0)), SUM(ISNULL(col2,0)) + ...
FROM TestTable
We should write:
SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
FROM TestTable
However, I prefer to use ISNULL than COALESCE because I can type it quickly
(...just kidding..)
--
Rohtash Kapoor
http://www.sqlmantra.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:9O-dnaPUQsGe32aiRVn-vA@.giganews.com...
> Since the SUM aggregate ignores NULLs anyway you will save some processor
> cycles by putting ISNULL (or COALESCE) outside the SUM. This deals with
the
> specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Since the SUM aggregate ignores NULLs anyway you will save some
> processor cycles by putting ISNULL (or COALESCE) outside the SUM. This
> deals with the specific case where all values in the column are NULL.
> SELECT ISNULL(SUM(col1),0), ISNULL(SUM(col2),0) + ...
> FROM TestTable
> COALESCE is an ANSI Standard SQL function which is why I usually prefer to
> use it in preference to the proprietary ISNULL function.
On the other hand, if you do this and run with ANSI_WARNINGS enabled, you
will get the message "Warnings: null eliminated from aggregate".
(ANSI_WARNINGS is enabled for default, unless you are using DB-Library.)
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Convert Null to 0

Hi,

What would be the Syntax in a SELECT statement to convert a Null Value to 0,

ie,

SELECT RowID, Amount FROM TableA

and Amount is a smallmoney field. For example it is coming back with:

RowID Amount
 1 2.3000
 2 15.0300
 3 NULL
 4 7.8900

But I want it to return:

RowID Amount
 1 2.3000
 2 15.0300
 3 0.0000
 4 7.8900

ThanksYou can use IsNull() for this, e.g. IsNull(Amount, 0).|||Thanks, that is what I was looking for. Works great

convert null to 0

I am trying to convert a value of null to 0. Can I do ot in the VIEW i created. I am calling from the VIEW from a query on my page and using a datareader to populate a datagrid. The error I get is

Operator is not valid for type 'DBNull' and type 'Decimal'.
<<itemtemplate><%# String.Format("<font color="darkred">{0:c}</font>", (DataBinder.Eval(Container.DataItem, "MyDecimal"))*(DataBinder.Eval(Container.DataItem, "price"))) %>
Line 81: </itemtemplate>
Line 82: </asp:templatecolumn>

Thanks for any helpOH,
The sql function ISNULL(mydecimal,'0') as my decimal
thanks anyway!!|||use

isnull(price,0)

in the query

hth

Convert MS acess to SQL Server

if (!Page.IsPostBack)

{ if (Session["users"] != null && (Session[flag"] == "true"))

{

String IP = Request.ServerVariables["remote_host"].ToString();

String Datee = DateTime.Now.Date.ToString();

OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source="

+ HttpContext.Current.Server.MapPath("~/App_Data/result.mdb"));

OleDbCommand cmd = new OleDbCommand("Insert Into KullaniciSayisi (IP,Datee) Values (IPDatee)", con);

cmd.Parameters.AddWithValue("IP", IP);

cmd.Parameters.AddWithValue("Datee", Datee);

con.Open();

intresultt = cmd.ExecuteNonQuery();

con.Close();

Session["flag"] = "false";

}

}

I want to convert this code to SQL sever . However I can not do it? Can you help me?

Hi,

1) Change OleDbConnection to SqlClient.SqlConnection

2) Change OleDbCommand to SqlClient.SqlCommand

3) Change the connection string to connect into your local SQL server, you can get more info from www.connectionstrings.com

That is all what you need.

|||The name "IP" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.|||

I convert however I have a problem. my code do not work :(

String IP=...;

String Datee=DateTime.Now.ToShortDateString();

............

SqlParameter paramIP=new SqlParameter("@.IP",SqlDbType.varchar,50);

SqlParameter paramdate=new SqlParameter("@.IP",SqlDbType.datetime);

paramIP.value=IP;

paramdate.value=Convert.ToDateTime(Datee).ToShortDateString();

cmd.Parameters.AddWithValue("@.IP",paramIP);

cmd.Parameters.AddWithValue("@.date",paramdate);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

When I debug the code cmd.ExecuteNon.Query() give problem. In my db date type is Datetime, IP is String. Can you help me?

|||Post the whole code and the exception as well.|||

if (!Page.IsPostBack)

{

if (Session["ziyaretci"] != null && (Session["kontrol"] == "true"))

{

try{

String IP = Request.ServerVariables["remote_host"].ToString();

String Tarih = DateTime.Now.Date.ToString();

SqlConnection= my connection string

SqlCommand cmd = new OleDbCommand("Insert Into userss (IP,Datee) Values (IP,Datee)", con);

SqlParameter paramIP=new SqlParameter("@.IP",SqlDbType.varchar,50);

SqlParameter paramdate=new SqlParameter("@.IP",SqlDbType.datetime);

paramIP.value=IP;

paramdate.value=Convert.ToDateTime(Datee);

cmd.Open();

int result = cmd.ExecuteNonQuery();

cmd.Close();

Session["kontrol"] = "false";

}

catch(Exception ex){

Response.Write("There is a problem" + ex)

}

}

}

This is my all code.

|||

Hi,

Change the followings

SqlCommand cmd = new OleDbCommand("Insert Into userss (IP,Datee) Values (@.IP,@.Date)", con);

SqlParameter paramIP=new SqlParameter("@.IP",SqlDbType.varchar,50);

SqlParameter paramdate=new SqlParameter("@.Date",SqlDbType.datetime);

|||

Thanks for your helping.

Convert Date Format of 00-XXX-00 to NULL

Let me start by saying that I'm brand new to SQL Server 2005 and SSIS.
I'm using the import wizard in SQL2005 to import from a flat file into a table and everything works fine except for dates. A typical date in my flat file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The flat file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"

The value of 00-XXX-00 means that there is no purchase date.

I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.

The table Data Type is datetime.

If I use the import wizard using the example above I get this error message:

- Copying to [cpstest].[dbo].[date] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
(SQL Server Import and Export Wizard)
Error 0xc020901c: Data Flow Task: There was an error with input column "DateOfLastOrder" (32) on input "Destination Input" (26). The column status returned was: "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (26)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (26)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - date" (13) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
(SQL Server Import and Export Wizard)

If I remove the 00-XXX-00 values and import something like this:

"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"03-DEC-05"

The import is successfull and the dates look correct in a querry.

SELECT *
FROM date

date
--
2006-01-01 00:00:00.000
2006-01-02 00:00:00.000
2005-12-03 00:00:00.000

(3 row(s) affected)

Does anyone know how I should go about getting these date columns into a datetime table and convert the 00-XXX-00 values into NULLs?

Thank you,

Ryan

Ryan,

The import wizard gives you the option to save the package. You should do this and then open it up in Business Intelligence Development Studio (BIDS) so that you can edit it.

In there you will find a data-flow task which is the thing that does the work. It is made up of things called components which can change the data before it gets inserted to the destination.

You need to introduce a component called a Derived Column component. That can take some input data and change it in-memory. It uses an expression language to do this. In your case the expression wants to be something like:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

Hope that helps!

-Jamie

|||Could you please break down what that expression does or point me to a reference for the expression language?
|||

Don't mean to butt in here, but what Jamie has listed here:

Jamie Thomson wrote:

SUBSTRING(<input-col>, 4, 3) == "XXX" ? NULL(DT_STR) : <input-col>

-Jamie

uses the SUBSTRING function (like similar functions in VB or C#) and it returns a subtring of the string <input-col> starting at the 4th character of the string and returns 3 characters. In your situation where your specific date that was throwing an error, you would want to look for the ones with "XXX" at this position.

So if the substring of your date column equals "XXX" then return a null value (null of type string) else return your original column value.

The "?" and ":" are somewhat equivalent to "then" and "else." and in this expression language, the "if" is implied.

Hope this helps.

Mark

http://spaces.msn.com/mgarnerbi

|||

There is a very good expression reference in BOL. In fact, that is the only reference seeing as it is rather good - another one aint really needed.

-Jamie

Convert character to NULL

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Under this scenario, is that the only value in the column? Or are you searching/replacing (.) with NULLs?|||

I am using a conditional evaluation in the derived column transformation:

[Coulmn1] == "." ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : [Column1]

The columns would never have a value that contains a dot, only values or the dot

|||

crancilio wrote:

I am loading a flat file to a table but I also need to scrub the data a bit before the data hits the table. The main update required is converting a dot (.) character to a null value. The source file is using this character to indicate a blank. I know I can use the Dervived Column Transformation, but I have quite a few columns which will take a while to manually configure. Is there another transformation option that anyone can point me to?

Thanks

Not that I am awre of. I am afraid you have to do the same thing for every column affected by that logic. Perhaps, you could try to use an script component where you could use the magic of copy and paste...

|||Let's use a script component instead of a derived column. You'll be very happy with the following solution:

Instead of the derived column, add a script component, set it to be a transformation.

Select the columns you wish to work with. Set their usage types to "ReadWrite." ONLY select the columns you wish to process with this logic.

Below is the script. I don't understand it fully, and I've hacked something that our forum user, jaegd wrote:

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports System.Text
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1
'Credit has been given where credit is due
'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

Public Class ScriptMain
Inherits UserComponent

Private inputBuffer As PipelineBuffer
Private cols As Dictionary(Of Int32, ColumnInfo) = New Dictionary(Of Int32, ColumnInfo)
Private currentColumnInfo As ColumnInfo = New ColumnInfo

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
'Setup control counter
Dim counter As Integer = 0
'Loop through segments
'MsgBox(currentColumnInfo.colIndex.ToString)
For Each currentcolumn As KeyValuePair(Of Int32, ColumnInfo) In cols
'MsgBox(inputBuffer.GetString(currentcolumn.Key))
If (inputBuffer.GetString(currentcolumn.Key)) = "." Then
inputBuffer.SetString(currentcolumn.Key, Chr(0))
End If
Next

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
' Get the Pipeline Buffer for subsequent ordinal column access
inputBuffer = Buffer
MyBase.ProcessInput(InputID, Buffer)
End Sub

Public Overrides Sub PreExecute()
BuildColumnDictionary()
End Sub

Private Sub BuildColumnDictionary()
Dim indexes() As Integer
Dim input As IDTSInput90
Dim col As IDTSInputColumn90
Dim offset As Integer = 0

input = Me.ComponentMetaData.InputCollection(0)
'presumes GetColumnIndexes order matches iterator order
'as BufferManager is not available to my knowledge in ScriptComponent
indexes = Me.GetColumnIndexes(input.ID)
For Each col In input.InputColumnCollection
Dim columnStructure As New ColumnInfo
With columnStructure
.colName = col.Name
.colLength = col.Length
.colIndex = indexes(offset)
'Normally, BufferManager would be used, but its not exposed in Script Component
.colPrecision = col.Precision
.colScale = col.Scale
.colType = col.DataType
End With
cols.Add(indexes(offset), columnStructure)
offset += 1
Next
End Sub

Public Structure ColumnInfo
Dim colName As String
Dim colType As DataType
Dim colIndex As Int32
Dim colLength As Int32
Dim colPrecision As Int32
Dim colScale As Int32
End Structure

End Class

A screenshot of the results: HERE|||Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.|||This is great - thanks! I will let you know how it goes|||

Phil - thank you so much for your help!

This code was exactly what I was looking for. The only tweak I had to make was to use SetNull() instead of SetString() - the Chr(0) actually added an empty string rather than a null.

Thanks again!

|||

Phil Brammer wrote:

Note: I would LOVE it if someone would come in and simplify the code above. I just don't have the understanding of the SSIS programming model to do anything BUT hack code together. Not yet anyway.

Also, the work happens in the Input0_ProcessInputRow sub. Periods are replaced with Chr(0) which is null. Give it a shot, tweak it to however you need, etc...

You may want to trim() the columns first, before going into this transformation.

How's this?

Note - this is really only useful for generically accessing each column, and has no type safety, so the code only works for string columns. jaegd's original code provided a lot more information about the columns, including type information, which would allow you to add conditional logic to use the appropriate accessor (GetString, GetInt32, etc). I've also used the System.Reflection to access the buffer, but I need to test the performance a bit more before pushing that as a solution.

Code Snippet

'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1

'Credit has been given where credit is due

'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

'Trimmed to smaller set of code by jwelch

Imports Microsoft.SqlServer.Dts.Pipeline

PublicClass ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

PublicOverridesSub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim counter AsInteger = 0

For counter = 0 To inputBuffer.ColumnCount - 1

If (inputBuffer.GetString(counter)) = "."Then

inputBuffer.SetString(counter, Chr(0))

EndIf

Next

EndSub

PublicOverridesSub ProcessInput(ByVal InputID AsInteger, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

EndSub

EndClass

|||

jwelch wrote:

How's this?

Yep, I like that MUCH better. Knew jaegd had much more stuff in there, I just didn't quite know where to begin to trim it down... Ran out of time too... The golf course beckoned.