Data script task - how to generate multiple rows from one row ? (string splitter)

Hi

in input we have a set of rows, each one with a column containing a string (eg: "AAOOOOAAAOOA").

We'd like to split this string (using a vb.net data script task) into tokens (eg: "AA", then "OOOO","AAA","OO","A"), and to output one line per token.

How can we achieve that with a vb.net data script task ? (Or anything else ?)

best regards

ThibautJust take your source and throw it into a derived column transformation where you'll perform your string split to create new fields for each "token." Then you'll go into an unpivot transformation where you'll take the new columns and turn them into rows.

Why use a script task when you can use the optimized data flow tools as-is?|||

You need to create an asynchrnous transform. So add the transform, select and also setup columns as required. Ensure the SynchronousInputID of the output is set to 0, making it async.

Then read rows and add them to the output as required. Sum dummy code -

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

' Read the rows in...

While Row.NextRow() ' This happens once for each input row

' Do something here, and as required, add rows to the output, use a loop or whatever

For i As Int = i < 10

With OutputBuffer

.AddRow() ' Adding a new output row and setting values. Can do this as many times as we like, 0 or more times in the context of this input row loop interation

.Asset = Row.InputColumn

.Product = i

Next

End While

If Row.EndOfRowset Then

OutputBuffer.SetEndOfRowset()

End If

End Sub

|||Thanks a lot Darren ! That's really perfect (and thanks for the detailed sample, I really appreciate).

regards,

Thibaut|||Just be careful because some have tested the script task and it performs slower than the other, native dataflow tasks.|||Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?|||

DarrenSQLIS wrote:

Phil, I agree in principal, the native stock components should be faster as a rule, but have you got any references?

Yep, no problem... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=857796&SiteID=1|||Hi Phil

thanks for your input as well. Like Darren, I agree that a good rule of thumb is that a pipeline component will work generally faster than a corresponding script component.

A script component is generally one-shot code, which generally doesn't get the same level of testing and optimization. In the article you point to, the script task benchmark shows an average CPU usage of 5% (which is often a sign of synchronization issues, contention etc). Here (like suggested by a commenter), a modification of the implementation (like handling sets of rows instead of one row at a time) would most likely boost the performance a lot.

So I wouldn't draw conclusions based on a single script example, given that each implementation is likely to vary a lot in terms of performance and memory consumption - just like any kind of code!

But anyway - it seems that I have two solutions for my problem now. I don't hesitate to use script task when they prove useful, but I first try to stick to the pipeline components.

I'll keep you posted!

thanks again for the input

Thibaut Barrère|||Hi

I've finished the job using a script task (I will report back the details later).

I'm curious about how it would have been possible to implement this using a derived column transformation (at first sight I couldn't find out).

Phil could you give a bit more details ? I could not find any real string splitting functions in SSIS - were you thinking of using a FIND function recursively (or any other feature I've missed ?)

cheers

Thibaut

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UKJon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||What exact steps did you take? How did you "build a new log file"?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB servers failed.
> The DB server was given separate physical disks to split the data and txn log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pretty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 11:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
>> I thought the dat/mdf files should have contained nearly all the data
>> upto the point the DB failed because of the disk failure. Am I missing
>> something here?
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Had a problem recently with some lost data after a disk in one of our DB
>> servers failed.
>> The DB server was given separate physical disks to split the data and txn
>> log files on different disks to guard against disk failure.
>> The disk with the txn log failed at 14:30. We got the DB online again
>> pretty quickly by building a new log file for the DB. However, the last
>> data in the DB was written at 11:45. I'm not expecting to recover the
>> data now but was confused to see the lost data.
>> I thought the dat/mdf files should have contained nearly all the data
>> upto the point the DB failed because of the disk failure. Am I missing
>> something here?
>> Is there anything I should be checking on the server ?
>> TIA,
>> Jon A
>> London, UK
>>
>

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK
What exact steps did you take? How did you "build a new log file"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB servers failed.
> The DB server was given separate physical disks to split the data and txn log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pretty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 11:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>
|||Jon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>
|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>
|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas

"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK
|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>

Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UKWhat exact steps did you take? How did you "build a new log file"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB s
ervers failed.
> The DB server was given separate physical disks to split the data and txn
log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pret
ty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 1
1:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>[/vbcol]|||Jon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>