Moving Data In Ssis From Sql Server 2000 To 2005 On Same Server
Feb 9, 2007hi, does anyone know how I can make a connection to a 2000 db thru ssis?
View 8 Replies
ADVERTISEMENT
Newbie Question, Moving Data Between Sql Server 2000 And 2005
Jun 7, 2006Hi,
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main
table and rename the staging table to the main table, to minimize the
down-time of the data. I can't get the workflow to work, because the
staging table has to exist when I run the package. I thought I could
use an "Execute SQL" task to generate the table before I would run the
task, but that doesn't work. Am I going about this the wrong way? Is
there an optimal solution to this problem so my data can be accessible
as much as possible.
Regards,
Atli
Moving DB From Sql Server 2000 To 2005
May 21, 2008Hello, what is the easiest way to move a DB (tables and data) from SQL server 2000 to 2005.
Ive been trying to export the DB tables from 2000 using Excel, yet i get "cannot expand named range" errors on several of the tables.
Is excel the way to go? another way? Can i use backup/restore?
Note: also moving from one machine to another.
Thanks for any help.
Moving CRM, SPS, Project Web Server, SQL 2000 To SQL 2005
May 17, 2007I am reciving a new powerful database server
My old database server runs SQL 2000 and has the CRM 3.0 app and database, our corporate web site (SharePoint Services 3.0) database, and Project Server 2007 database. The corporate site and project apps and web sites reside on our file server.
I want to move, upgrade or migrate the databases, corporate site and Project site to this new SQL 2005 server. I am a little unsure the best way to do this.
Should I move all the database first, then move the apps?
Should I do a restore of everything on the new server, and after I know it works just turn off the other server?
Am I missing a better way?
Anyone have any insight, let me know.
Thanks,
Ken
Moving Data From SQL 2005 To SQL 2000 Daily
Feb 21, 2007We need to upgrade our HR system from SQL 2000 to SQL 2005. Support for SQL 2000 ends 12/31/2007.
We have a third party vender that do not currently support SQL 2005. Employee's data is transfered from our HR database to the third party database for the purpose of yearly performance evaluation on a daily basis.
Both databases are currenlty on a SQL 2000 server.
Can data be transferred from a SQL 2005 database to a SQL 2000 database?
Moving Data To And From SQL Server 2005, With Manipulation In C#
Oct 9, 2006Hi
I am wishing to
* take a table of data into a C# CLR procedure and store it in an array
* take a second table of data into this procedure row by row, and return a row (into a third table) for each row (and this returned row is based on the data in the array and the data in this row).
I am new to CLR programming, and pulling my hair out at the moment. I€™m sure that what I€™m trying to do is simple, but I am not making any progress with the on-line help and error messages L. I have now described what I am trying to do in more detail, firstly in English, and then in a T-SQL implementation that works (for this simple example). I€™m looking for the C# CLR code to be returned €“ containing preferably two parts:
* the C# code and
* the CLR code to €˜make it live€™
Since I am not sure where my coding is going wrong. (I think it should be possible to read in the one table, and then loop through thte second table, calculating and returning the necessary output as you do the calculations...
Problem in English
Consider a situation where there are three tables: DATATABLE, PARAMETERTABLE and RESULTSTABLE.
For each row in PARAMETERTABLE, I will calculate a row for RESULTSTABLE based on calculations involving all the entries form DATATABLE.
I am wishing to do this in a C# CLR for performance reasons, and because the functions I will be using will be significantly more complex, and recursively built up.
Consider the following table structures
DATATABLE €“ [i int, datavalue real]
[1,12.5]
[2.10]
[3.14]
[4,17.5]
(where numberofrows is 4)
PARAMETERTABLE [parametera real, paramaterb real]
[10,2]
[11.7,1.1]
RESULTSTABLE [resultvalue real]
[20.5]
[18.26]
The values of the results table are calculated as the
sum (for i = 1 to numberofrows)
of
(Parameter1+i*parameterb-datavalue)^2
Which leads to the values shown.
T-SQL Implementation
-- Set up database and tables to use in example
USE master
GO
CREATE DATABASE QuestionDatabase
GO
sp_configure 'clr enabled', 1
GO
USE QuestionDatabase
GO
RECONFIGURE
GO
CREATE TABLE DataTable (i INT NOT NULL, DataValue REAL NOT NULL)
GO
CREATE TABLE ParameterTable (ParameterNumber INT NOT NULL, ParameterA REAL NOT NULL, ParameterB REAL NOT NULL)
GO
CREATE TABLE ResultsTable (ParameterNumber INT NOT NULL, ResultsValue REAL NOT NULL)
GO
--Initialise the Tables
INSERT INTO DataTable (i, DataValue) VALUES (1,12.5)
INSERT INTO DataTable (i, DataValue) VALUES (2,10)
INSERT INTO DataTable (i, DataValue) VALUES (3,14)
INSERT INTO DataTable (i, DataValue) VALUES (4,17.5)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (1, 10, 2)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (2, 11.7, 1.1)
-- The TSQL to be rewritten in C#, to produce the Output as hoped
INSERT INTO ResultsTable (ParameterNumber, ResultsValue)
SELECT ParameterNumber, SUM((parametera+i*parameterb-datavalue)*(parametera+i*parameterb-datavalue)) AS b
FROM DataTable
CROSS JOIN
ParameterTable
GROUP BY ParameterNumber
-- Output as hoped
SELECT * FROM DataTable
SELECT * FROM ParameterTable
SELECT * FROM ResultsTable
-- which produced
1 12.5
2 10
3 14
4 17.5
1 10 2
2 11.7 1.1
1 20.5
2 18.26
-- but I hope to do the same with something like:
CREATE ASSEMBLY *** FROM 'C:***.dll'
CREATE PROCEDURE GenerateResultsInCLR(@i int, @r1 real, @r2 real)
RETURNS TABLE (i int, r real)
EXTERNAL NAME ***.***.***
EXEC GenerateResultsInCLR
This is a simple example, that can be easily written in T-SQL. I am looking to develop things that are recursive in nature, which makes them unsuited to T-SQL, unless one is using cursors, but this becomes very slow when the parameter table has 1m records, and the data table 100k records. This is why the datatable must be read in once, and manipulated many times, and the manipulation will need to be in the form of a loop.
Thanks very much for your help.
Go well
Greg
Moving Data From Progress DB To SQL Server 2005
Feb 9, 2007
Hi All,
We have a Progress DB in our Company. We are trying to move all the data to SQL Server 2005. When I try to run the Import Data task from SQL Server 2005 the radio button called "Copy data from one or more tables or views" is getting disabled and it is asking me to write a SQL script to copy all data from 120 tables.
I am using a .NET Framework Data Provider for ODBC when I run the Import Data task from SQL Server 2005.
I am using the DataDirect 4.1 32-Bit Progress SQL92 v9.1D ODBC Driver to connect to Progress DB. I am getting a connection but the copy feature is getting disabled.
Can anyone please help me to resolve this issue, or even if you provide me with some pointers that will be really helpful.
Thanks
Suresh
Moving Data From Excel To SQL Server 2005 Express
Oct 17, 2006I am trying to load data from an Excel spreadsheet file into SQL Server 2005 Express. I understand that DTS is the best tool for doing this but from my research it appears that DTS is not available with the Express edition and the import wizard that does come with Express is not well suited for this type of conversion.
Does anyone have any suggestions for how to achieve this objective? Thanks for any help you can provide.
Moving Data From Mysql To Sql Server 2005. Does Not Work!!
Nov 9, 2006
Problem: Moving data from mysql to sql server 2005
I am trying to pull data over from mysql to sql server. First the import wizard greys out so I have to put in 1 query at a time which is pain. and second it does not even work! it takes me through the end of the wizard for me to click finish and then says oops it does not work. there was an error!
Anyway i tried going through the ssis route cuz its going to be a nightly job. i used the ado.net odbc connection. It worked but the performance is really not acceptable. it took 5 mins to import 24000 rows where as dts was taking 1 sec to do this. i wish i could use the native mysql odbc 3.51 connector and import. can some one give me step by step instructions on how to do that ?
I hear someone mentioned of using excute sql task which can use mysql odbc 3.51 driver. but since i am new how do i get it to work. say for example in the excute sql task i run a statement like select * from addr. then what?
cuz eventually i want the result to be saved in a sql server table called addr. How can i get the result from that excute sql task and put it inside of an addr table in sql server. should i save the result to a variable of type object. but then how do i get the data from object and tell sql server in the designer that the result contains these columns and it needs to map to these columns in the addr table of sql server.
Very confused. i wish the first option would have given me results which an enterprise ETL gives. but apparently it is too slow that it wont be acceptable in a production envrioment. when i will have millions of rows coming in .
Please anyone can help me in this.
Thanks for the help!
Can I Access SQL Server 2000 Tables Via SSIS/SQL Server 2005
Apr 9, 2007Can I access SQL Server 2000 tables via SSIS/SQL Server 2005?
View 1 Replies View RelatedMoving An Sql Server 2005 Database To A Sql Server 2000 Database
Dec 12, 2006I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database.
I'm not sure the best way to do this.......
Can anyone enlighten me?.....
Migrate SQL Server 2000 DTS To SQL Server 2005 DTS Or SSIS
Apr 8, 2008
We have DTS packages in SQL Server 2000, which populates the data from SQL 2000 to Ms-Access tables.
We are moving our database from SQL Server 2000 to SQL Server 2005.
Why & How & What is the best approach to migrate DTS packages?
Migrate SQL Server 2000 DTS to SQL Server 2005 DTS Or SSIS
Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS
Nov 20, 2007
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries
May 3, 2007Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
How To Create A Data Diagram In SQL Server 2005 Management Studio For A SQL Server 2000 Database
Jan 9, 2008Hello:
I have an old database created in SQL Server 2000,
now I can open, access it in SQL Server 2005 Management Studio, but I can't create Data Diagrams.
Please advise on how to make it work....
Thank you
Bit-data From SQL Server 2000 (2005 Working, 2000 Doesn't)
May 19, 2008Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
View 2 Replies View RelatedSQL Server 2000 To SQL Server 2005 - Any Changes Required To Existing Code, System.Data.SqlClient.SqlConnection?
Dec 13, 2007My web project (ASP.NET 2.0 / C#) runs against sql server 2000 and uses the System.Data.SqlClient.using System.Data.SqlClient;
I use System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlCommand to make the connections to the database and do selects and updates. Is it correct to continue to use these against SQL Server 2005? I ask because I made a connection string (outside of .Net) for SqlServer 2005 using the SQL native provider and it had the following - Provider=SQLNCLI.1 and any connection strings I had made (also outside of ASP.NET) fro SQL Server all used Provider=SQLOLEDB.1. This is why I wondered if there is a different SqlClient in .Net 2.0 for SQL Server 2005?
Cheers
Al
Migrate Data From SQL Server 2000 To SQL 2005 Server Express
Nov 12, 2005Hello:Does Sql Server 2005 Express support data migration from Sql Server 2000.
View 2 Replies View RelatedCopying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?
May 8, 2006An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.
On SQL Server 200 instances the job ran in minutes in the old 2000 package.
Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?
Any inputs will be much appreciated.
Thanks,
MShah
Moving A SQL Server 2000 Database From One Server To Another Advice
May 23, 2006Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
Thanks in advance for your help
Nick
Converted Our DTS 2000 Packages To Sql Server 2005 SSIS
Nov 7, 2007We are converted our DTS 2000 packages to Sql Server 2005 SSIS. I am getting following error on my ActiveX script that got converted. I am new to SSIS and DTS. Never ever worked with ActiveX also. So any help would be appreciated. Following is the script followed by error I get:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim oPkg
SET oPkg = DTSGlobalVariables.Parent
SET stpBeginJob = oPkg.Steps("DTSStep_DTSActiveScriptTask_1")
SET stpFinishedJob = oPkg.Steps("DTSStep_DTSActiveScriptTask_3")
IF DTSGlobalVariables("Count").Value > 0 THEN
stpFinishedJob.DisableStep = True
stpBeginJob.ExecutionStatus = DTSStepExecStat_Waiting
ELSE
stpFinishedJob.DisableStep = False
stpFinishedJob.ExecutionStatus = DTSStepExecStat_Waiting
END IF
Main = DTSTaskExecResult_Success
End Function
Error I get Is:
[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x000A39BC.
Thank You
Managing Data Between SQL Server 2000 And SQL Server 2005
Apr 14, 2008Hello,
I have SQL Server 2000 and 2005 running on the same server. My current system is in SQL Server 2000 and the new system is in SQL Server 2005. My users will be using SQL Server 2000 for the most part, But for the part the processes that have been migrated to 2005, they will be using SQL Server 2005 as well. The database schema is different on both servers eventhough the data will be similar in most respects.
Is there is a way through which any data that is inserted/updated in SQL Server 2000 via content Management System/ manually/ via a batch job, the data is automatically populated in SQL Server 2005 tables or vice versa?
Please advice at the earliest.
Thanks.
Transform Data From DB On Sql Server 2000 To Another On Sql Server 2005?
May 8, 2007Hello everybody,
Can anyone give the best way to transform data from a DB on sql server 2000 to a DB on sql server 2005?
Note: - the source DB and the destination DB are different in their structure
- We need the conversion way to be scripted to can be done on different site.
thanks
Sql Server 2000-&>-&>-&>-&>-&>Data-&>-&>-&>-&> Sql Server 2005
Apr 18, 2007How can i migrate data from Sql Server 2000 to Sql Server 2000
View 1 Replies View RelatedData From SQL Server 2005 To SQL Server 2000
May 16, 2008
Dear all,
Now I am planning to flow data from SQL Server 2005 in Singapore to SQL Server 2000 in Hong Kong.
I am planning to write DTS or VB.Net Program or SQL Stored Procedures. Which is better? Is there any problems or important notes to take care from SQL2005 to SQL2000???
I am afraid when doing in next week, I will face some fatal errors or data transmission problems or conversion problems!!!
Appreciate your help,
Moving A SQL Server 2005 Database From The Local Network To An Online Server.
Mar 7, 2006Hi All, first post.
I have done a bit of searching around and cant find a clear answer to this question.
Current Setup
Desktop application (c#) that connects to a SQL Server 2005 express database on the same local network as the application (currently 3 users)
It is only a very small company and has just taken on their first remote worker, but expects to take on another 6-8 over the next few months. They have asked for the database to be moved online.
The application was written in such a way that everything has been done using no stored procs, or views, it is all native SQL.
This will be my first DB hosted online and before I go ahead and do anything I just wanted to make sure what I have to do is correct, sorry if this is a very basic question, although I have been programming for a long time, I have never had the chance to do any online databases before.
Will this work.
1.Find a SQL Server 2005 Hosting company.
2.Move the database to the server.
3.Setup the users permissions.
3.Alter the connection string in the application to point to the new location.
So the only thing that would change would be a new connection string in the application preferences?
Or am I living in a dream world, because nothing is ever that simple.
One thing I am worried about is the security/visiblity of the database and data as it travels from the server to the client and back.
Thanks for any advise you can give.
Mark
Moving SQL Server 2000 Master Db
Mar 6, 2006Does anyone know how to move master db to a differetn drive instead of default c: drive in a named instance environment?
Thanks.
Rick
Moving SQL 2000 Server To A New Machine
Jun 24, 2002I am moving my SQL 2000 Server from a Small Business Server to a full version of Windows 2000 Server and SQL 2000 Server. I have had no experience doing this but have backed up and restored my production databases for practice in case a of disaster situation. Is there anything special I need to do to move everything, including the system databases to a new server and not loose any of the user logons, etc.?
Thanks for any advice,
Chris
Moving From Access To SQL Server 2000
Sep 26, 2001Hi there everyone, I have written a database system which tracks the performance of working in a shipping company in access. Im now rewriting the system in sqlserver and the only real problem I have found so far is that its difficult to estimate what kind of a server *cpu* & *ram* would be appropraite. The system currently performs transaction on my desktop machine in a second a quickest and 2 at slowest.
There are going to be about 500 users in 3 time zones so there will only really be about 300 max hitting the system in an hour.
I was looking at a Dell Poweredge server with twin P3's and half a Gb or ram would this be a good place to start from?
Any Advice would be great!!!
Steve
Moving From Accesss97 To SQL Server 2000
Jul 20, 2005I have developed an Access 97 database that I would like to distribute to anumber of staff, but they do not have Access. At the same time I amconsidering upgrading to SBS 2003 premium edition which comes with SQLServer 2000.1. If we were to upgrade would it be a very difficult job to recreate thedatabase in SQL Server 2000, as in does it have wizards, macros etc.?2. Would the staff be able to use and share the new database from the serveror would they also require additional software on their desktops?3. Is there any way of distributing the Access 97 database with some sort of"run-time" licence - it was not developed using a developers edition?This has been posted to both comp.databases.ms-access andcomp.databases.ms-sqlserverYour advice would be appreciated.RegardsJohn McC
View 1 Replies View RelatedMoving SSIS Projects To Another Server
Oct 23, 2007I need to move SSIS packages from our development server to our production server. I need to move those projects in a manner that would allow me to modify them on the destination server. The build option seems to move an executable only. Is there a way to move the entire package to another server?
Thanks,
Ed
Moving SQL Server 2000 Instances To A Cluster
Dec 10, 2006Hi,
We have a new failover cluster (Windows 2003 SP1, Microsoft SQL 2000 SP4) with each node of the cluster hosting 7 SQL Server instances in a 2-node active-active configuration connected to a SAN. We are planning to move some SQL Server Instances(from existing stand-alone servers) into this Cluster. Any insight into the process of moving SQL Servers into the cluster would be highly appreciated.
Best Regards,
Himansu
Converting SQL 2000 Data Into A SQL 2005 Server
May 4, 2006I have a large (huge) database that I want to copy onto my new slq 2005 test server. I'm leaning toward detaching the data on my 2000 box, duplicating it, copying it to my new 2005 machine, and attaching it. Is it possible that it could be that simple? If not, how is it done? Thanks a bunch for any help or pointers to the articles I was totally unable to find on the subject.
View 3 Replies View Related