Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Merge 3 Rows Into 1 With SSIS


 

I have 3 input sources, date, name and text. What SSIS transformation could I use to merge these 3 rows into one single row. Example:
 
10/16/2007
John Doe
Text.......................................
 
Result wanted :

 
10/16/2007   John Doe  Text.......................................
 
Thank you so much fro your help.
 
Tara


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Ssis Package Design To Load Only Rows Which Are Changed From Exisiting Rows.
Hi  i tried designing a SSIS package which  loads  only those rows  which were different from existing  rows in the table ,  i need to  timestamp the existing row   with  an inactive date   when a update of that row is inserted (ex: same  studentID )
and  the newly inserted row with a insert  time stamp
so as to indicate the new row as currently active, in short i need to maintain history and  current rows in same table , i tried using slowly changing dimension  but could not figure out,  anyone experience  or knowledge  regarding the Data loads please respond.
 
example of Data would be like
 
exisiting data
 
StudentID    Name      AGE   Sex   ADDRESS  INSERTTIME      UPDATETIME
12               DDS       14      M       XYZ ST        2/4/06                    NULL
14                hgS        17      M      ABC ST         3/4/07                     NULL
 
 
New row  to insert would be
 
12        DDS            15    M      DFG ST         4/5/07
 
the data should reflect
 
StudentID    Name      AGE   Sex   ADDRESS  INSERTTIME      UPDATETIME
12               DDS       14      M       XYZ ST        2/4/06                    4/5/07
 
12               DDS       15      M      DFG ST         4/5/07                     NULL
 
14                hgS        17      M      ABC ST         3/4/07                     NULL
 
Please   provide your input  as much as you can  even though it might not be a 100% solution.
 
 
 
 
 
 
 

View Replies !   View Related
Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source
Environment:
 
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
 
Problem & Info:
 
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
 
Desired Help:

 
How to perform
 
1)       stripping out all undesired rows
2)       importing each column into sql table

View Replies !   View Related
Merge Rows Into One?
Does anyone have any idea how to construct a select statement querying a temp table that contains the following data:

Code|Jan-Apr| May-Aug| Sep-Dec
----------------------------------------
ABCD|250.00|NULL|NULL
ABCD|NULL |889.56|NULL
ABCD|NULL |NULL|1233.67

... that will merge the results as follows?

Code|Jan-Apr| May-Aug| Sep-Dec
----------------------------------------
ABCD|250.00| 889.56| 1233.67


Your help would be greatly appreciated!

Thanks.

View Replies !   View Related
Merge Two Rows In The Same Table
I want to create a stored procedure that will merge columns from tworows that contain duplicated contacts.I have can easily identify the duplicates and extract the UniqueIDs asparameters, but I can't figure out how to construct the actual updateSQL.@KeeperID int,@DupeID intUpdate ContactsSETa.Info1 = LEFT(TRIM(IsNull(a.info1,'') + ' ' IsNull(b.Info1,''))255),a.Info2 = LEFT(TRIM(IsNull(a.info2,'') + ' ' IsNull(b.Info2,'')),255),etc, etc...FROM(here's what I can't figure out)Contacts a ID = @KeeperIDContacts b ID = @DupeID

View Replies !   View Related
Merge A Column From Several Rows Into One Row
 

Hi
I have an old db I'm converting with SSIS.
In the old db I have an Log-table wich is in this format:
ID,CaseID,Note
 
each CaseId can have several rows.
 
Now I want to merge the Note Row into one row based on the CaseID.
So that I get One Row per CaseID with one bignote.
 
I have been looking into Pivot, but just dont seem to get it working.
Any Ideas?
 
 
 

View Replies !   View Related
How To Merge Data From Two Different Rows
Hi Guys,

Can anyone please explain and show an example of how to merge data from one row to another using in SQL 2000

thanks in advance

View Replies !   View Related
Merge/Combine Rows
I am writing a database system which recieves information parsed from various data formats. These data formats may or may not be complete, and as such some rows in the database can have gaps.
The input formats may contain reference to the same row (in this case the same Company record) but hold different facts about that company.
Eg one message may have name, phone and fax, whereas another message may contain name, address and website.
I need to be able to insert new companies into the database OR update current records with extended data as relevant, ive been looking for UPSERT or MERGE queries in SQL Server but i cant find any useful resources explaining its use.
Alternativly i'd like to be able to condense potential duplicates into single rows, eg:
NamePhoneEmailnullNamePhonenullWebsite
which would combine into one row where the null values get set by values derived from other similar rows:
Name PhoneEmailWebsite

Any help at all would be really appreciated
Thanks,
Toby

View Replies !   View Related
DTS (1000 + Rows) To Merge Replication - HOT***
Merge Replication -
Large inserts take hours – small inserts take seconds
SQL Server 7.0 SP2 - Merge Replication – Continuous – Poll interval (1 – 7 seconds)
Platform: Quad 600mhz – RAID 5 – Gigabit network

When the Publisher receives 1000 + data changes (via DTS or application) the changes are not available at the Subscriber for hours.
Merge Replication is moving small table changes (inserts – updates – deletes) 100 – 500 records very quickly (1 to 5 seconds).

How can insure immediate replication of 1000+ record changes?

View Replies !   View Related
All Rows Are Not Replicated Correctly (merge)
Hi all! Hope you can help us with this one. We installed SP 2 of Sql server 2005 few months ago, and that caused a strange problem with merge replication.

We have about 150 subscribers in one merge publisher, and 200.000 new rows generated each day (retail system). Most tables are filtered.

Replication and filtering works fine, except 0,1% --> 0,2% of rows are not replicated correctly. These "problem-rows" are always replicated correctly to the publisher database, but publisher can't always replicate these rows further to other subscribers. Before SP2 everything worked fine.

Same problem occurs with filtered and non-filtered tables.

SQL Server shows no errors or warnings, everything SEEMS to work fine.

We have to make "dummy"-updates (like update SALES set id=id) in publisher database, to wake up the replicator. And always after these dummy updates all problem-rows will be replicated correctly from the publisher to subscribers.

Why do we have to "wake up" the replicator time to time with dummy updates? Is there a bug in SP2?

View Replies !   View Related
How To Merge Multiple Rows Into Single Row
Hi,

I have two tables of news feed NewsHeader & NewsDetails
NewsHeader:
Time               Header
10:15:34 AM   News1
10:15:34 AM   News1
10:15:34 AM   News1
11:19:39 AM   News2
11:19:39 AM   News2
12:35:04 PM   News3
12:35:04 PM   News3

NewsDetails
Time               Text        RowC
10:15:34 AM   ABC          1
10:15:34 AM   DEFG        2
10:15:34 AM   HIJKL         3
11:19:39 AM   AABB        1
11:19:39 AM   CCDD        2
12:35:04 PM   ZZYY         1
12:35:04 PM   XXWW       2

Required Output
Time               Header     Text
10:15:34 AM   News1      ABCDEFGHIJKL
11:19:39 AM   News2      AABBCCDD
12:35:04 PM   News3      ZZYYXXWW

Thank you.

View Replies !   View Related
Merge Join - No Output Rows
Hi,
 
I have a problem with a Merge Join providing no output (when it should have 1890 rows).  My Data Flow Task has 4 OLE Data Sources, 3 Multicasts, and 1 OLE Data Destination.  I am experiencing the problem near the end of my data flow where two Multicasts create two parallel flows of data (see Level 1 below).  I have two Merge Joins which join one leg from each multicast with a leg from the other multicast (see Level 2 below).  Then the two remaining legs use a Merge to get my destination output (see Level 3 below).
 
I am experiencing my problem with the Merge Join (input A2, B2) --> (output C2) transformation.  The Merge Join providing output C1 appropriately outputs 1890 rows, but C2 outputs 0 rows.  Both Merge Joins are identical.  The data is identically sorted prior to entering the problematic Merge Join and a DataViewer (Grid) verified that the data is appropriately entering in.  Merge Join (input A2, B2) --> (output C2) has 667 rows as input A2 and 1890 rows as input B2 (using an inner join, just like the other merge join),  but C2 baffles me with 0 rows of output (when it too should have 1890).  I receive no Ouput errors and the execution completes showing all green.
 
Level 1
Multicast (output A1, A2) [667 rows]
Multicast (ouput B1, B2) [1890 rows]

 
Level 2
Merge Join (input A1, B1) --> (output C1) [1890 rows]
Merge Join (input A2, B2) --> (output C2) [0 rows]
 
Level 3
Merge (input C1, C2) --> (output D1) [1890 rows]*

 
I read about mysterious behavior with Merge Joins and have attempted modifying my EngineThreads property to values between 2 and 10, with no luck.  Any help/ideas would be appreciated.
 
Thanks,
 
Devin
 
* Should be 3780 rows

View Replies !   View Related
Merge Subscribers Pull Twice The Amount Of Rows
We are running merge replication, SQL Server 2005 Enterprise with SQL Mobile 2005 (Windows Mobile 5) subscribers. Partitions are filtered on HOST_ID.

 

Ocassionally we experience a situation where a subscriber experiences an unusually long synchronization duration, and upon examining Replication Monitor, it appears that  twice the number of rows, or X the number of rows (up to 7 times the number of rows) that should have been inserted are recorded as synchronized for the session: once the normal amount as inserts and once the normal amount as updates. This occurs for all tables in the subscription. This occurs on a first time synchronization to an empty subscriber database where there should be only inserts taking place.

 

I have examined the snapshot partition folders for these users on the file system and they appear to be identical in size and content as other subscribers. Checking the last partition snapshot job run and other characteristics for the the subscriber in question, everything appears to be the same as other subscribers functioning normally.

 

The HOST_ID for us is an employee ID used to filter employee specific data. I have seen this happen if the subscriber changes the value for the HOST_ID used in filtering, after the mobile database has already been populated (2 employees attempt to use the same mobile device and database).  But, we have seen this happen recently where the HOST_ID was apparantly never changed midstream.

 

This just started happening recently. The only modification around the same time frame was the implementation of a custom business logic handler/custom conflict resolver that performs like "Latest Wins" but has logic added to update the a last-update datetime column for selected transaction tables at time of synchronization, so that an SSIS job can detect the changed rows for copying incremental database changes to another application database. This all seems to be working perfectly.

 

Any ideas?

 

Thanks,

Matt

View Replies !   View Related
Merge Replication Mysteriously Deleting Rows
I am running a simple merge replication in SQL Server 2000.  I have one database that is the publisher, and a second database that is the subscriber.  When I add a new row to the subscriber it will replicate to the publisher as expected.  However, the new row at the subscriber will then be deleted without explanation.  The row will remain at the publisher though.

Does anyone know why it is doing this?

 

View Replies !   View Related
Merge Multiple Rows Into A One Or More Rows With Multiple Columns
Please can anyone help me for the following?
 
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
 
for eg:
data

ID           Pat_ID  

1             A


          2             A
          3             A
          4             A
          5             A
          6             B

          7             B
          8             B
          9             C   

          10           D  

          11           D




I want output for the above as:

Pat_ID         ID1          ID2           ID3
A                 1            2              3
A                 4            5             null
B                 6            7              8
C                 9            null          null
D                 10          11            null

 Please help me. Thanks!

View Replies !   View Related
Only 9999 Rows After MERGE JOIN In SQL Server BIDS
I've gote 2 Tables with about 50.000 rows and I left outer join them with MERGE JOIN.

The result are 9999 rows. Has anybody got the same problem. Maybe it's a bug!?

View Replies !   View Related
Merge Allmost Identical Rows For Output Table
I need to transform Foxpro table to SQL Server  table with merging all rows into one where all column values  are the same except one . For this the only column with the different values , I want them also to be merged as coma or space delimited string. The question whether SSIS is a good candidate for this kind of  data munging and also would be interested to know  knowing as  many as possible ways of doing that.  Surely I may  produce  Foxpro  script  in  5  minutes  which wil do that and  be a pre-processor action before SSIS starts.

View Replies !   View Related
Merge Replication: Missing Rows At Subscriber With No Conflicts
Hi all,

 

We are using a mix of SQL 2005 and 2000 servers and our "main" database server is running SQL 2005 x64 (SP2 ver. 3042).

 

Our system has run perfectly for months, then subsequent to an SP2 update we are seeing several instances where the data record counts are different for several tables among all the servers.

 

We are using Merge Replication, with no filters and published every 2 minutes.

 

Any ideas?

 

TIA,

Michael

 

View Replies !   View Related
Merge Join: Nr Of Output Rows Unchanged When Amount Of Input Changes
Dear all,

 

I created a package that seems to work fine with a small amount of data. When I run the package however with more data (as in production) the merge join output is limites to 9963 rows, no matter if I change the number of input rows.

 

Situation as follows.

 

The package has 2 OLE DB Sources, in which SQL-statements have been defined in order to retrieve the data.

The flow of source 1 is: retrieving source data -> trimming (non-key) columns -> sorting on the key-columns.

The flow of source 2 is: retrieving source data -> deriving 2 new columns -> aggregating the data to the level of source 1 -> sorting on the key columns.

Then both flows are merged and other steps are performed.

 

If I test with just a couple of rows it works fine. But when I change the where-clause in the data source retrieval, so that the number of rows is for instance 15000 or 150000 the number of rows after the merge join is 9963.

 

When I run the package in debug-mode the step is colored green, nevertheless an error is displayed:

Error: 0xC0047022 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Merge Join" (4703) failed with error code 0xC0047020. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

 

To be honest, a few more errormessages appear, but they don't seem related to this issue. The package stops running after some 6000 rows have been written to the destination.

 

Any help will be greatly appreciated.

 

Kind regards,

 

Albert.

View Replies !   View Related
Merge Replication Downloading Rows Immediately After Uploading SQL 2005
Hi

I have noticed that four out of the 145 tables being replicated in my topology perform a mysterious update.

If I insert data at the subscriber then synchronise the upload is completed followed by immediate downloading of the same identical set of rows as updates.

Has anyone seen anything like this.  I cannot see any difference in the filter joins or article settings compared to any other table in the system but these four consistently do this.

Publisher/Distributor: SQL 2005 SP1 on Win 2003 Server
Subscriber: SQL Express 2005 SP1 on Win XP SP2

Cheers
Rab

View Replies !   View Related
URGENT Merge Replication: Missing Rows At Publisher And Subscriber: No Conflicts
We have SQL Server 2000 with merge replication at a Publisher and subscriber.

We have some records getting deleted at Publisher and Subscriber and no conflicts are logged.

We have tried the compensate_for_errors setting and this has had no effect.

This is causing serious data corruption and has now become an URGENT issue. Out tech team are almost out of ideas.

Has anyone experienced this or have any ideas as to what to check next?

View Replies !   View Related
SSIS Merge Join
 

I am working on an ssis package and i find an problem while using the merge join for merging 2 OLEDB Data sources .
 
data source 1 is : - The table formed my an sal server comand , that out put is given to a multicast since i want to sare that output amoung 2o other tables.

So the the left input for the merge join is OLEDB source , which contains direct data from source table
 
I am usong Inner join on one column
 
The problem is i am not getting the expected rows as out put of merge .
 
I tried to join the two tables in sqlserver query window and i am getting expected result
 
What could be the problem
 
The first table is
 
Reservations.ReservationManual
 
second table is  Out put of the following query
 

Select Distinct B.ReservationID as R

from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C

Where

A.propertyID = B.PropertyId And

C.ReservationID = B.ReservationID And

getdate() >=C.Until +A.ReservationOffLineDays
 
 
 
 
i am not getting the expected result here in SSIS package merge join
 
But if i try to execute the following in query editer in management studio i am getting the expected result !!
 
 

declare @temp as table

(ResID Varchar(50)

)

Insert into @temp

(ResID)

Select Distinct B.ReservationID as R

from Property.Main A ,Reservations.Reservations B ,Reservations.ReservationRooms C

Where

A.propertyID = B.PropertyId And

C.ReservationID = B.ReservationID And

getdate() >=C.Until +A.ReservationOffLineDays

select * from Reservations.ReservationManual A , @temp b

Where A.reservationID = b.resID

View Replies !   View Related
SSIS And Merge Replication Problem
Hi,

 

I'm doing merge replication between SQL Server 2005 and SQL Compact on mobile devices. I also have an SSIS package set up which imports data from the client's accounting system into the SQL Server database. I'm having a problem where imported records are not being replicated to the subscribers on synchronization. The only way to get the newly added records to the subscriber is by generating a new snapshot and reinitializing the subscriptions. Does anyone have any ideas why this data is not replicating? Records added directly to the server database e.g. via Management Studio replicate ok, and records synchronized from subcribers are correctly replicating to all other subscribers.

 

Regards,

 

Greg McNamara

View Replies !   View Related
Merge Data From 2 Columns Into 1 Column In SSIS
I m extracting data from Excel-sheet and inserting into SQL Server 2005 Database using SSIS(SQL Server Integration services).
Having 2 columns in excel-sheet and want to insert those into 1 column of SQL Table.
Can anyone help me about, how can I insert data from 2 columns into 1 column in SSIS. Is Export/ Import column or Copy Column or Merge will work..

Plz reply soon..

Thanks...

View Replies !   View Related
Inserted Rows Count From SSIS Not Like Table Rows Count
Hi all

 

i using lookup error output  to insert rows into table

Rows count rows has been inserted on the table 59,123,019 mill

table rows count 6,878,110 mill ............................

 

any ideas

 

View Replies !   View Related
Complex SSIS Lookup/Merge Join Using NK And Dates
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

 -- Brian

View Replies !   View Related
Problem Using Merge Join Transformation In Sql Server 2005(SSIS)
Hi,
 I am pretty new to SSIS. I am transferring some rows from 2 source tables to 1 destination table.
 The 2 source tables have 1000 rows.They act as the 2 inputs to a merge join transformation where i perform the join between the 2 tables based on a couple of fields. But for some reason the output of  the merge join gives me about 1018 rows .Shouldnt the destination also have only 1000 rows?
 How do i solve tis problem?
 
Thanks in advance
Sat
 

View Replies !   View Related
SSIS: JOINING TABLES FROM TWO SERVERS - BETTER WAY THAN SORT -&&> MERGE JOIN?
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 Replies !   View Related
SSIS: Multi-Record File Using Merge - Getting Blank Lines
 I  have used the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have created the 9 datasources, ordering each on a field commmon to all.

I have created the required derived columns headers and have merged all the record types into a file.
 
The resulting file looks fine, except for the odd blank line between record types.  Any ideas regarding cause and what to do to fix?
 
Any help is most appreciated!
 
 

View Replies !   View Related
How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns
 

Please can anyone help me for the following?
 
I want to merge multiple rows (eg. 3rows) into a single row with multip columns.
 
for eg:
data

Date           Shift  Reading
01-MAR-08     1     879.880
01-MAR-08     2     854.858
01-MAR-08     3     833.836
02-MAR-08     1     809.810
02-MAR-08     2     785.784
02-MAR-08     3     761.760
 
i want output for the above as:

Date              Shift1         Shift2         Shift3
01-MAR-08     879.880       854.858       833.836
02-MAR-08     809.810       785.784       761.760
 Please help me.

View Replies !   View Related
SSIS: Merge Problem: The Input Is Not Sorted (for Use In Exporting A Multi-record Format File)
 

I am using the following useful article regarding exporting a multi-record file:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have created the 2 datasources, ordering each on a field commmon to both.

I have created the two derived columns headers and am now moving on to the merge.

It is failing with the following error:
"the input is not sorted"

And whilst I definitely have an order by on the query, when I look at the metadata between the datasource and the derived column, the Sort Key Position items displays "0" for all my fields, I was expecting the sort field to have a "1" in this column.  What am I missing?

Any help would be most appreciated!

 

View Replies !   View Related
SSIS Drops Rows
I've written an SSIS package which has two DataFlow tasks running simultaneously, importing the data from two separate ASCII files into two staging/prep tables: one Tasks imports records into an ORDER table while the other Task imports the ITEM-level records into an ITEM table.

Starting several days ago, the ITEM DF Task began importing a partial set of records even though the ASCII file clearly showed more records were available.

For example, in today's processing, here is the message from SSIS: << [ITEM Ascii File [1695]] Information: The total number of data rows processed for file "\webserv01globalscapeusr vr logstandarditem.txt" is 217361. >> yet i only found 10,401 rows -- and they all appeared to be the bottom 10,401 rows of the incoming ASCII file.

I cannot see any other untoward messages about SSIS encountering a problem.

There are no Error Output restrictions on the ASCII file.

The ASCII row preceding the 10,401 rows i DID get looks OK.

Can anyone suggest a possible place to look which might explain this "jumping" over the nearly 207,000 rows?

Thanks very much.

Seth J Hersh

View Replies !   View Related
How Can I Append Only New Rows In A Table Using SSIS?
Hi,
 
Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table  and import it into a Destination table using  Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new  data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.


 
Your assistance will be highly appreciated.

View Replies !   View Related
SSIS Perf Tuning - Tables Of 15M&#043; Rows
The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.

I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.

I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?

I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

The design of the data flow at the moment is:


..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4

any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?


*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

View Replies !   View Related
SSIS Doesn't Read All Input Rows
Hi *,

I'm trying to import a flat file with ~3500 rows into a SQL-DB. SSIS extracts only around half the rows. It leaves out every 2nd row. Anyone had this problem before?

Thanks!

View Replies !   View Related
Ignore First 6 Rows In Excel Import In SSIS Pkg.
I have an SSIS package that imports from an Excel file with data beginning in the 7th row.

Unlike the same operation with a csv file ('Header Rows to Skip' in Connection Manager Editor), I can't seem to find a way to ignore the first 6 rows of an Excel file connection.

I'm guessing the answer might be in one of the Data Flow Transformation objects, but I'm not very familiar with them.

Any pointers would be greatly appreciated.
Eric

View Replies !   View Related
SSIS Oledb Destination Not Writing Any Rows
 

I have a Dataflow task with oledb source that is using SqlCommand to retrieve data and oledb destination to write the source output to a table. I have access to both the source and destination databases.
 
The problem is the destination component is not writing any rows to the destination table eventhough the Source component is returning rows (I can see them in the Preview and the source database table as well).
I'm using "Table/View  Name from Variable" for destination.
 
The Package executes without any errors but there is no output.
 
Any ideas?
 
Thanks.
 
 

View Replies !   View Related
How Will I Know The Rows Being Saved By SSIS Package Into Tables
Hi Guys,

 

Yet another question again on the issues with SSIS. I have a package now which is working fine.

The package consists of a control flow and i have 2 DF tasks which are unionall first and then saved into a sql server destination.

It's fine up to this point but i've just been notified that i would need to generate 2 files based on different values after i combined the data from 2 sql server DF tasks.

My question is how can i know the rows which are being saved on this sql server destination.

I have a primary key which is an autoincrement column. 

Thank you

Gemma

View Replies !   View Related
SSIS - Delete Rows Before Flat File Import
I finally put together a SSIS package that takes a Text File and successfully imports its data into the right table. My question is, where in the package's properties can I find the option to Delete all rows from Destination Columns prior to Importing. I have looked everywhere in the Package Explorer for this setting. Thanx in advance.

View Replies !   View Related
How To Limit No Of Rows While Pulling Data From Oracle To Sql Through SSIS
 

Hi,
 
I want to pull sample records lets say 1000 rows only from oracle database to sql server. Is there any option in ssis to limit the number of rows?
 
 

View Replies !   View Related
Create A Data Flow In SSIS Wich Updates Som Rows.
Hi,

I have a table customer wich has the columns phone_number(char type) and ok_to_call(bit type). There are already data in the table and the column ok_to_call only contains the value false for every row.

Now i want to update the latter column. I have a text file with a list of phone numbers and i want that all the rows in the Customer table(phone_number column)that matches the number in the text file to update ok_to_call to true.

This is to be done in SSIS(Integration Services). I'm new at this and i've looked around that tool but is a lot of items, packages and stuff so i dont know where to begin.

Would appreciate help on how to solve this issue in SSIS. What controlflow/Data flows to use,wich items and packages to use, how to configure and how to link together?

Regards
/Tomas

View Replies !   View Related
SSIS - Slowly Changing Dimension - Detect Deleted Rows From The Source
 

Hi all,
 
        Can you help me to resolve my problem ? I have to do a simple daily backup system. Source : Flat File; Destination : SQL Server. I want to use the Slowly Changing Dimension component to backup only the new and updated row from my source (Flat File) and put them into SQL Server.
 
But how can I do to detect deleted rows from my source ?
 
Any suggestions ?
 
If it's not clear enough, please ask for more details !
 
GO

View Replies !   View Related
After SSIS Package Runs All Rows, All Fields Are NULL In Destination Table ???
I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.

I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the  fields are NULL in the destination database.  I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.

 

Does anyone have a clue as to why it would be doing this?

 

 

Any help would be much appreciated.

 

Thanks...

 

 

View Replies !   View Related
A Custom Component For Use As A VIEW In SSIS- Is It Possible To Create One MERGE Like Component With More Than 2 Inputs
Hi all
I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too.
Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting??
(I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)

View Replies !   View Related
Merge Replication Error - The Merge Process Could Not Retrieve Column Information
I had merge up and running fine. All of the sudden, the agent took a dive.
This is what I get from the agent log. Any help would be greatly appreciated.
Thanks in advance.



[10/12/2006 9:00:18 AM]VBSERVE19.LTS_Efile: {call sp_MSgetversion }
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'LTS_Efile'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'LTS_Efile'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
Uploading data changes to the Publisher
Connecting to Subscriber 'FO-DB-003.LTS_Efile'

Server: FO-DB-003
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:23 AM]FO-DB-003.LTS_Efile: {call sp_MSgetversion }
Connecting to Publisher 'VBSERVE19.LTS_Efile'

Server: VBSERVE19
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:24 AM]VBSERVE19.LTS_Efile: {call sp_MSgetversion }
Connecting to Subscriber 'FO-DB-003.LTS_Efile'

Server: FO-DB-003
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:24 AM]FO-DB-003.LTS_Efile: {call sp_MSgetversion }
Connecting to Publisher 'VBSERVE19.LTS_Efile'

Server: VBSERVE19
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:24 AM]VBSERVE19.LTS_Efile: {call sp_MSgetversion }
Connecting to Subscriber 'FO-DB-003.LTS_Efile'

Server: FO-DB-003
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:24 AM]FO-DB-003.LTS_Efile: {call sp_MSgetversion }
Connecting to Publisher 'VBSERVE19.LTS_Efile'

Server: VBSERVE19
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:25 AM]VBSERVE19.LTS_Efile: {call sp_MSgetversion }
Connecting to Subscriber 'FO-DB-003.LTS_Efile'

Server: FO-DB-003
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:25 AM]FO-DB-003.LTS_Efile: {call sp_MSgetversion }
Connecting to Publisher 'VBSERVE19.LTS_Efile'

Server: VBSERVE19
DBMS: Microsoft SQL Server
Version: 08.00.2039
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[10/12/2006 9:00:30 AM]VBSERVE19.LTS_Efile: {call sp_MSgetversion }
The process could not enumerate changes at the 'Subscriber'.
The process could not enumerate deletions at the 'Subscriber'.
The process could not enumerate changes at the 'Subscriber'.
The merge process could not retrieve column information for table 'dbo.cmsReturn'.
The process could not deliver delete(s) at the 'Publisher'.
The process could not deliver delete(s) at the 'Publisher'.
The process could not deliver delete(s) at the 'Publisher'.
Percent Complete: 0
The process could not enumerate changes at the 'Subscriber'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200999
Message: The process could not enumerate changes at the 'Subscriber'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_MSenumchanges(?,?,?,?,?,?,?,?)}
Percent Complete: 0
Category:SQLSERVER
Source: FO-DB-003
Number: 208
Message: Invalid object name 't'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147200988
Message: The process was successfully stopped.
Disconnecting from Subscriber 'FO-DB-003'
Disconnecting from Publisher 'VBSERVE19'
Disconnecting from Subscriber 'FO-DB-003'
Disconnecting from Subscriber 'FO-DB-003'
Disconnecting from Subscriber 'FO-DB-003'
Disconnecting from Subscriber 'FO-DB-003'
Disconnecting from Publisher 'VBSERVE19'
Disconnecting from Publisher 'VBSERVE19'
Disconnecting from Publisher 'VBSERVE19'
Disconnecting from Publisher 'VBSERVE19'
Disconnecting from Publisher 'VBSERVE19'
Disconnecting from Distributor 'VBSERVE19'

View Replies !   View Related
Merge Repliction - Run Stored Procedure When Merge Agent Starts
I have database on SQL Server 2000 set up with a merge publication.This publication is configured with a number of dynamic filters toreduce the amount of data sent to each client. Each client has ananonymous pull subscription. The merge process can be triggered by thewindows sync manager and my application.To improve performance I have created some helper tables to hold themapping between user login and primary keys of selected entities.For the replicated data to be correct the contents of the helper tablesneeds to be up to date.I need to fire off a stored procedure on the publisher beforereplication starts to verify that this data is up to date. I can notsee any documented way of doing this however I have been experimentingwith some unorthodox systems.Firstly has anyone any ideas?I have been considering adding a trigger to some of the tables used bythe Microsoft replication code - yes I know this is very nasty.My problems arise because executing this stored procedure will causesome data to be updated. In updating data we could create a newgeneration in the database. I must therefore run my stored procedurebefore any the Microsoft code makes any generation checks / updates.Anyone done anything similar, Anyone have any better ideas?Any comments would be gratefully received.

View Replies !   View Related
Change A Merge Subscriber To Become A Merge Publisher
Hi,

I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB.
It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting.
The initial configuration of the main publisher and subscription is controlled via scripting, which works fine.
The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription".
I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.

Please help,
Jez W

View Replies !   View Related
Processing Multiple Rows And Multiple Columns In SSIS
 I previously posted a problem with result set bindings but I have not been able to resolve my problem.  I guess all this comes with being new to programming in this environment!  Anyway, I am trying to figure out how to process from an ADO.NET connection multiple rows with multiple columns.  I have to read and manipulate each row.  I was originally looking at using a foreach loop but have not been able to get it to work.  One reply to my previous thought I should be using a data task to accomplish this.  Could someone tell me the best way to handle this situation?  As a note, I am new to programming in SSIS and basically trying to learn it as I go so please bear with me!  Thanks in advance!

View Replies !   View Related
Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate
Hi All,

I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.

But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.

There are indexes in the tables.

Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.


But nothing is improving

View Replies !   View Related
To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal
Hi,
 
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
 
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS. 
 
Any sort of help would be highly appreciated.
 
Thanks,
 

View Replies !   View Related
To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal
Hi,
 
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
 
Any sort of help would be highly appreciated.
 
Thanks,
 

View Replies !   View Related
Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1   answer1   answer2   answer3user2   answer1   answer2   answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View Replies !   View Related
Compare Values In Consecutive Rows And Print Rows Based On Some Conditions
 I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID  TrDt       TrTime    TimeDiff  Odometer  Miles  TrCity    TrState
1296   1/30/2008  08:22:42  0:00:00   18301     000    Omaha     NE
1296   1/30/2008  15:22:46  7:00:04   18560     259    KEARNEY   NE

Can someone please help me here?

Thanks,
Romakanta

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved