DTS To SSIS Migration Question
Aug 2, 2007
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?
View 4 Replies
ADVERTISEMENT
Nov 12, 2007
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.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim fn
Dim mm, dd
mm = Month(Now)
dd = Day(Now)
'Padded a 0 in front of the month if its less than 10 to maintain the YYYYMMDD format
If cInt(mm) < 10 then
mm = "0" & mm
End If
'Padded a 0 in front of the month if its less than 10 to maintain the YYYYMMDD format
If cInt(dd) < 10 then
dd = "0" & dd
End If
'Dev Server
fn = "D:Documents and SettingsUserDesktopDTSMigrateSamplesAEC_" & Year(Now) & mm & dd & ".txt"
dim oPackage
dim oConn
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections("HR Feed")
oConn.datasource = fn
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
End Function
View 3 Replies
View Related
Jan 10, 2006
Good Day,
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?
Thanks for any help/suggestions.
View 8 Replies
View Related
Sep 6, 2007
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.
View 3 Replies
View Related
Nov 6, 2006
Well..
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?
View 1 Replies
View Related
Apr 5, 2007
Hi guys,
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.
View 1 Replies
View Related
Feb 16, 2006
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.
Is there anybody who was in such situation ?
Please advise.
Adarsh Mathur
View 2 Replies
View Related
Sep 21, 2007
Good Day,
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.
Thank you in advance.
View 9 Replies
View Related
Aug 8, 2006
Greetings,
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.
Thanx a million in advance.
Regards
View 1 Replies
View Related
Mar 26, 2008
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.
Appreciated Assistance/guidence in this regard
View 3 Replies
View Related
May 2, 2008
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)
Thanks in Advance!
Your input is priceless.
Regards,
Prabhu.M
View 4 Replies
View Related
Mar 26, 2008
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.
Appreciated Assistance/guidence in this regard
View 6 Replies
View Related
Mar 18, 2008
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?
TIA,
barkingdog
View 2 Replies
View Related
Apr 9, 2008
hi
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?
thanks in advance for your help
peter
View 9 Replies
View Related
Jan 29, 2008
I have a question regarding a DTS (SQL server 2000 package) to SSIS (SQL SERVER 2005 package) migration.
which is the best forum to post my question ?
View 1 Replies
View Related
Aug 7, 2006
I depolyed several package in a server A msdb in some logical folders
E.g. msdbTask1package1
msdbTask2package2
msdbTask3package3
I would like to migrate the package to other server B and keep the logical folders tree.
I know I can reploy the packages in B and move them to the logical folders tree one by one, it is too slow.
Is there any fast method to migrate the package and keeping the tree?
Thanks.
View 1 Replies
View Related
May 30, 2008
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?
View 10 Replies
View Related
Jul 13, 2007
Hi,
Hi,
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.
Thanks
-VD
Venkatesh
View 1 Replies
View Related
Jun 8, 2007
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?
View 6 Replies
View Related
May 22, 2007
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.
View 11 Replies
View Related
Mar 18, 2008
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.
View 4 Replies
View Related
Jul 6, 2015
exporting an SSIS from a 2008 R2 SQL Server and re-import it into a SQL 2012.
I have inherited an SQL 2008 R2 Server running two SSIS packages. I have both as .dtsx files and & a manifest to install.The task at hand is now to migrate those to a new SQL 2012 server.[There is also an IIS running on that machine and project-specific .dll and stuff within the IIS document root. I do not know if these .dlls relate to the IIS web page or to the SQL .dtsx) But the migrated web site runs fine].
Installation of the packages into the MSDB works out flawlessly and one of the scripts runs fine, but the other fails to run with the error:
"Error: the binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully."
Google tell me something about a "Pre-compile option"-setting on server as explained here. I can not find this option setting anywhere in SSMS. Also as the migration is from 2008 R2 -> 2012 this should not be relevant as in both versions pre-compilation should be automatic, right?
- I installed visual studio 2013 community
- I installed SSDTBI
- Start the "Integration Services Import Project Wizard"
to import the SSIS directly from the "Integration Services Catalog"
However, things don't quite work very well - Trying to import (from the locally installed SQL instance) it will not display the tree of SSIS stuff, but only the root directory.
So importing directly from the running system won't work. Let's see if we can get somewhere with the .dtsx As I _do_ actually have the .dtsx files here, why not open them up directly in Visual studio and try to get compiled whatever needs to be compiled.I create a new "integration Services" Project and open up the .dtsx into this project. ==> LOTS of errors of all kind.
(The job of this script is to fetch messages from an Exchange.)But opening up this specific bit of code doesn't work a bit - there is no binary code in it and how to reate it or where to get it from...
View 4 Replies
View Related
Jan 26, 2005
I have developed a database on my local machine using MSDE. I am using Visual Studio.net to add tables/stored procedures to the database. I have created asp.net pages. I need to get this database on a web server that runs Enterprise SQL Server. Can I just use the 'sp_detach_db' and 'sp_attach_db' to move the database between servers?
View 4 Replies
View Related
Aug 29, 2001
Hi Everyone,
I am migrating my old sql server database to the new hardware, in the processes I migrated all the databases from old sql server 7.0 to new sql server 7.0, and also generated the scripts for all jobs and executed in the new server, all the jobs appear but when I go and see in the job steps, I don't see any steps in the jobs, I tried different ways but I am not able to get the steps.
Can any one help me out..............
I have about 160 jobs so I cannot make new steps for each and every job.
Thanks in advance.........
Regards,
Madhuker.
View 1 Replies
View Related
Aug 30, 2001
Hi Everyone,
I am migrating my old sql server database to the new hardware, in the processes I migrated all the databases from old sql server 7.0(All tables from MSDB Database, sysjobs, syshistory etc) to new sql server 7.0, and also generated the scripts for all jobs and executed in the new server, all the jobs appear but when I go and see in the job steps, I don't see any steps in the jobs, I tried different ways but I am not able to get the steps.
Can any one help me out..............
I have about 160 jobs so I cannot make new steps for each and every job.
Thanks in advance.........
Regards,
Madhuker.
View 2 Replies
View Related
Sep 20, 2001
I want to do a data transfer but i am not sure how ..from oracle to sql server.
i want to know if its required to install the oracle client to configure so as to transfer data or not.
I heard from someone that if we change the win.ini file entry for oracle.ini and place the tnsnames.ora file i spcified location it will work but it does not what is the way to deal with..
How do i perform this with the odbc configuration for oracle..and use the same in the dts.
Thanks in advance.
View 1 Replies
View Related
May 9, 2000
Hi Every one,
I am upgrading from one computer to another.
I have few questions.
1. Is it neccessary to run the SQL Services on Domain User Account on both machines or on 6.5 machine.
2. How to check the all User logins exists in Master database.(I read in BOL that all user logins must exists in Master Database)
3. How much time it will take to complete for 10 GB database.
Please give me anohter suggetion which will help in this regard.
Thanks,
Dindu.
View 1 Replies
View Related
Aug 28, 2000
Can any one tell me the steps to migrate sql server 7.0 from one server to another server(Production databases). Its a 24x7 shop. The down time should be minimal. Iam looking for a most efficient way of doing that.
View 1 Replies
View Related
Feb 19, 2001
Is it possible to migrate SQL Server 6.5 to 2000? We have an ACtive/Active Cluster Environment. It it's possible, please give me a brief guidance on how to go about it. Thanks for your help.
View 1 Replies
View Related
Aug 11, 1999
has anyone migrated a ms sql 6.5 server to a mssql server 7.0
on 2 different machines? and if so how the heck didja do it?
the 7.0 server keeps telling me that it cant do it until the 6.5 server
is upgraded.....help...help....help....the 7.0 server has the 6.5 one registered it just wont connect to it....tells me that it has to be a 7.0 server.....help
thanks
tony
View 1 Replies
View Related
Jan 28, 2002
What are the differneces between using Backup/Restore vs Copy Database Wizard when Migrating a SQL Server 7.0 Database to SQL Server 2000 ?
View 2 Replies
View Related
Apr 9, 2001
I have to migrate INFORMIX database to SQL Server 7.0 and I was wondering if there is anyone out there with any experience? I want to know the procedures and how to go about it towards a successfully INFORMIX migration to SQL Server. I look forward to hearing from you.
Regards,
View 1 Replies
View Related
Jul 6, 2004
Hi,
Does any one aware of any tool for sybase to ms-sql migration, which will migrate store procedures/triggers from sybase to ms-sql server 2000.
Thanks
Vaishali
View 3 Replies
View Related
