Showing posts with label dts. Show all posts
Showing posts with label dts. Show all posts

Friday, March 30, 2012

How to attach a DB that has not been deatached

My SQL is 2KSP3a (8.00.760)
I 've both MSDB datafiles with no backup that comes from a Destroyed Server
and I would like to recover DTS from them
This datafiles have not been detached previosly. Are recovered from original
disks when SQL its stopped.
I've installed a new SQL Server on a new machine
I've put the necessary flags, started SQL detached actual MSDB and...when i
try to do 'sp_attach_db' on a new server with the old MSDB I obtain the KB...
Knowledge Base
FIX: Transaction Log Restore Fails with Message 3456
PSS ID Number: 329487
Article Last Modified on 9/25/2003
How can i obtain the fix or resolve the DTS recovery?
Thansk a lot !!
Germa Guerra
Not sure if this applies to MSDB but it 's probably worth a look:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Germa Guerra" <GermaGuerra@.discussions.microsoft.com> wrote in message
news:A5D3DE5A-4D04-41B5-944E-2E8302621F31@.microsoft.com...
> My SQL is 2KSP3a (8.00.760)
> I 've both MSDB datafiles with no backup that comes from a Destroyed
> Server
> and I would like to recover DTS from them
> This datafiles have not been detached previosly. Are recovered from
> original
> disks when SQL its stopped.
> I've installed a new SQL Server on a new machine
> I've put the necessary flags, started SQL detached actual MSDB and...when
> i
> try to do 'sp_attach_db' on a new server with the old MSDB I obtain the
> KB...
> Knowledge Base
> FIX: Transaction Log Restore Fails with Message 3456
> PSS ID Number: 329487
> Article Last Modified on 9/25/2003
> --
> How can i obtain the fix or resolve the DTS recovery?
> Thansk a lot !!
> --
> Germa Guerra

Monday, March 26, 2012

How to Append new rows and update existing rows using dts

Hello,
I'm trying to create a DTS package that allows me to update a new
database for a new site with the rows from the old site's database. I
know how to get the data to the new database, transformed how I want,
and I know how to append rows, but I have not been able to figure out
how to do updates to rows. I want to create a package that inserts new
rows and updates any changes made to old rows.
Ideally if there was a way to somehow link data from the old database
to the new database so that the two were kept syncronous on a real time
basis that woudl be ideal.
Any ideas or suggestions?
Thanks,
Jeff
Hi
If you use Execute SQL Task object you can write T-SQL query to handle those
changes
<jefftim@.gmail.com> wrote in message
news:1162916174.330520.50970@.e3g2000cwe.googlegrou ps.com...
> Hello,
> I'm trying to create a DTS package that allows me to update a new
> database for a new site with the rows from the old site's database. I
> know how to get the data to the new database, transformed how I want,
> and I know how to append rows, but I have not been able to figure out
> how to do updates to rows. I want to create a package that inserts new
> rows and updates any changes made to old rows.
> Ideally if there was a way to somehow link data from the old database
> to the new database so that the two were kept syncronous on a real time
> basis that woudl be ideal.
> Any ideas or suggestions?
> Thanks,
> Jeff
>
sql

How to Append new rows and update existing rows using dts

Hello,
I'm trying to create a DTS package that allows me to update a new
database for a new site with the rows from the old site's database. I
know how to get the data to the new database, transformed how I want,
and I know how to append rows, but I have not been able to figure out
how to do updates to rows. I want to create a package that inserts new
rows and updates any changes made to old rows.
Ideally if there was a way to somehow link data from the old database
to the new database so that the two were kept syncronous on a real time
basis that woudl be ideal.
Any ideas or suggestions?
Thanks,
JeffHi
If you use Execute SQL Task object you can write T-SQL query to handle those
changes
<jefftim@.gmail.com> wrote in message
news:1162916174.330520.50970@.e3g2000cwe.googlegroups.com...
> Hello,
> I'm trying to create a DTS package that allows me to update a new
> database for a new site with the rows from the old site's database. I
> know how to get the data to the new database, transformed how I want,
> and I know how to append rows, but I have not been able to figure out
> how to do updates to rows. I want to create a package that inserts new
> rows and updates any changes made to old rows.
> Ideally if there was a way to somehow link data from the old database
> to the new database so that the two were kept syncronous on a real time
> basis that woudl be ideal.
> Any ideas or suggestions?
> Thanks,
> Jeff
>

How to Append new rows and update existing rows using dts

Hello,
I'm trying to create a DTS package that allows me to update a new
database for a new site with the rows from the old site's database. I
know how to get the data to the new database, transformed how I want,
and I know how to append rows, but I have not been able to figure out
how to do updates to rows. I want to create a package that inserts new
rows and updates any changes made to old rows.
Ideally if there was a way to somehow link data from the old database
to the new database so that the two were kept syncronous on a real time
basis that woudl be ideal.
Any ideas or suggestions?
Thanks,
JeffHi
If you use Execute SQL Task object you can write T-SQL query to handle those
changes
<jefftim@.gmail.com> wrote in message
news:1162916174.330520.50970@.e3g2000cwe.googlegroups.com...
> Hello,
> I'm trying to create a DTS package that allows me to update a new
> database for a new site with the rows from the old site's database. I
> know how to get the data to the new database, transformed how I want,
> and I know how to append rows, but I have not been able to figure out
> how to do updates to rows. I want to create a package that inserts new
> rows and updates any changes made to old rows.
> Ideally if there was a way to somehow link data from the old database
> to the new database so that the two were kept syncronous on a real time
> basis that woudl be ideal.
> Any ideas or suggestions?
> Thanks,
> Jeff
>

Monday, March 19, 2012

how to add rows with Script Component?

Why am I getting this design-time error from my script:

'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'

Here's my script:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim output_row As Buffer = Row

Dim seq As Integer = 0

For Each code As String In Row.IC.ToString().Split("*"c)

If seq > 0 Then

output_row = Row.AddRow()

End If

output_row.seq = seq

output_row.value = code

seq += 1

Next

End Sub

End Class

And here's the error it generates:

Error 3 Validation error. Convert Bib (ISC) to Prep Item_Detail: Split ISC codes [52]: Error 30311: Value of type 'ScriptComponent_d021a3fd485946868f5f0daadaf0e57c.Input0Buffer' cannot be converted to 'System.Buffer'. Line 15 Column 36 through 38 Error 30390: 'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected'. Line 19 Column 30 through 39 Error 30456: 'seq' is not a member of 'System.Buffer'. Line 21 Column 13 through 26 Error 30456: 'value' is not a member of 'System.Buffer'. Line 22 Column 13 through 28 ConvertDIN.dtsx 0 0

You are getting the error because your trying to get async script behavior from a synchronous script. This is clear from the for loop per input row, which could produce multiple output rows per input row. This is practically the definition of an asynchronous component.

So, make your component asynchronous.

1. On your output, set the SynchronousInputID to 0. Now you have an async component.
2. Add columns to your output, like seq and value.
3. Add rows to the output, as in the following.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim seq As Integer = 0

For Each code As String In Row.IC.ToString().Split("*"c)

If seq > 0 Then

Output0Buffer.AddRow()
Output0Buffer.seq = seq
Output0Buffer.value = code
End If
seq += 1
Next
End Sub|||

Thanks, now it compiles! But how does the object returned by Output0Buffer.AddRow() get its seq and code properties set? When I ran the above code, I got 0 output rows.

Also, how do I output a row when seq = 0, that is, the output row that corresponds to the first code in the input Row.IC string?

|||

I took out the test for seq > 0, and replaced the Recordset destination I was using for debugging with an OLE destination, and now my data view shows 1 row being output for each input row. Thanks!

Friday, March 9, 2012

How to add DTS

How can I Add a DTS by Script?
I run a batch script with osql or Query Analyser...
:D
ThanksYou can use SQL-DMO either with VB/VBScript or through sp_OAxxx

Friday, February 24, 2012

How to add (pick and choose) items from one table to another

We have two tables (one has DTS import of external spreadsheet - the other is the online table)

The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table).

I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table whereDescription=@.FirstTableFieldText)

I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how).... Even the base structure (pseudo code- whatever) would help

I suppose that you can do it in one insert statement

Insert into SecondTable(partno,ID)

Select partno,thirdtable.ID from firsttable

LEFT JOIN thirdtable

ON thirdtable.description = firsttabledescription

where partno not in (select partno from secondtable)

I do not know exactly your database schema but in this TSQL statement you select records from first table and connect each record to its description ID ( join) and in where statement you select only records which has partno which does not exists in second table (destination).

Is it what you need?

Thanks

JPazgier