Migrating from 2000 to 2005

Hello guys, I have vry basic question. I'm migrating from 2000 to 2005. MY question is do we have to recode all packages or just migrating of old 2000 DTS is fine. If so, then I can see only small icon of DTS package in 2005, whihc is not showing the details of DTS. Kindly, explain me what do i really do, migrating old one or re-creating new in 2005. Thanks in Advance

There is some information in this forum and on the web:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=DTS+migration&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

http://www.google.com/search?hl=en&q=migration+ssis+to+dts

Migrating DTS Active X/ADO script to SSIS

Hi All,

In SQL Server 2000 DTS, I had an Active X script that, using ADO, performed the following basic operation:

Retrieve a list of account numbers through Recordset1
While Not Recordset1.eof
Build a sql statement based on fields from current row of Recordset1
Open Recordset2 based on the sql statement built
Run an update query based on results from recordset 2
Loop

I want to bring this over to SSIS, but i'd like to take advantage of the most current "ways of doing things" instead of just using SSIS's activex script.

Does anyone have any recommendations on the best way to handle this type of thing in SSIS? A script task? A script component? I've never really worked w/ ADO.net so if it involves that I'd have to pick up some pointers... any suggestions?

Thanks in advance
Jeff

A script task would be one way to do this. You could use an ADO.Net connection in the Script. You can then use that to help manager the connection information, and it will return a SqlConnection for use in the script. For example-

Public Sub Main()

Dim connection As SqlClient.SqlConnection = CType(Dts.Connections("MyAdoSqlClient").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

Dim command As New SqlClient.SqlCommand("EXEC ProcName", connection)

command.ExecuteNonQuery()

Dts.Connections("MyAdoSqlClient").ReleaseConnection(connection)

Dts.TaskResult = Dts.Results.Success

End Sub

A script component is only used inside the data flow, and this doesn't seem like it is moving data anywhere really. You could use a data flow, OLE-DB Source to Recordset Destination, but then what. I think this fits into the over engineered category as does the option below.

Alternative methods could be to use the Execute SQL task to get the recordset and the For Each Loop to process the recordset. I'd be tempted by this option. I'd use a script task maybe to just build teh SQL statement inside the loop, then store it in a variable. I don't quite get the Recordet2 and Update steps, but maybe an Execute SQL Task would do this. If you don't end up with any real worker tasks inside the For Each Loop, then I would just do it all in a Script Task again, but using VB.Net and the nice things it gives over ActiveX Script.

Migrating an Access Database to MSDE

I'm very new to MSDE, so this may be a basic question but I've been unable
to find a solution.
Is it possible to migrate a database from Access to a Sqldatabase in MSDE?
If so, how? The Access Upsize Wizard doesn't seem to do it?
Thanks in advance.
The Access upsize wizard should move the data but not much more. You can
also use DTS to import Access/JET database. Unfortunately, an "Access" JET
database also contains, forms, reports, macros and other "stuff" that is
lost when you move to MSDE. The Access application itself also uses
techniques that don't migrate well and don't take advantage of SQL Server's
power.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Tom Wing" <biciwing@.optonline.net> wrote in message
news:c6m7d.22354$kq6.12187878@.news4.srv.hcvlny.cv. net...
> I'm very new to MSDE, so this may be a basic question but I've been unable
> to find a solution.
> Is it possible to migrate a database from Access to a Sqldatabase in MSDE?
> If so, how? The Access Upsize Wizard doesn't seem to do it?
> Thanks in advance.
>
sql