Showing posts with label sql2005. Show all posts
Showing posts with label sql2005. Show all posts

Wednesday, March 28, 2012

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

Wednesday, March 21, 2012

Replace column value with MAX() query in dataflow before insert

Hi,

We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.

Just to be clear:
column id (int)
column name (string)

Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table

Which transformation do I use to replace the value of the id column with this new id?

thanks,

Stephane

Hi Stephane,

You would first need to get the maximum value of the id column into another column (called maxid, for the purposes of this example). You could do this with a script component, or by splitting the flow and using an Aggregate transform,and merging it back. Then, you can then use a derived column transform to replace the value of the column by setting a replace expression such as the following:

ISNULL(id) ? maxid + 1 : id

Mark

|||

Hi Mark,

Thanks for your reply.

Your solution would work if all the data was in the dataflow, which unfortunately it isn't for me (we iterate over excel files with one row per file). We now do a select max() ... at the start of the control flow and store it in a variable. When we detect a new row, we increment the value and use a derived column transform like you suggested as well to put the value in the column.

Thanks,

Stephane

sql

Wednesday, March 7, 2012

repairing replicated tables

Hi all,
I have a sql2005 merge replication running nightly between SQl 2005 standard
and SQLexpress. (The subscription is on the SQLexpress) last night it began
failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
it and the lowest level of repair is repair with loss. This box is on its
way out and i need to band-aide it until the new one arrives and is setup.
My question is, do I need to drop the replication before putting the DB in
single user mode? Is there a way to repair the table without running DBCC
checktable with the repair option?
TIA,
Joe
Is the problem related to some indexes and do you get a RID ID error?
If so this is related to some indexes (on system tables IIRC) and you
can drop them and recreate them.
Run CHECKDB again note the object ID which is experiencing the error
and evaluate whether it is an index or not. Script out the index, drop
it and recreate it. There will be no data loss associated with this.
If it is table related you will have to bcp the data out noting where
failure occurs and then work around that/those rows using the firstrow
and lastrow options. You can use DBCC Page to look at the problem
pages if you need to be so granular in your data retrieval.
On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
> Hi all,
> I have a sql2005 merge replication running nightly between SQl 2005 standard
> and SQLexpress. (The subscription is on the SQLexpress) last night it began
> failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
> it and the lowest level of repair is repair with loss. This box is on its
> way out and i need to band-aide it until the new one arrives and is setup.
> My question is, do I need to drop the replication before putting the DB in
> single user mode? Is there a way to repair the table without running DBCC
> checktable with the repair option?
> TIA,
> Joe
|||Thank you, it was an index issue and droping and recreating the Indexes did
the trick.
Thanks again.
"Hilary Cotter" wrote:

> Is the problem related to some indexes and do you get a RID ID error?
> If so this is related to some indexes (on system tables IIRC) and you
> can drop them and recreate them.
> Run CHECKDB again note the object ID which is experiencing the error
> and evaluate whether it is an index or not. Script out the index, drop
> it and recreate it. There will be no data loss associated with this.
> If it is table related you will have to bcp the data out noting where
> failure occurs and then work around that/those rows using the firstrow
> and lastrow options. You can use DBCC Page to look at the problem
> pages if you need to be so granular in your data retrieval.
> On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
>