Copying DTS Packages Across Servers
May 6, 2004
HI All,
Now my brain isn't quite where it should be right about now - so hopefully you will be able to put me at rest.
I have been building a new server with SQL2000 on it. Some of the stuff on our existing server is going to be migrated across - however I for the life of me cannot make any sense of how to move stuff (like DTS packages) from one server to another.
moving the Database's - no problems, but all the other guff in the tree (in Enterprise Manager) I cant seem to get.
Any assistance here is greatly appreciated.
Cheers
Troy
View 3 Replies
ADVERTISEMENT
Jun 11, 2001
Is there a way to copy saved DTS packages from one server to another?
Thanks!
Ellen
View 6 Replies
View Related
Sep 20, 1999
I have 5 DTS packages that I need to copy to another server. Can someone
please tell me how to do this.
I am a 6.5 DBA and now trying to adjust to 7.0. Big Change.
Thanks for any help.
Dianne
View 2 Replies
View Related
Apr 2, 2007
Is there any way to copy SSIS packages from one mirrored server to another? I'm considering using robocopy, but is there another solution? I'd then like to use this solution with the Transfer jobs task.
View 4 Replies
View Related
Dec 22, 1999
I have two servers running SQLserver 7.0. I have a number of jobs that are configured to import files from an AS/400 each day. I do not want to recreate these JOBS on the second server, for it took quite sometime to set up only one. I know that I can set up multi server jobs, but I want these jobs to run independant of the Master server. In other words I just want to move a copy of the job over to the next server.
View 1 Replies
View Related
Apr 10, 2008
I am attempting to move a User Database from the Production Server to a Training Server. What is the best/most simplistic way for me to accomplish this task and place this copying action on a schedule of say "every saturday morning @ 7am"
I tried the "Copy Database Wizard" within Enterprise Manager and it successfully copies the database, however when I try to schedule it to happen at a different time...it does not copy the database. It seems to only work when I tell it to perform this action now.
Please help.
Thanks,
JC
View 11 Replies
View Related
May 18, 2006
I'm trying to copy files between 2 servers on a local network from within aSQL Job (and Query Analyzer) using xp_cmdshell.xcopy but get an accessdenied message returned.I'm able to successfully do the copy from within a command window so thinkthe problem has something to do with using the default SQL Server accountbut as yet I don't know how to resolve.Any help/suggestions would be much appreciated.
View 1 Replies
View Related
May 3, 2007
We have a growing number of servers and databases on each server that all share the same (sub)set of sprocs and UDFs. DTS packages, which we use for data import, frequently need to be copied between the servers. What is the best way to maintain this? Ideally, I would like to be able to click a button and have a script creating or altering one or more sprocs automatically run aginst all DBs on all servers. Likewise, I'd like to be able to copy DTS packages to all servers.
We use SS2000 SP4 and plan to migrate to SS2005. We also use ASP.net 2.0 and VS 2005 SP1.
View 4 Replies
View Related
May 22, 2008
HI,
Actally we have different server and we work on only one server and end of the day i have to copy the list of newly or modified procedure to other databases of other server. i have create the following Sp. but i am hving problem int it. please any one can have a look..
Declare @ServerName nvarchar(max)
Declare @DatabaseName nvarchar(max)
Declare @Prod nvarchar(max)
declare @m nvarchar(max)
DECLARE @SERVER nvarchar(max)
Declare @String nvarchar(max)
DECLARE @sp VARCHAR(MAX)
SELECT @sp = OBJECT_DEFINITION(OBJECT_ID('SPNAMEMOD'))
SET @Prod = 'SPNAMEMOD'
DECLARE ProcedureScripingCursor CURSOR FOR
SELECT SQLServer,DatabaseName FROM databaseListName
-- where sqlserver is ip address and databasename is databasename on that server
OPEN ProcedureScripingCursor
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sp = REPLACE(@sp,'''','''''')
Set @String = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
Set @M = 'execute [' + @ServerName + '].' + @DatabaseName +'.sys.sp_executesql N'''
SELECT @M = @M + ' IF EXISTS (SELECT * FROM sys.objects where NAME = '''''+ @Prod + ''''') DROP PROCEDURE ' + @Prod +' '''
select @string = @string + ' ' + @sp + ' '''
SELECT @M
select @string
PRINT @M
-- Print @string
EXEC sp_Executesql @M
exec sp_Executesql @string
FETCH NEXT FROM ProcedureScripingCursor
INTO @ServerName, @DatabaseName
END
CLOSE ProcedureScripingCursor
DEALLOCATE ProcedureScripingCursor
View 2 Replies
View Related
Feb 23, 2007
hi
I've got a job which copy tables between different servers .
I am feeding the tables one by one and the process of copying is in a loop so I have cotrol over the copying process.
it works fine but sometimes randomly I am getting
Execution failed with the following error: "ERROR : errorCode=-1071636471 description=SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification".An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}".
and the process fails and this might happen for in any point and on any table and sometimes it runs all the way successfully!!
any idea what the problem might be!
Thanks
View 5 Replies
View Related
Jul 20, 2005
I'm quite new to DTS, and have to modify a number of existing localDTS packages as part of an ongoing development project. Naturally,I'm doing these on a test server first, but so far I've failed to findany utility which will simply copy the package definition from thetest server to the live one (or any other server, come to that)Since some of these packages are quite complex, it is going to be verylaborious (and error prone, surely?) to have to re-write these locallyon the production server.Is there a way of doing this, or am I missing the point somewhat?
View 2 Replies
View Related
Oct 11, 2006
I have 2 servers. DWTEST and DWSQL1. I developed my packages connecting to DWTEST in the design time. I have 32 packages, one of them is the master one and the others are child packages.
package PM calls P1, P2, P3,.......P31
i have 2 data source in my project. RDS and S0, both setup to connect DWTEST in the design. i defined rds_connection_string and s0_connection_string variables in my master package and created an XML config file and put those variables into that config file.
C:PROJECTSDEVELOPMENTSSIS ConfigurationsSERVER.dtsConfig
<DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="TC sql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWTEST;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
- <Configuration ConfiguredType="Property" Path="Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWTEST;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
built the project into directory C:PROJECTSDEVELOPMENTS1_REUSABLE_DATA_STORES1_REUSABLE_DATA_STOREinDeployment
when i deploy my packages into DWTEST and run them as a SQL Server Agent job they work because data sources are signing to DWTEST already.
when i am deploying them(using the same manifest file in the deployment directory i wrote above) into the server DWSQL1, i select config file source as:
C:PROJECTSTESTINGSSIS ConfigurationsSERVER.dtsConfig
and update data source strings in it to DWSQL1:
- <DTSConfiguration>
- <DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="TC sql" GeneratedFromPackageName="PM" GeneratedFromPackageID="{CBE946D0-279F-44F6-80A3-83D056C1E36C}" GeneratedDate="11/10/2006 10:50:44 AM" />
</DTSConfigurationHeading>
- <Configuration ConfiguredType="Property" Path="Package.Variables[User::rds_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=RDS;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
- <Configuration ConfiguredType="Property" Path="Package.Variables[User::s0_connection_str].Properties[Value]" ValueType="String">
<ConfiguredValue>Data Source=DWSQL1;Initial Catalog=S0;Provider=SQLNCLI.1;Integrated Security=SSPI;</ConfiguredValue>
</Configuration>
</DTSConfiguration>
When i run SQL Server Agent Job i defined in DWSQL1(forcing it to use DWSQL1 data source in step definition), my master package PM connects to DWSQL1, and my child packages don't use my config definitions and connect to DWTEST which is defined in the design time.
How can i force all my packages to connect DWSQL1 server using the same built files in deployment directory WITHOUT editing my packages(recreating config files or changing the definition of Data Sources and then built the project again) in Visual Studio?
Thanks to you for reading my long question...
View 2 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
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
Aug 21, 2001
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
View 1 Replies
View Related
Sep 27, 2007
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
View 2 Replies
View Related
Aug 2, 2006
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
View 1 Replies
View Related
Jul 8, 2004
What is the Syntax for a statement that can copy one MS SQL DB to another Database (on a different server). Or is this even possible?
View 3 Replies
View Related
Jul 31, 2007
Dear friends
i want to copy all stored procedures in one drives. if i do manually it will take whole day. i have to change udd length in all sps.so please anybody give sugessions
View 1 Replies
View Related
Aug 17, 2006
Hi, I have been having fun and games for well over a week now trying to get an xml file copied into an sql file, but still have no joy. I and using the bulk copy to do this and think I am close to solving it but just need a final push in the write direction.
In green below is the full source code, I think that the trouble is with the try statement part here:
Try
bulk.WriteToServer(xd)
This comes up with the error :
Unable to cast object of type 'System.Xml.XmlDocument' to type 'System.Data.IDataReader'
Basically how do I get the XML data into a reader (or IDataReader) format so that the writetoserver command can interpret it
I would be so greatful if someone could help resolve this it is becoming increasingly more frustrating
Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim objXML
Dim objXSL
' destination table
Dim connectionString As String = ConfigurationManager.ConnectionStrings("Database1ConnectionString").ConnectionString
Dim myNewConnection As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(connectionString)
myNewConnection.Open()
Dim productreader As XmlReader
Dim bulk As Data.SqlClient.SqlBulkCopy = New Data.SqlClient.SqlBulkCopy(myNewConnection)
bulk.DestinationTableName = "Product"
Dim productcount As Integer = 0
Dim settings As New System.Xml.XmlReaderSettings()
settings.IgnoreWhitespace = True
settings.IgnoreComments = True
Dim xs As String = (Server.MapPath("~/App_Data/XMLfile.xml"))
Using reader As XmlReader = XmlReader.Create(xs, settings)
While (reader.Read())
If (reader.NodeType = XmlNodeType.Element And "product" = reader.LocalName) Then
productcount += 1
End If
End While
End Using
Response.Write(String.Format("found {0} products!", productcount))
Using bulk
bulk.DestinationTableName = "Product"
Dim xd As New System.Xml.XmlDocument()
xd.Load("C:Documents and SettingsSimonMy DocumentsVisual Studio 2005WebSitesWebSite1App_Dataxmlfile.xml")
Dim xr As New System.Xml.XmlTextReader(Server.MapPath("~/App_Data/xmlfile.xml"))
bulk.ColumnMappings.Add("TDProductId", "TDProductId") ' map First to first_name
bulk.ColumnMappings.Add("name", "name") ' map Last to last_name
bulk.ColumnMappings.Add("description", "description") ' map Date to first_sale
bulk.ColumnMappings.Add("imageUrl", "imageUrl") ' map Amount to sale_amount
bulk.ColumnMappings.Add("productUrl", "productUrl") ' map UserID from Session to user_id
bulk.ColumnMappings.Add("price", "price")
bulk.ColumnMappings.Add("currency", "currency")
bulk.DestinationTableName = "Affilaite_Product_new"
Try
bulk.WriteToServer(xd)
Catch ex As Exception
Response.Write(ex.Message)
Finally
myNewConnection.Close()
bulk.Close()
xd = Nothing
End Try
End Using
End Sub
View 2 Replies
View Related
Dec 24, 2007
i have sql local database in the application . I want to copy the table from one local database to another. here the detination table is already created with
one field which is incremental and other field is image and some other fields are text. any solutions on how to do it
View 3 Replies
View Related
Mar 27, 2004
Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!
Vik!
View 4 Replies
View Related
Aug 18, 2004
Hi,
I am changing my hosting from one company to another company. How can I copy my full database along with views and stored procedures. I have only access to query analyzer and enterprise manager from where I am not able to backup the database on my local computer. As it is very urgent please suggest me a way to do this.
Thanks in advance,
Uday
View 2 Replies
View Related
Oct 11, 2004
hi,
i m new 4 sql server.
i create one app. in asp.net.
i create one database in sql server.
now,
i want to make setup for my app.
so,
i want copy database and put on c:/..../wwwroot/app folder.
how can i do this?
one more things,
i create database using enterprise manager.
i want to see my database file.
where it is stored?
somebody help me.
it's urgent.
thanks in advance.
View 1 Replies
View Related
Dec 22, 2004
It's all in the same table
How do I copy something from one column to another, but limiting it to only the first "x" characters?
so if i wanted to copy just 'ABC' instead of 'ABCDEFG' how could i do that?
thanks in advance
View 5 Replies
View Related
Apr 28, 2006
I've got two DBs in the same SQL instance. They are named TST and PRD. I am using 2.0 so there are many ASP generated tables also. Every once in a while I want to refresh data from PRD to TST. But I don't want to copy the data from ASP tables.What is the easiest way to do so?
View 6 Replies
View Related
Jun 10, 2006
Hi,
I'm trying to insert one value (an order) into the table Order and (via a for-loop) all the products in that order in the table Product, hence, one order can have multiple products (and must have at least one). I have an automatically increased value for the OrderID as the primary key for Order, and I have a foreign key named OrderID in the Product table. So far, I _think_ everything's logically correct.
However, I don't understand how to retrieve the OrderID to be able to insert it in the Product table upon insertion. I guess this is done all the time, but the only solution I can think of is to make a new SQL Command, asking for the just created OrderID to use it in the SQL Command for the products' for-loop. I'm sure that's a bad idea. :-)
Can I use relationships or so to make this automatically updated (that is, to have the Product table "check for" the OrderID and insert the OrderID upon insertion of the Product row(s))?
I hope this is clear to you. Thanks in advance for all help!
Pettrer
View 1 Replies
View Related
May 6, 2002
Hello List,
Folks, I need a help here. We are running our production database on SQL Server 7.0. We are in a process of switching the production database server to another server hardware. I was wondering, How can we copy the Logins/Passwords from my old server to the new one. Well this is very important to have all the old logins and passwords on the new server to have anybody login to it. I know, Doing Export will copy the logins. But would it allow the same old password to be on the new server?
Please help me here.
View 1 Replies
View Related
Jul 20, 2001
I have a SQL Table called Consumers that contains 495,037 records.
The record size is about 350 bytes and it has one field that is a varchar (255). I am using DTS to copy this file from SQL (7.0) to Access (97). Last week this file had about 216,000 records and the DTS Process worked okay. Today, it has 495,037 records and the DTS Process appears to lose some records (about 26,000) during the copy. In the DTS Package the SQL query is just a straight SELECT statement with no WHERE condition.
During the DTS Process the SQL Consumers Table is being copied to and Access Table. The error I am getting during the DTS Process is "Error at Destination for Row # 468608".
When I open the Access Table it shows 468,608 as the actual number of rows in the Access Table and shows ConsumerID 675820 (primary key, identity field) for this particular record.
When I link to the SQL Table via Access the Record # 468,608 shows ConsumerID 643852 and the last record shows Record # 495,037 and ConsumerID 675820.
This tells me that some records are not being copied over during the DTS Process. The last record on the SQL Table is on the Access Table. So there are probably 26,429 (495,037 on SQL Table minus 468,608 on Access Table) that are not on the Access Table.
The DTS Select Query does not have a Where Condition. Do you think some records are being lost because of the size of the SQL Table?
There is an Advance Tab on the DTS Properties Page; there are some
options like 'Insert Commit Size', Fetch Buffer Size', 'Use Fast Load', 'Keep NULLS', and 'Check Constraints' that I
need to take a further look at. Have you used any of these options and do you think any particular one may help
my problem?
There is also and 'Exception File Name' option. I will give this a try next week to see if it will write the problem
records to disk.
Thanks for your help, Kevin
View 1 Replies
View Related
Aug 23, 2001
I have been trying to copy a VIEW from one database to another, but SQL 7 doesn't let me. I use the DTS wizard and it looks like it happns but then it doesn't appear, and I have tried to refresh. Can someone please tell me what I am doing wrong?
TIA,
Bruce David
View 1 Replies
View Related
Sep 24, 2001
Hello,
This may seem like an easy question, but I have a DTS package that I need to copy to another SQL server. Is this done easily? Can it be done (i think so?).
Thanks in advance,
Jim
View 1 Replies
View Related
Mar 30, 2000
I have an 8 GB SQL 6.5 database that I need to move to another server. I know I can create the devices and the database on the new server exactly as they were created on the old server then replace the new DAT files with the old (after shuting down SQL Server). The issue I am running into is that the sort order and character sets on the two servers are different. Will this have an effect if I copy the DAT files? Am I better off using BCP? I'd hate to use BCP because of the time that it will take to move the files.
Thanks for you help
View 4 Replies
View Related
