Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

sql

Replacing Active X/VBscript used in SQL2000 data transformations to SQL2005 SSIS

Hi

I am new to SSIS and have the following problem. I used the following script to clear data in columns of any CR/LF/Commas and char(0)'s. Can I just transfer this to SSIS and how exactly do I do that? Any help or advice would help.

Function Main()

Dim x

For x=1 to DTSSource.count
If Isnull(DTSSource(x)) = False Then

DTSDestination(x) = replace(replace(replace(Replace(DTSSource(x) , chr(13),""),chr(10),""),chr(0),""),","," ")

Else

DTSDestination(x) = DTSSource(x)

End If

Next

Main = DTSTransformStat_OK

End Function

Andre

SSIS does not support ActiveX script transforms. You have the Script Component that gives you full VB.NET, so you coudl simply convert the code for that if you wanted. In this case I woudl strongly recommend you use teh Derived Column transform. This uses the SSIS specific expression syntax, which includes a REPLACE function and a conditional operation, and ISNULL, so you can do what you require.

To specify the non-printable characters you can use the unicode character code, \x005C for a \ character.

Escaping In Expressions
(http://wiki.sqlis.com/default.aspx/SQLISWiki/EscapingInExpressions.html)

|||

Hi

Thanks for the answer, problem though I do not want to use derived columns as I have multiple extracts per client, currently if i add up each individual extract I am sitting at 300+ with the figure increasing monthly. I cannot do this by field by extract. I need something generic which will look at each field in an extract and then take out all char(10), char(13), commas and char(0) before writing the results to a flat file.

Your help with code would be much appreciated

Andre

Replace Multiple LIKEs

I have a query below that performs horribly:

@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)

SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)

The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.

What can I do to get this to perform reasonably? I cannot use full-text
search.
Any help is appreciated.
lqlaurenquantrell wrote:

Quote:

Originally Posted by

I have a query below that performs horribly:
>
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
>
>
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
>
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
>
What can I do to get this to perform reasonably? I cannot use full-text
search.

Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of

create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)

insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')

do this:

create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)

create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)

insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')

insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')|||Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq

Ed Murphy wrote:

Quote:

Originally Posted by

laurenquantrell wrote:
>

Quote:

Originally Posted by

I have a query below that performs horribly:

@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)

SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)

The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.

What can I do to get this to perform reasonably? I cannot use full-text
search.

>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')

|||I need a way to improve performace of the query with the existing table

Quote:

Originally Posted by

structure.

There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.

Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq
>
>
Ed Murphy wrote:

Quote:

Originally Posted by

>laurenquantrell wrote:
>>

Quote:

Originally Posted by

I have a query below that performs horribly:
>
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
>
>
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
>
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
>
What can I do to get this to perform reasonably? I cannot use full-text
search.

>>
>Normalize tblStuff by removing column Keyword and replacing it with a
>second table tblStuffKeyword. For instance, instead of
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50),
> Keyword text
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (1, 'A', 'B', '123456,321654,987987,345987')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (2, 'C', 'D', '')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (3, 'E', 'F', '123456,987987')
>>
>do this:
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50)
>)
>>
>create table tblStuffKeyword (
> StuffKey int,
> Keyword varchar(50)
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (1, 'A', 'B')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (2, 'C', 'D')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (3, 'E', 'F')
>>
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '321654')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '987987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '345987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '987987')

>

|||Dan,
Thanks.

Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I need a way to improve performace of the query with the existing table
structure.

>
There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.
>
Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq

Ed Murphy wrote:

Quote:

Originally Posted by

laurenquantrell wrote:
>
I have a query below that performs horribly:

@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)

SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)

The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.

What can I do to get this to perform reasonably? I cannot use full-text
search.
>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')

replace instance of multiple char(32)

I have several fileds in a sql table which holds some text and a hell of a lot of white spaces (char(32)) which is causing some problems when I show the data on the front-end

What I want to do is use sql replace function to replace the char(32). However, because there is text I can't do a simple replace as the text will become just one word !

Can anyone tell me how to loop through the field to find the char(32), where the char(32) is greater than say 5 sapces and then replace this with just one single space

E.g. a field in a table contains the following :

"my text field and loads of spaces then some more text with loads of spaces "

I want to replace the char(32) with just one space so it looks like this :
"my text filed and loads of spaces then some more text with loads of spaces"

Thanks for your helpI'd try using:SELECT Replace('Target string with spaces', ' ', ' ')-PatP|||I had to run the replace twice...

declare @.Targetstring varchar(50)
set @.Targetstring = '1 2 3 4 5 6'

select @.TargetString
select Replace(@.TargetString, ' ', ' ') --Not quite there yet...
select Replace(Replace(@.TargetString, ' ', ' '), ' ', ' ') --Lookin' good!|||Replace is kinda like that... It only makes one pass through the string on its own. It can be coerced into doing the right thing, if you have a big enough stick!

-PatP