I am using SQL Server 2005 and I want a clue if there is some good built-in support to do the following.
I want to transfer all the data from one source database to another destination database having same number to tables and schema like source database. I also want to ensure that the old data on the destination database should be secure too.
I have an idea that this can be done through SSIS in coding but can anyone give me a guide, tip, link, sample code that can be helpful.
I just moved to SQL 2005 and am facing a difficulty trying to build a DTS Package using SSIS Projects. I need to migrate data from an old Database design to a new Database design in my local pc.
Can anyone provide me some guidelines or tutorials on which components to use and a flow of the entire process which I should follow? I am confused.
Thanks alot in advance, hope to hear from you guys soon.
I am trying to migrate DTS 2000 packages to SQL 2005 SSis using Package Wizard. When I am invoking and after the passing server information of source and destination, while reading the packages it is giving error
TITLE: Microsoft SQL Server
------------------------------
This wizard will close because it encountered the following error:
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)
Could someone can help in this regards. As this is a important task for me and I am stuck on this which has to be resolved by end of today.
OK I migrated a DTS package from MS SQL 2000 to MS SQL 2005 64-bit SSIS. OK so I fixed a problem with a Double Global Variable... Now I am stuck at a connection to Oracle and it returning data... Here is the error message:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "Remedy_Count": "Unsupported data type on result set binding 0.".
My SQL Statement is this:
SELECT COUNT(*) AS Expr1 FROM DB.MYTABLE
When I run it from the Query Builder it returned this:
EXPR1 = 2983
Here are some details from the General Page on the Execute SQL Task
ResultSet - Single row ConnectionType - OLE DB Connection - Oracle Provider for OLE DB SQLSourceType - Direct input BypassPrepare - True
In the Result Set I have The following:
Result Name - Variable Name 0 - Remedy_Count
So... what can I do to fix this. I have the Remedy_Count set as a:
EvaluateAsExpression - False Value - 2414 ValueType - Double
I have tried setting the ValueType to Object and other things... Same error... I read some where about how SSIS has issues when Oracle returns a Numeric type data. Can someone help me with this? Anything you all can tell me would be great.
and it seems really straight forward. does this tutorial cover everything i need to do for data migration? do i need to watch out for anything? if anyone knows of a better tutorial please let me know. thanks for the help.
Hi all. I'll say from the get go that I'm no SQL guru. I'm primarily a web dev who has been given the data jobs kind of by default so I'm definitely no DBA.
Anyway, having established my noobness, I have encountered an issue. The employer recently decided to upgrade our databases from 2000 to 2005 and I have been tasked to migrate the data from old to new. I have discovered the Import/Export Wizard and got it to move all the data in a dev database we use...but there was one problem. It seems that the tables in 2000 that had columns designated as identity columns do not have the identity attribute when they arrive in 2005.
I haven't figured a way to alter them to give them that attribute once they're in 2005 so I'm wondering if there's something I can do/change in the import process that would retain the identity property of the columns if I were to re-import them.
This is my first migration attempt so dumping it and starting over is no great loss...but any help would be most appreciated.
I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?
Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access. The constraint here is that the data needs to be migrated to a completely new schema. I just need to migrate data only and no other objects.
Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.
I want to migrate data from Oracle Database to SQL Server 2005 by using SSIS Package. Client wants to migrate just data not schema. Schema is already there for SQL Server, generated through script.
I tried to migrate the data by using import-export wizard in SSIS. But, its asking for creating new tables when migrating data.
I want to migrate data in existing tables, without dropping already existed tables or creating new tables.
I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?
Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects.
I am trying to import data from SQL 2000 to SQL 2005
The Data schemas are the same in both (tables, relationships, key and triggers).
Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1
Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query
If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.
We have our databases running in sql server 7 and sql server 2000. Our clients are planning to migrate to sql server 2005 very soon. The front end is VB for few applications and .NET for another 2 applications.
We maintain sql server authentication and have logins created in the security folder.
I was able to download the sql server express and management studio express and restore the sql server 7 db there. I tried to create a sql server login in sql server 2005 also. But then, the server seems to allow only windows authentication.
Can someone help please?
Also, can someone advise on what change might be necessary in the application when we migrate to 2005.
I have a problem with this ActiveX Script. I need to find out why I'm getting this error message: "retrieving the file name for a component failed with error code ..." I think it has something to do with my global variable setting. Am I right? Help please.
I am currently in the process of migrating my DTS packages to SSIS---one at a time---and have come accross the following error during the excecution of a "Copy SQL Server Objects Task". The exact error is:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported.".
What I am doing with this particular task is to copy a table(s) from another SQL Server, to the current SQL Server. The original location is a SQL2000 DB table. In the near future the source server for this table will be a SQL 2005 server but not until the current migration has been completed.
I have looked at all of the properties, but can not find anything relating to the db compatibilty level. Is there an easier way? Or a setting that I can change to accomplish what I am trying?
Hi everybody, I would REALLY appreciate it if some had a link to a good tutorial about migration from DTS to SSIS, but without visual studio . net. For example I need to use DTS package in sql server 2005 and execute them, I used migration wizard (SQL 2005) but it didn't work because i have in my computer sql 2000 et sql 2005 and the server name is PC2, however when i use the server source PC2 and the server destination PC2 an error message appear "server destination should be a sql server 2005 instance ".
help me please.. Thanks very much anyone response my question.
Have migrated my SQL 2000 DTS packages to SSIS packages through the package migration Wizard.Also, the hurdle to schedule it as a job has been resolved.But, the concern is the package contains entrieslins for old dataserver.
Is it possible to edit the SSIS pckage using management studio or integration services?
I am in a situation where we are redesigning our Datawarehouse. Currently we have our Datawarehouse in SQL 2000 and we are rebuilding from scratch in SQL 2005. This means that even though we will get the same tables but we are planning to rename each & every attribute to make it more meaningful.
The question is like this:
We can migrate the current DTS packages to SSIS but since all the ODBC connections , field names(attributes) will change is it worth it to leverages the DTS packages ?
Also we convert the julian date to gregorian date in our DTS packages but since SSIS has a feature to convert julian date it would be redundant to migrate the packages and my feeling is to create new packages in SSIS and start on a clean slate.
Its probably a little late for this kind of question but, I'm converting several DTS packages over to SSIS and in the conversion I have the ability to run tasks as 'Execute DTS 2000 Package'. My question is, should I use that or do a full re-write of the flows to use the SSIS tasks?
So far I've been removing anything pertaining to SQL 2000 and adding the SSIS tasks. Its becoming very time consuming this way due to some of the DTS packages have a lot of tasks/flows. Is it the best way to do this or should I just use the DTS migration Wizard and then leave the .dtsx files alone once they're created from the wizard?
I am in the process of moving my ssis package development to a new machine. I have zipped up all of my development/solution files and put them on the new machine...put them in place...but when I try to test them within Visual Studio I am getting a build error that says
Error 3 Cannot create the output file "C:Documents and SettingsmandolinMy DocumentsVisual Studio 2005ProjectsTransfer and Load Enrollment DataTransfer and Load Enrollment DatainCUP Enrollment Data Pickup.dtsx": The device is not ready. . 0 0
Additionally, but related, I can only open some projects within the solution. I am getting the following error when I try to open "some" of the packages within the solution:
The Device is not ready. (Exception from HRESULT: 0x80070015)
Can someone offer some advice on just what is happening here? I would really appreciate any thoughts or advice.
Hi, I am going to work on the Migration project(DTS to SSIS).Could you plese suggest me some useful links reg Migration process and Best Practices used for Migration.
We would like to Share the BatchJobs functionality before going to the difficulties what we are facing in SSIS package Design and it's implementation.
BatchFile Process Steps:
1.Flat File is coming from the upstream server(DB2 main Frame Server) to the linux box in which the jobs are scheduled using crons.
2.The Flat file is validated whether having header and trailer information.The flat file name itself (eg. APPLWIC00077.dat ) will have it's sequence number ie 00077.This number will be checked against Flat file Header information Which also gives the sequence number information .If both number matches,The Loading will be initiated.Same way checking happens with Trailer.If anything goes wrong,mail will be sent and Loading will not happen.
3.The Job will process the coming flat file and Loading it into SQL Server Staging Table.
4.If loading is successful,then Some Sql server Procedure are called to validate the Staging Table Data,Filters,then move the Data from Staging table to Target table.
5.All Process or Steps are tracked,If anything goes wrong,Mail will be sent to Concerned Person.
Utilities Involved:
1. SQL Loader to Load the FlatFile Data into the SQL Server 2005 Database.
2. SQLPLUS to execute the PL/SQL procedures.
3. SENDMAIL or MAILX to send mails to externals.
Additional Information:
The SQL Loader Control File Splits the flatfile data by Position wise in order to load it into the particular column of the Staging Table.No column delimiters.Data are taken position wise (like column1 -> Sequence No , 1-3 -> column2 , 4-8 -> column2 )and checked against a condition By which the particular Staging Table is selected.
Difficulties We face in migration in regard to SSIS Package Design and it's Implementation:
1. We could Receive the flat file using FTP Task and we don't know how to open and validate the flat file data Like What we mentioned in STEP 2.
2. We could load the sample flat file which is properly delimited like {CR}{LF} ,{CR},{LF},semi -colon,comma,colon,tab,vertical bar .
If flatfile is messed up and data need to be processed by position wise (Scenario mentioned in Additional Information) ,We don't know how to implement this in SSIS package with the Limited delimiter option given.
FYI : Flat File columns are not fixed and ragged type.
3. We need logging information like howmany rows inserted, discarded,something in detail..How can we log these information using SSIS.This will help to send detailed mail to Application handler.It should be automated using SSIS.
4. How can we execute Procedures Using available SSIS tasks ( Executing many Procedures which is residing in Sql server 2005 using Single sql file as input to SSIS package)
Hi, I am going to work on the Migration project(DTS to SSIS).Could you plese suggest me some useful links reg Migration process and Best Practices used for Migration.
I applied the DTS Migraiton Wizard to a DTS package and it worked fine with one BIG exception. The DTS package imports a fixed-width record, length about 1500 bytes, with 60+ "parse" lines (one per field). The migrated SSIS package did not preserve any of the parse structure. That's alot of error prone worke to recreate and we have another 3 DTS packages based on the same parsing. Any suggestions on how to preserve the parsing structure in SSIS?
in my department we used livelink for a long time. now we have the order to migrate to sharepoint. i have the task to make a package in ssis which reads from livelink using the livelink api and transform the data to sharepoint.
i have begun creating a custom connection manager. it is recognized by ssis and i can select it in the new connection window. what do i have to do in the aquire connection methods? do i have to create a custom datareader?
I have a server that is sql2000. We are planning to upgrade that to SQL SERVER 2005. We also have a test 2005 server. Before going live, we are migrating all 100 DTS pkgs that we have in sql server 200 over to SSSS on the TEST 2005 sql Server. once all THE 100 DTSPKGS THAT we have in SQL 2000 PKGS Are scucessfuly migrated/tested on test 2005 server, only then we will move forward with migrating the PRODuction 2000 Server to 2005.
My question is:
1. When we are ready to migrate PRODuction 2000 SERVER, TO 2005, We will need to transfer/copy all the SSIS 2005 PKGS THAT WE HAD tested on the test 2005 server. Once we move that to prod 2005, we will need to change datasource connection to point to the PRODuction 2005 SERVER.
However my question is WHAT IS THE EASIEST/FASTEST WAY TO tranfer/copy all these (100+ ssis pkgs) 2005 ssis pkgs that are on the TEST SERVER OVER TO PROD 2005 SERVER? Do we transfer each and every SSIS PKg manually ,or is there any easy way to get these 100 SSIS Pkgs, over from TEST 2005 TO PROD 2005?
OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
I am migrating mainframe data to SQL Server 2005 and have found that from a mainframe character field with leading zeros for example the value of 00023 to a SQL Server column defined as varchar (5) the resulting column value is 23 not 00023. I need the leading zeros because these are account ids, etc. So the value is 00023 not 23. Is this some setting in SQL Server 2005 that needs to be changed or what? This is not a numeric field on the mainframe or a numeric column for SQL Server.
I am currently in the process of migrating data from Sybase to Sql server and would like to know how to test the data migrated.
As of now, we took one table data from both source and destination and compared it in Excel to check if the data migrated looks good (note, we used SSIS to migrate data). However, I would like to check if there are any other best & easy ways to apprach data validation post migration.
