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

rephrasing my quick SQL question......

i really appreciate the responses i received about my sql question. i think, however, that i wasn't really making my question clear and hope it's not too annoying to try one more time....

machine A has an instance of sql server running and it is listed under enterprise manager as "(local)(windows nt)"

now...

if i go to machine B, or C, or D or whatever and try to make a new registration...the drop down list of available sql servers on our network includes one called "sqlReports" - and that is the one which on the actual machine it resides on, is called "(local)(windows nt)" - so...if it isn't called "sqlReports" on the machine it resides on.....how is it coming up with that name on all the DROP DOWN LISTS of available sql servers on all the other machines?

and how can i rename "(local)(windows nt)" to something else??

Is the machine name sqlReports? If that isn't it, are you running active directory?|||

i THOUGHT the machine name was something else but after your post i went and double checked and it IS sqlReports.

mystery solved.

thanks