Problem With XML -&&> TABLE Transfer.

Mar 27, 2006

i got an XML source and 1 OLE DB destination

i got an xml file

<?xml version="1.0" encoding="utf-8"?>
<Node>
<Student>
<Name>
Daren
</Name>
<Address>
France

</Address>
<Age>
27
</Age>
</Student>
</Node>



and a XML schema file

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Node">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Student">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="Name" type="xs:string" />
<xs:element minOccurs="0" name="Address" type="xs:string" />
<xs:element minOccurs="0" name="Age" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>



i create the OLED destination on a table that contains

1. Name varchar (20)

2. Address varchar(50)

3. Age bigInt null



but i got the following errors

Error 1 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Name" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0
Error 2 Validation error. Data Flow Task: OLE DB Destination [550]: Column "Address" cannot convert between unicode and non-unicode string data types. Package.dtsx 0 0



Inside the OLEDB destination,

near the table selection, I click new

it has this script :

CREATE TABLE [OLE DB Destination] (
[Name] NVARCHAR(255),
[Address] NVARCHAR(255),
[Age] TINYINT
)

View 2 Replies


ADVERTISEMENT

Transfer Data To New Table Then Transfer Indexes

May 30, 2008

Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table

if it is what flow item would be used to transfer/alter the indexes and defaults?

I'm very new to ssis so the more detail you can give the better.

Thanks

View 5 Replies View Related

SQL Server 2012 :: Data Transfer From Staging Table Into Main Table

Jan 14, 2014

What is the best way to transfer data from the staging table into the main table.

Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)

Note: Staging table may have some data same as the main table.

Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)

The problem with the above method is that, it takes a lot of time and log file size grows very big.

View 9 Replies View Related

Transfer Data From One Table To Another T-sql

Sep 7, 2005

I need to transfer data from one table to another.I will be using the SQL Query Analyzer to do this.This is not a simple transfer of data to the same structured tables.These tables are completely different, for the most part.For instance, I will be selecting certain fields of one table.....SELECT fldOne, fldTwo FROM someTableI need to take this information, one row at a time and input it into a different type table.So, something like this to insert into the other table...INSERT INTO otherTable( fld1, fld2) VALUES( value1, value2 )I've looked around for a sample to achieve this, but may have overlooked it?Anyone have a link to show this or a quick sample?Thanks all,Zath
 

View 3 Replies View Related

Transfer Table To Schema?

Mar 5, 2014

I created a schema, Admin. I have to transfer a table from the dbo schema to the admin schema. I keep getting an error that I do not have permission or the table does not exist.

Simply looking for confirmation here - is my syntax correct?

ALTER SCHEMA Admin TRANSFER MyShop.Addresses;
(MyShop is the Database, Addresses is the table)

NOTE: When I created the schema, I did not create an inner table. The syntax for that was simply CREATE SCHEMA Admin;

View 8 Replies View Related

Transfer Table With Schemas Other Than Dbo

Jan 25, 2007

My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas.
let's say I have

dbo.tableA
sch1.tableA
sch2.tableA
sch3.tableA
And I just want to copy let's say sch1.tableA to a Different DB.
If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task.
Would please recommend any other way to achieve this.
Many Thanks in advance

View 1 Replies View Related

Dynamic Table Transfer

May 7, 2008

I've got a DB2 database that contains a lot of tables. I need to extract the data from some of them and put them in a SQL Server database. The number of tables needed may change, so I need an easy way of controlling this.

So, I created a lookup table in the target SQL Server DB that lists the tables I want with any selection criteria needed.
I have a For Each loop that goes through every row in the table. using the current row, it deletes the contents of the destination table (done and working), and then tries to load the source into it.The destination OLE DB uses a variable for the table name to insert into.

The problem I've got is whilst I can set up the OLE DB Source to use a SQL command as variable, I have to do it in the advanced editor or in the properties, as the normal editor gives an OLE DB Error. This is probably due to the fact that the variable has nothing in it at this point. I've turned EvaluateExternalMetaData off to avoid any design time errors, but when attempting to run I still get :




Code Snippet
[DTS.Pipeline] Error: "output "OLE DB Source Output" (11)" contains no output columns. An asynchronous output must contain output columns.



Which I understand, as there are none until runtime. How do I get around this problem of not having any columns to map until I know which table I am processing?

Thx

Rob

View 3 Replies View Related

Table Transfer Errors

Jan 10, 2007

Hello,

I am trying to transfer a database from one server to another using the Import Export wizard in SSIS and I am consistantly getting this error on 2 different tables so far.

- Execute the transfer with the TransferProvider. (Error)
Messages
* ERROR : errorCode=-1073451000 description=The package contains two objects with the duplicate name of
"output column "ErrorCode" (79)" and "output column "ErrorCode" (14)".
helpFile=dtsmsg.rll helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} (Microsoft.SqlServer.DtsTransferProvider)



This error message is beyond cryptic and when I click on the link it sends me to a web page the just tells me that there is no information available for my current issue. I am transfering the tables to an empty database so I do not understand why I am receiving this error. I have to say that I am not impressed with SSIS at all. I know alot of developers think it's the best thing since sliced bread, however either I am doing something wrong or Microsoft needs to come out with a service pack that fixes these bugs...

Any help would be appreciated...



Thanks,

David

View 29 Replies View Related

How To Transfer A Column From One Table To Other

Apr 29, 2008


Hi,
I want to write a Procedure to transfer a column from Table1 to Table2.
I mean Table1.Last Trade to transfer to Table2.Prev Close.


INPUT:
Table1

Symbol Last Trade








ABB


ACC


AMBUJACEM


BHARTIARTL


BHEL


BPCL


CAIRN


CIPLA


DLF


DRREDDY






1127


770.2


113.9


903.2


1844.2


391


263.1


215.85


672.5


634.55



Table2

Symbol Prev Close








ABB


ACC


AMBUJACEM


BHARTIARTL


BHEL


BPCL


CAIRN


CIPLA


DLF


DRREDDY






Null


Null


Null


Null


Null


Null


Null


Null


Null


Null


OUTPUT:

Symbol Prev Close








ABB


ACC


AMBUJACEM


BHARTIARTL


BHEL


BPCL


CAIRN


CIPLA


DLF


DRREDDY






1127


770.2


113.9


903.2


1844.2


391


263.1


215.85


672.5


634.55

View 1 Replies View Related

Need To Transfer Certain Rows From One Table Into Another Table

Apr 10, 2007

On my online store, I need to figure out with sql how I can copy data from several different tables into another table: There is a table that contains all of the customers billing infoanother table has customer's shipping info The target table is for tracking and processing orders.  I will need to populate different fields from different tables, as well as insert values into fields from my code behind (i.e. customers can choose different types of shipping, payment options).  I googled this and keep running into the join statement (which I use all the time).  I don't need to join anything for displaying, I literally need insert data into a column in my orders table, just not sure what the sql syntax is for that. 

View 1 Replies View Related

Table Transfer With New Table Name In Destination DB

Sep 23, 1998

Guys,

Has anyone done this before?

Transfer table from one server to another (using EM) and create the destination table using a different table name.

The `Transfer` tool in EM do not allow name change.

I`d appreciate any help from you if you`ve done it successfully.

Thanks in advance.

Asfen

View 1 Replies View Related

Object Transfer Of Table During Production

Oct 14, 1999

My applications group wants to do a table transfer from one server to another as a means of refreshing the information on the target on a scheduled basis. That means that reports could be running against the target table during the transfer. This has introduced erroneous reports because the object transfer initially truncates the table, then transfers the data. If reports are running during the transfer, they might retrieve no data or only part of a table. I wrote a procedure to make a copy of the table to be transferred, then transfer that new table to the target machine, then drop the target table, then rename the transferred table. Now, I have the slightly smaller problem of doing a DROP TABLE when users are accessing the table. I can't set the database to 'dbo use only' because there are other tables in the database that must be available. Is this really a replication application?

Cindy Rutherfurd
SQL Server DBA
ZC Sterling Corp.

View 1 Replies View Related

How To Speed Up Table Data Transfer Thru Bcp In?

Jan 30, 2008

for bcp in,
1. use fixed length format file or delimitered file?
2. table w/o index including primary key?
3. sort the text file before bcp in (will it speed up indexes creation after data uploading?)


which pt will or will not improve the overall bcp in processing?

thx...

View 4 Replies View Related

Mssql 2005 Table Transfer

Jun 23, 2008

Hello,

In MSSQL2000, I am able to transfer files from one db to other.
using addTask -> export data.

In MSSQL2005, I am not able to transfer files from one db to other.

Please advise.

Thanking You.
Regards,
Edward

View 16 Replies View Related

How To Transfer Oracle Table To SQL 2005

Feb 6, 2008

Hi all

How to transfer oracle table to SQL 2005.

Thanks

View 1 Replies View Related

Question About Data Transfer From One Table To Another

Dec 9, 2007

Hello,

Just requesting for help about transferring data from one table to another.

For example:
In Table1, there are 3 columns:
Col1 Col2 Col3
T1C1R1 T1C2R1 T1C3R1
T1C1R2 T1C2R2 T1C3R2
T1C1R3 T1C2R3 T1C3R3
T1C1R4 T1C2R4 T1C3R4

In the target Table2, Col1 should be a string constant,e.g. "A" and Col2 from Col1 of T1 and Col3 from Col2 of T1:
Col1 Col2 Col3
A T1C1R1 T1C2R1
A T1C1R2 T1C2R2
A T1C1R3 T1C2R3
A T1C1R4 T1C2R4

I found something like
"INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1"
but this code requires colomn one of the table 1 be selected as well but the column one of the
target table 2 requires a constant string.

I also tried this but doesn't seem to work:




Code Block

dtTable1 = ds.Tables(0) ' Table1
bSQL.Length = 0
For Each dr In dtTable1.Rows
bSQL.Append("INSERT INTO Table2(Col1, Col2, Col3) ")
bSQL.Append(" VALUES(")
bSQL.Append("'" & "A" & "',") ' Constant for column 1 of Table2
bSQL.Append("'" & dr(0).ToString() & "',") ' All data in the first column of Table1
bSQL.Append("'" & dr(1).ToString() & "')") ' All data in the second column of Table1


.......

.......


Next

.....

.....


Thanks,

K

View 3 Replies View Related

Transfer Table Data To Another Database

Apr 21, 2008



Guys

i have two database on my sql server,, and i m trying to create a report in whch both database need to share their data.. @ the moment what i did, i simply create a view on one database to access the table of other database.. but is anyone has a better idea how can i transfer data from one database to another database... i think if i do with creating temp table that might resolve this problem.. but when it comes on another server of another database , how can i will do this ...
please give me any idea if u got my point .

Thanks and looking forward.

View 5 Replies View Related

Data Transfer From One Table To Three Other Tables

Feb 19, 2008

hi iam new to writting stored procedures

so anyone please help me out.

i have to transfer data from one table to three other new tables.
and if there are any duplicates in original table i have to send them to
duplicates table.the remaining data should be send to three other tables.
so can anyone help writting stored procedure for this.

thanks for your suggestions and answers


regards

ramya.

View 20 Replies View Related

Multiple Table Transfer Problem

Jan 11, 2008

We are trying to move data from 190 tables from one db to another (same tables, different structure) using ssis. The package comprises of 190 data transfers (is there an easier way to do this??).

The package works correctly except for one table. All of the tables have a column called disabled which is defined as bit datatype. However, in the table which has a problem the Disabled column has been incorrectly defined as Int. The data stored within the column is still either 1 or 0. When we run the package it executes ok and we don't get any errors but looking at the destination tables, the one with the disbaled column defined as Int contains -1's instead of 1's.

I had a look at the xml for the package and noticed that SSIS has used the same definition of the Disabled column for all source tables. This means that it has defined it as BOOL (which is correct on all the other tables) even though it should be i4 on our problem table. I assume this has something to do with the problem but I'm a bit stumped and out of time.

If anyone can shed any light on this I would be most grateful.

Thanks.

View 4 Replies View Related

DTS Transfer Table With Text Column Slow

Jun 30, 2004

I am using DTS to transfer some tables from one server to another as part of a migration. We want to be down for as little time as possible, but we need the most up-to-date copy of the database tables in question.

I am currently testing the transfer process in our test environment by migrating the data from one database to another on the same SQL instance.

There are 7 tables to transfer and the total size of the database is 450 MB (with around 117 MB used). The two largest tables have around 17,000 records each.

One table (the header) has no text column and it takes just a few seconds to transfer. The other table (the detail) has two columns, one of which is a text column (actually, its not fair to call it the detail table; the relationship is actually one-to-one, but for the sake of this discussion, let's leave it at that).

The header takes seconds to transfer, but the detail takes up to 18 minutes.

Physically, our test server is quite robust; 2 processors, a 3 disk RAID-5 for the data files and a separate RAID 1 partition for the logs. Performance counters don't indicate any real issues: during the transfer, the disk utilization on the data partition occasionally spikes to a high level, but comes right back down until the next spike (the spikes being separated by about 1 minute. No issues with memory, paging or CPU.

I have removed the clustered index on the affected table as well as the PK. No help.

Are text columns just slow? Is there something that I am missing?

Regards,

hmscott

View 4 Replies View Related

SQL 2012 :: Can't Transfer Table From One Schema To Another Within Same Database

Oct 7, 2015

I am trying to do the above using the following...

ALTER SCHEMA [dbo] TRANSFER [schemaame].[tablename];

...but getting the following error...

Msg 15530, Level 16, State 1, Line 5

The object with name "tablename" already exists.

The tablename I see in SSMS is schemaame.Tablename

When I look at the properties of the table, it says the name is 'tablename'.

I cant work out how I can change the schema of the table. Also, surely having a in a schema name is not recommended, right?

View 1 Replies View Related

Is There A Way To Transfer Ntext Data From One Table To Another? MSSQL2000

Aug 29, 2006

Is there a way to transfer ntext data from one table to another?I tried thisUPDATE [projects]SET [description] = (SELECT [description_ntext] FROM [table] WHERE[id]=1)WHERE [id_project] = 1;and thisDECLARE @DESCRIPTION ntextSET @DESCRIPTION = (SELECT [bids].[bid_conditions] FROM [bids],[projects] WHERE [bid_accepted_id] = [bids].[id_bid] AND [id_project] =@ID_PROJECT);UPDATE [projects]SET [description] = @DESCRIPTIONWHERE [id_project] = 1;none of those work in MSSQL2K,error reported is "The text, ntext, and image data types are invalidfor local variables."

View 2 Replies View Related

Changeing The Table Name While Using Transfer Sql Server Object

Mar 30, 2007

Hi,
I am wondering using transfer sql server objects task in a sub-package and feeding tableslist property from the parent package. which works fine.
problem :

I want to be able to change the name in the fly so if I have
TableA I want to copy it for the destination as TableB
is there any work arround this just using transfer sql server objects task.
Thanks

View 7 Replies View Related

Table Structure And Data Transfer From SQL2000 To Access (.mdb)

Aug 8, 2006

Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...

View 3 Replies View Related

How Can I Transfer Text File To Table Located In Different Domains

Dec 26, 2007



Hi,

I have a package which is pulling data from a text file. The text file is located in different domain.

when I copy the text file manually to same domain where my sel server is located then Job is working fine.

How can I avoid this. Do we have any oher options ?

View 2 Replies View Related

How To Transfer A Table Between Databases In SQL Server 2005 Express

Jan 10, 2007

Hi,
In SQL Server 2005 Express I currently have 2 databases which I€™m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.



View 1 Replies View Related

Transfer Manager Does Not Transfer Stored Procedures

Oct 22, 1998

I am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??

View 3 Replies View Related

EM Transfer Manager Fails Moving Data After 80th Table?

Sep 14, 1999

Hello:

I am running on mssql 6.5, sp4. We have been trying to use EM transfer manager to move one test database on one server to another database on another server.
We are dealing with 135 tables on this database. The transfer works up until about the 80th atble and then just dies but the scheduled task says it failed and check error log. The transfer creates the tables on the destination database but only loads the data until this one table.

WE use all of the options in EM Transfer manager which are st as defaults.

THere is no one on the source or destination databases locking this table.

Other smaller databases were successfully transferred from one database on one server to the other database on the other server without any problems today and yesterday.

Has any one run across something like this?

THanks.

David Spaisman

View 1 Replies View Related

How To Refresh Transformation In SQL2005 SSIS (I'm Trying To Transfer Random Sql Table Into Dbf)

Jun 13, 2007

Im from Russia, sorry if my english is not very good.

Here's the case:

1)-------------------------------
I made a DTS-package in sql2000 that transfers the [sql table] into [dbf file] via jet4.
First i create (in delphi) the empty dbf with the same name and columns same as in sql table.
Second, I run my DTS with variables - source and destination table names

In DTS there is source, destination and transformation . After I send the Variables(table names)
, the transformation "arrow" needs to be "refreshed" to make column names in both tables correspond each other. For that in transformation I chose ActiveXScript Mode and wright VB Script:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()

dim i

For i = 1 To DTSSource.Count
DTSDestination(i) = DTSSource(i)
next

Main = DTSTransformStat_OK
End Function

And it works

2)------------------------------
I want to do same thing in sql2005 SSIS but don't figure out how...
I managed to make a package that recieves (in variables) table names and runs correctly.
But after I change those variable names into any other it crashes -
Description: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Of cource this happens 'cause I didn't "refresh" the transformatoin (and maybe also source and dest), but I don't know how.

Anyone can help ?!

View 8 Replies View Related

Reg:SSIS Package To Transfer Data Between SQL Table And Olap Cube

Apr 2, 2008



Hi All,
can anybody help me in creating the SSIS package to transfer the data from SQL table in database engine to OLAP cube in Analysis services


Thanks in Advance.
Archana

View 1 Replies View Related

Dynamic Feed Of Table Name To Transfer SQL Server Object Task

Jan 10, 2007

Hi
I would like to be able to feed the List of tables to the Transfer SQL Server Object Task dynamically.
I have got a foreachloop container which it feeds the table names into a variable @table_name (string).

Transfer SQL Server Object Task is with in foreachloop container

I did add an expression into the property of Transfer SQL Server Object Task and assign the tablelist property to @table_name


I would be grateful if you can give me any hint.
Thanks
S

View 19 Replies View Related

Transfer Sql Server Objects Task Truncate Table Fails Due To Foreign Key

Aug 10, 2006

Hi

We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.

I set my Transfer Sql Transfer Objects Task to use the following settings:

CopyData = true

ObjectsTocopy = All Tables

As soon as it hits a table that has a foreign key, I get the following error:

"Truncate data failed for Table nnn"

I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.

First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?

Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?



Kind regards

Ben

View 3 Replies View Related

Transfer SQL Server Objects Task (for A Table): Can It Be Split Into Smaller Batches

May 29, 2008

We are using the Transfer SQL Server Objects Task to transfer a large table. The trans log is filling up for this table. Is there a method to split the Data Transfer Task into smaller batches? (Smaller tables are transferring without issue.)

Thanks.

View 2 Replies View Related







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