Move Data From Source Database To Destination

May 14, 2008

I need to write TSQL script that will insert, update, delete rows in a production db from new, updated and deleted data from staging db. They are both in different servers and I can only use TSQL. Basically, the production DB needs to be synchornized with the staging DB.

I was thinking using dynamic sql, cursors, and linked servers. Any ideas?

Jim

View 2 Replies


ADVERTISEMENT

How To Replace Data Of Tables From Source To Destination Database Using SSIS

Apr 29, 2008

I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.

View 5 Replies View Related

SQL 2012 :: SSIS Data Flow Items Tab Missing For Adding Data Source / Destination

Apr 3, 2014

I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.

View 4 Replies View Related

T-SQL (SS2K8) :: Load Data From Flat File Source Into OleDB Destination By Changing Data Types In SSIS

Apr 16, 2014

I have an source file and i have to load it into the data base by changing datatype of the columns in ssis

View 1 Replies View Related

Processing Data From Source To Destination Table

Sep 22, 2013

I want to process data from source table to destination table without using cursor.

At this point; we are creating temp table and inserting data from source to temp table. once we get data into temp table; using while loop we are processing record one by one to destination table.

while executing stored procedure; we noticed that there are few records in source table which are invalid and stored procedure is terminating from such records.

Any better approach to log INVALID data and resume code to process next record instead of terminating.

View 7 Replies View Related

Transferring Data From Read-only Source To Destination.

Oct 17, 2006

Hi,

I have read only permission in the source (OLTP) database. The source database is running in SQL Server 2000 and the size is more than 200 GB. I need to pull data from source and load target which is running in SQL server 2005.

Following are the objectives I want to achieve.



Data should be loaded on incremental basis.

Whatever changes take place (Update/Delete) in source, that should be replicated to already uploaded data.

Here I want to mention that, the source database does not have any identification key or timestamp column like Updated_Date by which I can filter the data which are recently inserted or updated into the source and upload the same. The source does not maintain any history data also. So I do not have any track of deleted record also.

I don€™t have any scope to change the schema in the source. In this scenario can anybody suggest me the best approach to achieve the above mentioned objectives?

Can I retrieve only the recent updated or inserted date form transaction log back up. Can log shipping solve the give the solution?

One more question. Say I have a table and I am exporting/importing all the data from/to my target table using SSIS or DTS. In this scenario does using query or using directly the table affects the performance?

Regards

Sudripta Rakshit

View 4 Replies View Related

Lookup For Compare The Source And Destination Data

Nov 19, 2007



Hi

i want to compare the source and destination data my source is Oledb source and destination is sql server destination I want to Compare the Data in destination with source and also want to add validation like iF Any row is there is destination but not in source delete that row if any row in source but not in destination Insert that row nd compare source with destination respect to Primary key if There is change in any column of that row Update that if not change than move to next record. Please tell me what is the sutable object and what to do
Please Help me Out
Thanks
Sandeep

View 10 Replies View Related

Lookup For Compare The Source And Destination Data

Apr 24, 2008

Hi,

I am new to SSIS can u help me out in this i have a source(Flat File) and target SQL Server 2005. Source has got 2 columns- Col1 and Col2 even target has got 2 col's Col1 being the PK. I have created a package that checks if target and if the record exist it updates and if it does not it inserts. My package looks like

Source - lookup on target- Conditional split- Derived Column and then 2 OLE DB Destinations 1 for inserts and 1 for updates.

I have created a relationship in lookup with col1 from source and col1 from target and col2 as lookup col and connected red output to 1 OLE DB for inserts and redirect rows to it. Green out put i have taken to conditional split and gave condition like Col2 from source is not equal to lookup col2. I run the package with this it is inserting new records but not updating it.

I tried to add derived column after conditional split but lacks in writing expression that says update col2 records if they changed at source. Can u help me out in this scenario. I would appreciate if you could get back to me ASAP.

Thanks

View 5 Replies View Related

Excel 2007 Data Source/Destination

Dec 5, 2007

I've SSIS 2005 SP2 and Excel 2007 installed. How come I do not see Excel 2007 on the Excel version list?

Thanks,

Ash

View 7 Replies View Related

SQL 2012 :: SSIS - Transfer All Data From Source To Destination

Sep 1, 2014

I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.

I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.

View 2 Replies View Related

Data Flow Task - OLEDB Source / Destination

Nov 9, 2006

Hi

Inside a data flow task, i have a oledb source and destination. In my situation, I need to pull data from a table in the source, but also hard code some columns myself, which means my source is a blend of data from table, hard coded data, which will then have to be mapped to columns in oledb destination. Does anyone which option to choose in the oledb source dropdown for the data access mode. Keep in mind, i do need to run a a select query, as well as get data from a table. Is it possible to use multiple oledb sources and connect to one destination, because that is really what intend to do here. I am not sure how it will work, or even if its possible. Basically my source access mode needs to be a blend of sql command and table columns, how would that be implemented? Any help or advice is appreciated.





MA

View 4 Replies View Related

XML Source To Data Conversion To OLE DB Destination - Loss Of CDATA

Apr 9, 2007

I'm trying to build a DTSX package that FTP's an XML file to the local file system and then imports it into an existing table.

My "Data Flow" for the package starts with an XML Source component and then goes to Data Conversion component and then to an OLE DB Destination component.

It all executes with out error and seems to work fine, but when I look at the data in the table after I've run the package it seems to have inserted the appropriate number of rows from the XML file but all of the column values are NULL.

All of the data in the XML file is surrounded by <![CDATA[ ]]> and I discovered that if I remove the CDATA wrapper by hand then it inserts the data properly. The only problem is that I'm not in a position to have the data provider remove the CDATA tags and some of the data in the XML file needs the CDATA wrapper or else it will not validate.

Anyone know of anything I can do to get the CDATA to import properly?

View 15 Replies View Related

SQL Server Admin 2014 :: How Data Will Transfer From Source To Destination

Aug 6, 2014

Does Replication use linked server.

If not then how data will transfer from source to destination.

Note : As per my knowledge it is not backup and restore like logshipping.

View 2 Replies View Related

Source And Destination Databases On Same Server While Data Transfer Using SSIS

May 9, 2007

Hi,

I am having one query regarding data transfer using SSIS. If we use

DTS packages for the data transfer between two databases then the source database and

destination database must be on different db servers or instances.Here, I

am talking about the DTS Packages with Distributed Transactions enabled.

I need to know that whether this constraint has been rectified with SSIS Packages or it still persists.



One more thing is that while transferring the data, can we view/insert/update source database or it is locked if the transfer is in process.



Please reply..........



Thanks and Regards,

Rajesh

View 5 Replies View Related

Importing Data From One Source In Two Destination Tables Linked By A Foreign Key

Nov 14, 2006

Hi,

I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :

table MOTHER (MOTHER_ID, MOTHER_NAME)

table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)

After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.

My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".

The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...

Is there a solution to chain two insertions with a foreign key constraint in a data flow?

Thanks

Regards,

Arnaud Gervais.

View 4 Replies View Related

DTS Works, Job Fails!(data Source Foxpro And Destination SQL Server 2000

Sep 26, 2006

The DTS works perfectly when I run it manually. However, when I run itas a job it fails. Before you ask if i'm running it under differentsecurity context. I have already made sure of that. I was logged intothe server through remote viewer, when I created and ran the package,as well as scheduling the job. So the accounts they're running underare consistent. They're the same accounts as the SQL Agent is runningunder and it's the sys admin account.The data source is a Fox pro database with a pull of two tables. I amusing Microsoft OLE DB Foxpro driver as my source connection and OLE DBConnection for SQL Server as my destination. I am doing a simple tableto table transformation. The path of my connection is a mapped Drive:E:Main. There are other packages and jobs within my job queue that arepointing to the same database and they seem to run fine using the abovemapped drive. The ONLY difference between this package and otherpackages are that, they're few months old and this one was created lastnight. I have also enabled logging on this package and here is thebelow error when the job fails:Package Steps execution information:Step 'DTSStep_DTSDataPumpTask_1' failedStep Error Source: Microsoft OLE DB Provider for Visual FoxProStep Error Description:Invalid path or file name.Step Error code: 80040E21Step Error Help File:Step Error Help Context ID:0Step Execution Started: 9/23/2006 11:39:17 AMStep Execution Completed: 9/23/2006 11:39:17 AMTotal Step Execution Time: 0.031 secondsProgress count in Step: 0

View 1 Replies View Related

Error When Using Configuration File For Source And Destination Connections In A Data Flow Task

Mar 7, 2008

Hi all,

I have a package that does simple exporting from an excel sheet to a table.
I used a Dataflow task with Excel Source and OLEDB Destination Components.
And i created Package configurations for Source and Destination Components.
After than when i execute the package i get the following error.


Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:TEST_ETLLPL_Config2.dtsConfig".

Information: 0x40016041 at ProductDetails_Import: The package is attempting to configure from the XML file "D:TEST_ETLDBCon2.dtsConfig".

SSIS package "ProductDetails_Import.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at ProductDetails_Import, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, DTS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "ProductDetails_Import.dtsx" finished: Failure.

The program '[2416] ProductDetails_Import.dtsx: DTS' has exited with code 0 (0x0).

I have been trying to troubleshoot the error message given below from last evening.


I have been trying to troubleshoot the error from last morning.
Counld not figure out what is causing this error to occur.

Please help!!!!
Any pointersSuggestions would be highly appreciated.

Thanks & Regards

View 3 Replies View Related

How To Retrieve Connections Collection Inside Custom Data Flow Tasks (source/destination)

May 16, 2008

Hi,

How do I retrieve the connections (connection managers) collections from Custom Data Flow destination? ComponentMetadata.RuntimeConnectionCollection is empty. I would like to be able to access all the connections defined in the package from the custom data flow task.


I came across code in which it was possible to access the Connections collection using the IDtsConnectionService for custom task (destination). The custom task has access to serviceProvider, whcih can be used to get access to the IDtsConnectionService interface but not the custom data flow task.


Any help appreciated.


Thanks

Naveen

View 5 Replies View Related

Integration Services :: DefaultBufferMaxRows - Is It Determined By Row Length Of Data Flow Task Source Or Destination

Oct 18, 2015

We have a single generic SSIS package that is used to import several hundred iSeries tables into SQL. I am not looking to rewrite the process. But I am looking for ways to improve performance.

I have tried retain same connection, maximum insert commit size, lock table (tablock), removed some large columns, played with the log file location and size, and now I am working to tweak the defaultbuffermaxrows.

To describe the data flow task - there are six data flows tasks (dft)  working at the same time. Each dtf has their own list of iSeries tables and columns and the corresponding generic SQL table names. Each dtf determines their list of tables based on the number of columns to import. So there is dft30 (iSeries table has 1-30 columns to import), dtf60 (iSeries table has 31-60 columns to import), etc. The destination SQL tables are generically called Staging30, Staging60, etc. Each column in the generic Staging tables are varchar(100). The dtfs are comprised of an OLE DB Source and an OLE DB Destination.

The OLE DB Source uses a SQL Command from Variable to build a SELECT statement. The OLE DB Source uses a connection manager that uses an IBM iAccess IBMDA400 provider.  The SQL Command ends up looking like this for the dtf30. This specific example is importing from the iSeries table TDACLR and it only has two columns so it will be copied to the Staging30 table.

select TCREAS AS C1,TCDESC AS C2,0 AS C3,0 AS C4,0 AS C5,0 AS C6,0 AS C7,0 AS C8,0 AS C9,0 AS C10,0 AS C11,0 AS C12,0 AS C13,0 AS C14,0 AS C15,0 AS C16,0 AS C17,0 AS C18,0 AS C19,0 AS C20,0 AS C21,0 AS C22,0 AS C23,0 AS C24,0 AS C25,0 AS C26,0 AS C27,0 AS
C28,0 AS C29,0 AS C30,''TDACLR'' AS T0 from Store01.TDACLR

The OLD DB Source variable value looks like the following, but I am not showing the full 30 columns

select cast(0 AS varchar(100)) AS C1,cast(0 AS varchar(100)) AS C2,cast(0 AS varchar(100)) AS C3,cast(0 AS varchar(100)) AS C4,cast(0 AS varchar(100)) AS C5, ... cast(0 AS varchar(100)) AS C30.

The OLE DB Destination uses OpenRowSet Using FastLoad From Variable. The insert into Staging30 ends up looking like this.

insert bulk STAGE30([C1] varchar(100) ,[C2] varchar(100) ,[C3] varchar(100) ,[C4] varchar(100) ,[C5] varchar(100) , ...  ,[C30] varchar(100) ,[T0] varchar(20)

Of course we then copy and transform the Staging30 data to the SQL table that equals T0.

But back to defaultbuffermaxrows. Previously the dtfs had default values of 10000 for DefaultBufferMaxRows and 10485760 for DefaultBufferSize. I added a SQL task to SUM the iSeries column sizes, TCREAS and TCDESC in this example, and set the DefaultBufferMaxRows by dividing the SUM of the columns max_length into 10485760. But I did not see a performance improvement. Do you think that redefining the columns as varchar(100) for the insert is significant? Should I possibly SUM the actual number of columns (2) as 2x100 or SUM the 30x100?

View 4 Replies View Related

Integration Services :: Package Development For Pulling Data Into Excel Destination File From OLEDB Source

Sep 2, 2015

1 How to get the desired output colums into Excel file without having 'copy of column/unwanted columns' in destination file.

2. How to override the existing file in excel destination.

View 2 Replies View Related

Transpose Source Data From A System Via Metadata Lookup Table Into Destination Table

Apr 1, 2014

I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.

Source Data Table:

Table Name: Source

SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013

Metadata Table:

Table Name:Metadata

MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date

Destination table:

The source data to be loaded into the destination table(as shown below):

Table Name: Destination

SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013

View 7 Replies View Related

Copying Table Data From SQL Server 2005 To SQL Server 2000 - Very Slow When Using OLEDB Source And Destination Sources?

May 8, 2006

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

View 5 Replies View Related

Question About Move Large Amount Of Data From Database To Database

Apr 23, 2007

guys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!

View 8 Replies View Related

Move Data Between Database

May 15, 2006

Currently we update database A everyday with tables from database B.
Database is India and has poor connection speed. What would be the best method of moving this data.
Currently it's about 100 tables and on average its about 900 mb.
Any input is greatly appreciated.
thanks,

View 3 Replies View Related

Move Data From One Database To Another

Nov 12, 2007

I have two databases DB_1 and DB_2 on my server. Both of them have 25 identical tables.

Tables under DB_2 gets refreshed with new data daily and once it's ready with new data, those data should be copied to DB_1 tables (deleting any existing data and loading it with new data).

My question is there any other way other than using Import Data Wizard for moving the data between two databases which included deleting any existing data on DB_1 tables and loadit with new data.

Please let me know...Thanx in advance...

View 7 Replies View Related

How Do I Move Data From One Database To Another?

May 8, 2007

I have two databases located on the same server. I was able to script a identical table in the other database, but how do I transfer the table data there?

View 5 Replies View Related

Move Data From Tables On Linked Server To Normal Database?

Nov 10, 2014

I'm trying to find a way to insert data from a TableA on ServerA into TableB on ServerB using SSIS in Visual Studio.

The specification I was given is basically

Insert INTO TableB AS (Select * from TableA WHERE NOT EXISTS on TableB).

I can't use a linked server unfortunately.

I wonder if it possible to move data from tables on a linked server to a "normal database"? What am I doing wrong?

View 4 Replies View Related

Integration Services :: File System Task Move Directory Deletes Parent Destination Folder

Mar 12, 2013

I attempted to use Move Directory to move the contents of one directory to another.  I encountered the 'different volume' issue that others have experienced.  While this error is frustrating I can work past this particular issue. My more pressing question is why is the move directory command overwriting a destination directory?  

When I setup the Move directory file task I provided two vars to hold src and dest location:

dest var:  estserveroutput
src var: devserverdev estfiles
Set overwrite destination = TRUE

Why would Move Directory overwrite output folder at destination?  Shouldn't it only overwrite if the testfiles directory exists at destination?  This is very frustrating since I cannot find enough information in the official documentation to understand what is happening here.  

Is it just me or does the documentation for Move directory seem.....incomplete?

View 4 Replies View Related

Source Destination Error

Mar 4, 2008

Hi, I need help please!

I get the following error on my OLE DB Destination: column"Oprcode" cannot convert between unicode & non-unicode string data type.
Please Assist on what i should do!

Regards

View 2 Replies View Related

OLE DB Source &&amp; Excel Destination

May 3, 2006

Hi,

My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.

Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.

SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.

xlsQuery

CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO

sqlQuery

Exec Report.MyReport 1

Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.

Please Help

Thanks

Shafiq



View 10 Replies View Related

Dynamic OLE DB Source And Destination

Mar 27, 2007

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.

View 22 Replies View Related

SQL Server 2014 :: Move Data From Database Taking Into Account Identity Keys

Oct 9, 2013

I'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.

For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).

Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).

Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).

Like this:
T1: PK1, A1, B1, etc.
T2: PK2, FK1, A2, B2, etc.
T3: PK3, FK2, A3, B3, etc.

So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 3 Replies View Related

Wanting To Move An Entire Database Using Backup? Export? That Takes All Users, All Data, All Permissions

Apr 25, 2008

I've had issues where backup up and restoring data from sqlserver2005 does not reattach the data to the correct users.  Any tips on how to best accomplish full database moves where data is owned by different security users?
thanks,

View 2 Replies View Related







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