SSIS Migration Between Servers

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


ADVERTISEMENT

DTS Migration To SSIS

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

DTS Migration To SSIS

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

Migration From DTS To SSIS

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

Migration Of DTS To SSIS

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

SSIS For Data Migration

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

Migration Of DTS Packages To SSIS

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

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 View Related

Data Migration Using SSIS In SQL 2005

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

Migration From Sql Server 2000DTS To SSIS

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

Regarding SSIS Implementation Difficulties In Migration

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

Migration From Sql Server 2000DTS To SSIS

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

SSIS Migration Wizard Question

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

Migration From Livelink To Sharepoint Using Ssis

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

Where Is The Forum For DTS -&&> SSIS Migration Questions?

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

Migration SSIS In Msdb To Other Server

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

SSIS Pkgs Migration Question

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

Migration Issue From DTS 2000 To SSIS SQL 2005

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

How Can I View The Design Of The Ssis Package After Migration

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

SSIS Leading Zeros Gone After Migration Of Mainframe To SQL Server

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

DTS Migration To SSIS - Oracle Unsupport Data Type

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

SQL 2012 :: Migration Of SSIS Script - (binary Code Not Found)

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

SSIS And SQL Server On Different Servers

Mar 22, 2006

We have been using SQL Server 2005 for our applications for some time now and had a question regarding SSIS.
What is the strategy to adopt if we want to have the SQL Server DB installed on one server and SSIS and Reporting Services running on another machine.

When we tried to actually do it, we ran into this problem. We had a package which was scheduled as a job on one of our database servers, A and loaded data into A from a sybase database. This job was working fine.

When we modified the package to load into another database server B, and scheduled the package as a job again on the SQL Agent on A, the package fails. The SQL Agent logs simply say €˜The package execution failed.€™

On enabling logging for the package, the Event logs on the A say €˜Failed to acquire connection €˜ServerB.sy_nawork.SQLuser1€™. Connection may not be configured correctly or you may not have the right permissions on this connection.€™ The SQL Server log on B logs a €˜Login failed€™ for user SQLuser1.

Why might this be happening?

I believe details of all connection managers used in a package are stored in the package itself. Is that correct.  
For this particular package, we are using a connection manager which connects to the database B using SQL Server authentication - user SQLuser1 and the corresponding password. We are sure that the credentials supplied are correct.

Would appreciate pointers to resolve this issue.

View 1 Replies View Related

How To Query Two Tables In Two Different Servers In SSIS

May 21, 2008



Hi All,

I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.

Hope any one will reply soon.

Thanks,
lmp

View 7 Replies View Related

Using Linked Servers In SSIS Package

Mar 21, 2006

We are trying to populate a table from tables on 2 different servers. We are using an SQL statement that runs fine on the reports server, i.e.

select a.field1, b.field2

from server1.db1.dbo.table1 a join server2.db1.dbo.table1 b on a.field1 = b.field2

Not sure if the syntax is right, but I have it right and working in a query. Anyway, when I try and run this via a SSIS package, is complains that it cannot make a connection to server2. Do I have to set up 2 data sources, or will SSIS use linked servers?

View 3 Replies View Related

SSIS - How To Run A JOIN Between Databases On Two SQL Servers?

Jan 17, 2006

Hi All,

I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 I would simply do the following:

From Server A:

sp_addlinkedserver B

INSERT dbo.ResultsTable

SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable

INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID

sp_dropserver B



What do I need to do to perform the same operation in SSIS world?

Thank you !

View 1 Replies View Related

SSIS Package To Loop Through All Servers

May 23, 2008



Hello all,

I currently started working on SSIS. Here is my first assignment; I am trying to get info from all servers. I was able to do it from one server, how can i extend this package so that my package touchs every server and get info from them.

Thanks

View 3 Replies View Related

Use SSIS To Copy Files Between Servers?

Apr 27, 2006

This is a question of whether or not to use SSIS to solve a problem.

I need to copy SQL Server database backup files from a server in the DMZ to a fileserver inside the firewall. The SQL Server is not allowed to write it's backup files directly to the fileserver, so they are written to local disk. A connection can be made from inside the firewall to the SQL Server to copy the files off.

So, I'm considering SSIS for the job. Is it possible to use SSIS to perform the file copies from one remote server to another? If so, is the FTP task required, or can File System tasks be used?

An alternative would be Windows scripting, xcopy, robocopy, etc. but I like the features of SSIS and would like to take advantage of it's flow control, error handling, database scheduling, etc.

Any tips, sample code, etc. would be appreciated.

Thanks,

david

View 4 Replies View Related

SQL Server 2008 :: SSIS - For Each Loop Through Multiple Servers

Jan 30, 2015

I have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?

View 1 Replies View Related

How To Have SSIS Package Operate On Multiple Servers Via Configuration?

Feb 9, 2008

How do I create, say an "update statistic" maintenace package that works on multiple servers and databases?

For example, how do I get the package to operate on the following servers and tables:
ServerA, tableX, tableY, tableZ
ServerB, tableM, tableN

Also, I would like to save the package configuration and store it on SQL Server. So if I want to add more servers or tables to the list, the SSIS package will pick it up at run time.

Is this possible?

View 7 Replies View Related

Running SSIS On Different Servers Without Changing Connection Information

Nov 14, 2007

What is the best way to run SSIS scripts on different servers without changing connection information. Our test server is ppntt140 and our production server is ppntd110. If I create a script on server ppntt140 what can I do so I can move it to server ppntd110 without changing any connection information? Database names are the same, it is just the server that changes. What is the best way to handle this? Thanks in advance.

View 4 Replies View Related

SSIS: JOINING TABLES FROM TWO SERVERS - BETTER WAY THAN SORT -&&> MERGE JOIN?

Jan 20, 2006

Setup:

I need to run an Insert query which pulls data from a table located on server A database AA Table AAA conditional on (or JOINED with) Table BBB in database BB sever B. In SQL 2000 it could be done as:

From Server A:
sp_addlinkedserver B
INSERT dbo.ResultsTable
SELECT SourceTable.* FROM B.BB.dbo.BBB SourceTable
INNER JOIN A.AA.dbo.AAA ConditionTable ON SourceTable.RecID = ConditionTable.RecID
sp_dropserver B


In SSIS one of the possible solutions is to use a package which does the following:
OPEN A + OPEN B-> SORT A + SORT B->MERGE JOIN A and B->OUTPUT RESULT

The problem with this approach is that it's extremely slow for large datafiles (50M records each)

Questions:

1) In the procedure above could the SORT step be avoided?
2) Is there another approach to run cross-servers JOIN in SSIS?

Thank you

View 3 Replies View Related

Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help

Nov 21, 2007

I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.

We use rectangles to force page breaks to page 2 and to page 3.

When running the report on the report server, it shows and prints fine.

When running the report from the QA website internally, it shows and prints just fine.

When running the report from the production website from a machine internally, it shows and prints just fine.

When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.

I need help in determining where I even begin with trouble shooting this!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved