Custom Script Destination - Wrote 0 Rows Problems

Jun 12, 2007

I have created a custom script destination using an article found on TechNet. The script executes fine and completes, but with one small problem. The last package execution log entry states "wrote 0 rows". This is a problem because I use this number to verify that all of the database records have been properly transferred.

I have monitored that database when the script is executing and have verified that the data is being transferred property.

Is there some function that I should be calling with each row prcessed in the custom script destination to "tally" the row count? This way the script component will display the proper row count.

Any suggestions would be appreciated.

View 7 Replies

Custom Destination Component Logging - Wrote 0 Rows

Mar 17, 2006

I wrote a custom destination component. Everything works fine, except there is a logging message that is displayed that I cannot get rid of or correct. Here is the end of the output of a package containing my component:

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x0 at Data Flow Task, MyDestination: Inserted 40315 rows into C: empfile.txt
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "MyDestination" (9)" wrote 0 rows.
SSIS package "Package.dtsx" finished: Success.

I inserted a custom information message that contains the correct number of rows written by the component. I would like to either get rid of the last message "... wrote 0 rows", or figure out what to set to put the correct number of rows into that message.

This message seems to happen in the Cleanup phase. It appears whether I override the Cleanup method of the Pipeline component and do nothing, or not. Any ideas?

public override void Cleanup()

{

ComponentMetaData.FireInformation(0, ComponentMetaData.Name,

"Inserted " + m_rowCount.ToString() + " rows into " + m_fileName,

"", 0, ref m_cancel);

base.Cleanup(); // or not

}

View 6 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

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

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

View 1 Replies View Related

Custom Destination Adapter

Jan 31, 2006

Hi All,

I have built a custom flat file destination adapter but it appears that the code is not working. When I debug the process I notice that the ProcessInput section is called multiple times. The first time it looks like everything is working, then it call it again and there is no inpout from the DTSInput90.

Why would it do this?

Thanks

Mike

View 4 Replies View Related

How Do I Programatically Fail My Custom Destination Component?

Nov 8, 2006

I am writing a custom dataflow destination that writes data to a named pipe server running in an external process. At runtime, in PreExecute, I check to see if the pipe exists. If it does not exist I want to be able to fail the component and gracefully complete execution of my package.

Question is, how do I do this? If I make a call to ComponentMetaData.FireError() that will only create an OnError event and not actually stop the execution. So, how to I halt the execution of my component and return the error message?

Thanks,

~ Gary

View 2 Replies View Related

How Do I Order Columns In My Custom Destination Component?

Nov 17, 2006

Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?

K. Lyles

View 1 Replies View Related

Selecting Connection In Custom Destination Component UI

Feb 24, 2006

Hi there,

I am writing a Custom Destination component with a custom UI. The UI contains a combo box which contains the connection names of type ��FLATFILE�?. I also have provided a button which would create a new connection of type ��FLATFILE�? by making a call to CreateConnection method of IDtsConnectionService.
The combo box gets properly updated showing the connections of type ��FLATFILE�? but on clicking on the new Connection button the application hangs up.
Am I missing something or is there some other way to do it?

The function are the events handlers which are called by the UI.

void form_GetConnections(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.ServiceProvider != null, "Service Provider not valid.");

this.ClearErrors();

try
{
IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));

if (connectionService != null)
{
ArrayList temp_Connections =
connectionService.GetConnectionsOfType("FLATFILE");

args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count];
for (int i = 0; i < temp_Connections.Count; i++)
{
ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections;
args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection);
}

}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}

void form_GetNewConnection(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.ServiceProvider != null, "Service Provider not valid.");

this.ClearErrors();

try
{
IDtsConnectionService connectionService = (IDtsConnectionService)this.ServiceProvider.GetService(typeof(IDtsConnectionService));

if (connectionService != null)
{
connectionService.CreateConnection("FLATFILE");

ArrayList temp_Connections =
connectionService.GetConnectionsOfType("FLATFILE");

args.AvailableColumns = new AvailableColumnElement[temp_Connections.Count];
for (int i = 0; i < temp_Connections.Count; i++)
{
ConnectionManager runtimeConnection = (ConnectionManager)temp_Connections;
args.AvailableColumns.AvailableColumn = new DataFlowElement(runtimeConnection.Name, runtimeConnection);
}

}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}

Has anyone else run into this?

I am running
SQL 2005 9.0.1399 and VS 2005 8.0.50727.42 (RTM.50727.4200) on Windows Server 2003 Enterprise Edition SP1.
Any suggestions would be welcome.

The code sample is an extension to the RemoveDuplicates sample (Dec 2005) which comes along with the SQL Server.

TIA,
Robinson

View 5 Replies View Related

Developing A Custom Task Using OLE DB Destination Adapter

Jul 25, 2007

I am writing a custom task to import data from delimited files into SQL tables. I use the standard Flat File Source adapter, a custom transformation to add a URN column and a filename column to the data and the standard OLEDB Destination Adapter.

Most of my test data files work fine except for ones with a lot of columns (around 350 columns). I get an error when I call the ReinitializeMetaData() method for the destination adapter.

Q1) Is there a restriction on the number of columns (or data row size) that can be imported into an OLEDB Destination Adapter?

Q2) The reason I use this adapter rather than the SQL Server Destination Adapter is that I need to set the destination table name using a variable. I don't believe I can do this with the SQL Server Destination Adapter. Is this the case?

Q3) Anyone know of a better/alternative way of acheiving the above? One way I have thought of is to create a custom destination adapter using the SQL Server Destination adapter as the base but I'm not sure whether this is a) possible and b) worth the hassle.

Hope someone can help...

Cheers

Nick

View 2 Replies View Related

Performance Issue With Custom Data Destination Component

Feb 12, 2008

Hi,
All the examples I've seen for creating custom data destinations (scripts or components) show that in the ProcessInput method you need to iterate through each record and execute a commit for each row. Here's a sample from http://forums.microsoft.com/msdn/showpost.aspx?siteid=1&postid=70469

Public Overrides Sub MyAddressInput_ProcessInputRow(ByVal Row As MyAddressInputBuffer)
With sqlCmd
.Parameters("@addressid").Value = Row.AddressID
.Parameters("@city").Value = Row.City
.ExecuteNonQuery()
End With
End Sub

This works for me but is extremely slow when processing lots of rows. Has anyone come across a better/faster example for committing rows to a database when creating a custom data destination component? I was thinking about using the ADO.NET batch update. Any thoughts on this or other approaches?

Thanks,

Mike Fulkerson

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

Have A Better UPDATE Statement Than What I Wrote?

Jul 23, 2005

/*This is a long post. You can paste the whole messagein the SQL Query Analyzer.I have a scenario where there are recordswith values pointing to wrong records and I need to fix themusing an Update statement.I have a sample code to reproduce my problem.To simplify the scenario I am trying to use Order relatedtables to explain a little better the tables i have to work with.Please don't bother looking at the wrong relationship and howthe tables are designed. That's not my current problem. Myjob is to correct the wrong data either using code or manually.Here are the tables I have created:TBLORDERS where two fields I am interested in are:ORDERTYPENO linking to TBLORDERTYPELASTSTATUSNO linking to TBLSTATUSTBLORDERTYPE where one field I am interested in isORDERPROCESSINGNOTBLORDERPROCESSINGEach order has a link to OrderTypeNo and eachOrderTypeNo has a link to OrderProcessingNo.TBLORDERSTATUSES where one field I aminterested in isSTATUSNOTBLSTATUS where one field I am interested in isORDERPROCESSINGNOI have the sample code here:*/--DROP DATABASE TestDBCREATE DATABASE TestDBGOUSE TestDBCREATE TABLE TBLORDER(IDNO INT PRIMARY KEY NOT NULL,ORDERNUMBER VARCHAR(50),ORDERTYPENO INT,LASTSTATUSNO INT)INSERT INTO TBLORDER (IDNO, ORDERNUMBER, ORDERTYPENO, LASTSTATUSNO)SELECT 1, 'ORDERTEST1', 1, 3 UNION ALLSELECT 2, 'ORDERTEST2', 1, 3 UNION ALLSELECT 3, 'ORDERTEST3', 2, 16 UNION ALLSELECT 4, 'ORDERTEST4', 2, 16 UNION ALLSELECT 5, 'ORDERTEST5', 2, 16 UNION ALLSELECT 6, 'ORDERTEST6', 2, 16 UNION ALLSELECT 7, 'ORDERTEST7', 4, 5 UNION ALLSELECT 8, 'ORDERTEST8', 4, 5 UNION ALLSELECT 9, 'ORDERTEST9', 6, 22 UNION ALLSELECT 10, 'ORDERTEST10', 6, 22 UNION ALLSELECT 11, 'ORDERTEST11', 7, 20CREATE TABLE TBLORDERSTATUSES(IDNO INT PRIMARY KEY NOT NULL,ORDERNO INT,STATUSNO INT)INSERT INTO TBLORDERSTATUSES (IDNO, ORDERNO, STATUSNO)SELECT 1, 1, 1 UNION ALLSELECT 2, 1, 2 UNION ALLSELECT 3, 1, 3 UNION ALLSELECT 4, 1, 4 UNION ALLSELECT 5, 2, 1 UNION ALLSELECT 6, 2, 2 UNION ALLSELECT 7, 2, 3 UNION ALLSELECT 8, 2, 4 UNION ALLSELECT 9, 3, 15 UNION ALLSELECT 10, 3, 16 UNION ALLSELECT 11, 3, 17 UNION ALLSELECT 12, 4, 15 UNION ALLSELECT 13, 4, 16 UNION ALLSELECT 14, 4, 17 UNION ALLSELECT 15, 5, 15 UNION ALLSELECT 16, 5, 16 UNION ALLSELECT 17, 5, 17 UNION ALLSELECT 18, 6, 15 UNION ALLSELECT 19, 6, 16 UNION ALLSELECT 20, 6, 17 UNION ALLSELECT 21, 7, 5 UNION ALLSELECT 22, 7, 6 UNION ALLSELECT 23, 8, 5 UNION ALLSELECT 24, 8, 6 UNION ALLSELECT 25, 9, 22 UNION ALLSELECT 26, 9, 23 UNION ALLSELECT 27, 9, 24 UNION ALLSELECT 28, 9, 25 UNION ALLSELECT 29, 10, 22 UNION ALLSELECT 30, 10, 23 UNION ALLSELECT 31, 10, 24 UNION ALLSELECT 32, 10, 25 UNION ALLSELECT 33, 11, 18 UNION ALLSELECT 34, 11, 19 UNION ALLSELECT 35, 11, 20 UNION ALLSELECT 36, 11, 21CREATE TABLE TBLORDERTYPE(IDNO INT PRIMARY KEY NOT NULL,ORDERTYPE VARCHAR(50),ORDERPROCESSINGNO INT)INSERT INTO TBLORDERTYPE (IDNO, ORDERTYPE, ORDERPROCESSINGNO)SELECT 1, 'CATEGORY 100', 1 UNION ALLSELECT 2, 'CATEGORY 200', 5 UNION ALLSELECT 3, 'CATEGORY 300', 3 UNION ALLSELECT 4, 'CATEGORY 400', 2 UNION ALLSELECT 5, 'CATEGORY 500', 4 UNION ALLSELECT 6, 'CATEGORY 600', 9 UNION ALLSELECT 7, 'CATEGORY 700', 8 UNION ALLSELECT 8, 'CATEGORY 800', 7 UNION ALLSELECT 9, 'CATEGORY 900', 6CREATE TABLE TBLORDERPROCESSING(IDNO INT PRIMARY KEY NOT NULL,ORDERPROCESSING VARCHAR(50))INSERT INTO TBLORDERPROCESSING (IDNO, ORDERPROCESSING)SELECT 1, 'ORDER PROCESSING A1' UNION ALLSELECT 2, 'ORDER PROCESSING A9' UNION ALLSELECT 3, 'ORDER PROCESSING Z5' UNION ALLSELECT 4, 'ORDER PROCESSING 76' UNION ALLSELECT 5, 'ORDER PROCESSING 98' UNION ALLSELECT 6, 'ORDER PROCESSING AB' UNION ALLSELECT 7, 'ORDER PROCESSING 11' UNION ALLSELECT 8, 'ORDER PROCESSING T7' UNION ALLSELECT 9, 'ORDER PROCESSING ZX'CREATE TABLE TBLSTATUS(IDNO INT PRIMARY KEY NOT NULL,STATUS VARCHAR(50),ORDERPROCESSINGNO INT)INSERT INTO TBLSTATUS (IDNO, STATUS, ORDERPROCESSINGNO)SELECT 1, 'ABC', 1 UNION ALLSELECT 2, 'DEF', 1 UNION ALLSELECT 3, 'GHI', 1 UNION ALLSELECT 4, 'JKL', 1 UNION ALLSELECT 5, 'MNO', 2 UNION ALLSELECT 6, 'PQR', 2 UNION ALLSELECT 7, 'STU', 3 UNION ALLSELECT 8, 'VWX', 3 UNION ALLSELECT 9, 'YZ', 3 UNION ALLSELECT 10, '123', 3 UNION ALLSELECT 11, '456', 3 UNION ALLSELECT 12, '789', 3 UNION ALLSELECT 13, '0AA', 3 UNION ALLSELECT 14, '0BB', 3 UNION ALLSELECT 15, '0CC', 5 UNION ALLSELECT 16, '0DD', 5 UNION ALLSELECT 17, '0EE', 5 UNION ALLSELECT 18, '0FF', 8 UNION ALLSELECT 19, '0GG', 8 UNION ALLSELECT 20, '0HH', 8 UNION ALLSELECT 21, '0II', 8 UNION ALLSELECT 22, '0JJ', 9 UNION ALLSELECT 23, '0KK', 9 UNION ALLSELECT 24, '0LL', 9 UNION ALLSELECT 25, '0MM', 9/*If you run the above, the data is CORRECT and the wayit normally should be.Basically, each Order is linked to an OrderTypeNo. EachOrderTypeNo is linked to an OrderProcessingNo.Each Order has MANY OrderStatuses. EachOrderProcessingNo has MANY Statuses.So both TBLORDERTYPE and TBLSTATUS is pointingto TBLORDERPROCESSING. I will mess up an Orderrecord for example to point to a wrong OrderType andleave its LASTSTATUSNO and all its CHILDTBLORDERSTATUSES STATUS records point tothe CORRECT ORDERPROCESSINGNO.*/UPDATE TBLORDERSET ORDERTYPENO = 3WHERE IDNO = 5 OR IDNO = 10/*So now both Order IDNO 5 & 10 are basically messedup as they are pointing to ORDERTYPENO 3 (i.e.ORDERPROCESSINGNO 3) whereas theirLASTSTATUSNO and all its TBLORDERSTATUSSTATUS records are pointing to .......ORDERPROCESSINGNO 5 & 9Now I will mess up both TBLORDER andTBLORDERDETAILS in order for my codeNOT to fix it since this I will have to deal anddecide what to correct manually.*/UPDATE TBLORDERSET ORDERTYPENO = 2, LASTSTATUSNO = 15WHERE IDNO = 8SELECT * FROM TBLORDERGOCREATE VIEW VIEW1-- This VIEW1 returns all TBLORDER records that have the problemASSELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNOFROM TBLORDERINNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNOINNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNOAND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNOGOCREATE VIEW VIEW2-- This VIEW2 does a GROUP BY of all TBLORDER.IDNO &TBLSTATUS.ORDERPROCESSINGNOASSELECT TOP 100 PERCENT TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNOFROM TBLORDERSTATUSES INNER JOINTBLORDER ON TBLORDERSTATUSES.ORDERNO = TBLORDER.IDNOINNER JOINTBLSTATUS ON TBLORDERSTATUSES.STATUSNO =TBLSTATUS.IDNO INNER JOINVIEW1 ON TBLORDER.IDNO = VIEW1.IDNOGROUP BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNOORDER BY TBLORDER.IDNO, TBLSTATUS.ORDERPROCESSINGNOGOCREATE VIEW VIEW3-- This VIEW3 checks to see if TBLORDERSTATUS records have more than oneORDERPROCESSINGNOASSELECT IDNOFROM VIEW2GROUP BY IDNOHAVING (COUNT(*) > 1)GOCREATE TABLE TMPORDERS(IDNO INT,OLDORDERTYPENO INT,NEWORDERTYPENO INT,LASTSTATUSNO INT)INSERT INTO TMPORDERS (IDNO, OLDORDERTYPENO, LASTSTATUSNO)SELECT TBLORDER.IDNO, TBLORDER.ORDERTYPENO, TBLORDER.LASTSTATUSNOFROM TBLORDERINNER JOIN TBLORDERTYPE ON TBLORDER.ORDERTYPENO = TBLORDERTYPE.IDNOINNER JOIN TBLSTATUS ON TBLORDER.LASTSTATUSNO = TBLSTATUS.IDNOAND TBLORDERTYPE.ORDERPROCESSINGNO <> TBLSTATUS.ORDERPROCESSINGNOLEFT JOIN VIEW3 ON TBLORDER.IDNO = VIEW3.IDNO AND VIEW3.IDNO IS NULLSELECT * FROM TMPORDERSUPDATE TMPORDERSSET NEWORDERTYPENO = TBLORDERTYPE.IDNOFROM TBLORDERTYPEINNER JOIN TBLORDERPROCESSING ON TBLORDERTYPE.ORDERPROCESSINGNO =TBLORDERPROCESSING.IDNOINNER JOIN TBLSTATUS ON TBLORDERPROCESSING.IDNO =TBLSTATUS.ORDERPROCESSINGNOWHERE TBLSTATUS.IDNO = TMPORDERS.LASTSTATUSNOUPDATE TBLORDERSET ORDERTYPENO = NEWORDERTYPENOFROM TMPORDERSWHERE TMPORDERS.IDNO = TBLORDER.IDNOSELECT * FROM TBLORDER/*Is there a better to write my Update statement? As you can see thatI am using 3 views, 1 temp table and 2 update statements tofix my problem.I am not even sure if i'll need to add more update statementsto handle other corrections. If that is the case I am trying tosee if my code can be simplified in order for it to be easilymodifiable to handle other scenarios.Thank you for your time.*/

View 2 Replies View Related

OLE DB Destination - Redirecting Rows

Feb 14, 2008

I'm getting some strange error handling behaviour when I tried to redirect rows when an error is encountered at the OLE DB Destination. I enabled the fastload option and set the maximum insert commit size to 10,000. My package is trying to insert 1,000,000 rows. When the row containing bad data is encountered, the entire batch is redirected to a separate table with no table constraints. So I have approx 990,000 rows of good data inserted, and approx 10,000 rows in an error table. However, only 2 of the 10,000 rows are actually bad data.

I changed the filter in the data source to reload 5 rows from the 10,000 rows of bad data. The 5 rows include the 2 rows of bad data. The fastload option and max insert commit size at the OLE DB Destination are not changed. When the package completed, 3 rows were inserted to the destination and the 2 bad rows are redirected again. Even though this is the outcome I wanted (only the real bad data is redirected), shouldn't all 5 rows be redirected since they should all be treated as a single batch?

My second question is, is there a way to get the best of both worlds - be able to load lots of data as fast as possible and only redirect the rows that are actually bad?

Thanks.

View 1 Replies View Related

The Order Of Insertion Of Rows Into Destination Is Not Same As The Order Of Incoming Rows

Dec 21, 2006

Hi ,

i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,

However it is same for smaller number of rows.

Please help ...i want the order to be same.

View 4 Replies View Related

Can't Find The Stored Procedure I Wrote In SQL Server

Jan 9, 2008

This is my first forray into writing stored procedures and like the manual said created one in SQL Server 2005 parsed or tested it, executed it, tested it again succesfully,need to modify it but can't find it under stored procedures file where I initially right clicked and selected New Stored Procedure.
Any idea where it went?

View 3 Replies View Related

Delete Excel Destination Rows

May 15, 2008

Hey all

I am exporting table rows (based on a query) into an excel file but I don't want to append to the file. I would like to delete the rows that were previously added and then add the new data. The file has column headings and I would like these to exist all the time.

I know how to export the data but don't know how to delete 'old' data rows from excel.

Any guidance will be highly appreciated.

Many thanks,

Rupa

View 5 Replies View Related

SQL Server Destination Not Loading All Rows

Jan 23, 2008

I have a SSIS package that transfers data from three SQL Server 7 servers to a SQL Server 2005 database. This package has about 30 different tables it copies. The table structures in the source database and destination tables are identical. About 25 different tables load without any issues. I have about 5 tables that load some nights without a problem. On other nights, the data transfers seem to randomly (though usually the most recent records) ignore some of the data. I have logging turned on and receive no errors. It just appears to stop loading data.

I should also mention that I truncate each destination table before begining and each table is loaded from data from each of the 13 source database (I am combining data from 13 regional database for reporting purposes). This is done using a Foreach Loop Container that updates the Server/Region connection string for each region. I am using the OLE DB Source connection to the SQL Server Destination. I have tried as well with the OLE DB Destination with the same result (and no error). I do not do any manipulation to the data on the transfer, but added a "RowCount" transformation between the source and destination and it gives the correct number of rows, but not all the rows get loaded.

View 6 Replies View Related

SSIS Oledb Destination Not Writing Any Rows

Nov 20, 2007

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

Filtering Rows That Already Exist In The Destination Table

Feb 17, 2006

Hi All,

What is the most straighforward way of not importing rows that already exist in the destination table (as determined by a primary key value)? Thanks.

Regards,

Daniel

View 3 Replies View Related

Integration Services :: Add Only New Rows To A Destination Table

Jul 22, 2015

How do I add only new rows to a destination table (when copying a table from another database every night) ?Every night I am copying a number of tables from one database to another.I only want to insert news rows (that are not in the destination table, but are in the source table) to the destination table.I might normally drop the destination table and just copy over the whole table, but in this case rows can be deleted from the source table, but I want to keep these old rows in the destination table (to maintain history). So I only want to add in rows to the destination table that have been added to the source table since last time.I guess I could copy the whole of the source table to a temporary table in the warehouse, then use a T-SQL merge command to compare and just add new rows to the destination table- but suspect that this is not the best way.

View 8 Replies View Related

Custom Rows In Table

Apr 4, 2007

Hi everyone,

I want to display my data in the following layout:

-------------------------------------------------------------------------------------------------------------

| Category | Count | Count % | SumofAmount |

-------------------------------------------------------------------------------------------------------------

| Category A | 34 | 27.42% | 121.23 |

| Category B | 87 | 70.16% | 1,232.33 |

| Category C | 3 | 2.42% | 4,344.21 |

-------------------------------------------------------------------------------------------------------------

| Total | 124 | 100% | 5697.77 |

-------------------------------------------------------------------------------------------------------------

Here the values of Count and SumofAmount are aggregate values grouped on the Category. The Count % is the percentage of Count of each category (Count[ i] / Total Count). My dataset which is flat and looks somthing like this:

--------------------------------------------------------------------------------------------

| ID | Amount | Category | Other fields |

--------------------------------------------------------------------------------------------

| 1 | 134.12 | Category A | blah bla |

| 2 | 1134.21 | Category B | blah bla |

| 3 | 124.45 | Category A | blah bla |

| 4 | 34.76 | Category C | blah bla |

| 5 | 87.19 | Category A | blah bla |

| 6 | 2143.92 | Category B | blah bla |

| 7 | 84.42 | Category A | blah bla |

| 8 | 86.33 | Category C | blah bla |

---------------------------------------------------------------------------------------------

I have tried to use Matrix control as well as the Table, but i am unable to achieve this layout. I know how to group in the Matrix control but on one column only here are 3 columns.

Can someone guide me on how to create this layout with given data?

Thanks and Regards,

Naveed

View 3 Replies View Related

I Wrote My Own Configuration Tool To Maintain The Connection Strings In A SQL Table

Mar 29, 2006

I wrote my own VB app to maintain all of my connection strings and link them to packages. I then grab them at run time and set them as variables in memory.

View 1 Replies View Related

Any Way To Check The Duplicated Rows In Destination Before Loading Data?

Jun 5, 2006

Hi. As the title, I am try to figure out how to write script to prevent duplicated rows before loading data from couple csv files to the OLE database table.
Another quick question, when I use Data Conversion to convert data from string to datetime or decimal type, it always return error like potential data loss.

View 4 Replies View Related

How To Feed Specific Rows From Excel Spreadsheet To OLE DB Destination

Jan 2, 2008

Hi,

I am in a bit of a quandry. I have an Excel spreadsheet source, but I only want certain rows from the spreadsheet, which will be delineated by a header and footer row (Start and End). Here is my script thus far (see below).

My question is, what is the correct way to do this? I guess I am stuck on syntax. i.e.) Do I need to create an OUTPUT BUFFER?

Thanks

==============================

Sample data:
blah
Start
Frank 1234 1234 1234
Sue 2345 2345 2345
End
blah
blah

==============================

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Static Section As String

'Set sections

If Row.Name = "Start" Then

Section = "Start"

End If

If Row.Name = "End" Then

Section = "End"

End If

If Section = "Start" Then

'GRAB THESE ROWS AND SEND THROUGH THE PIPELINE
'IGNORE ALL OTHER ROWS!
'What syntax goes here?
'Output buffer?

End If

End Sub

View 9 Replies View Related

Losing Rows From File To Destination Table - Need Troubleshooting Help

May 30, 2006

I am losing data from time to time and can not figure out where the rows are vanishing to. In essence, I have 5 files that I process. The processing occurs on a daily basis by one SQLAgent job that calls 5 individual packages, each doing a different piece of work.

I stage each file to a temp table, then perform some minor transformations, then union the rowsets from each file before landing to another temporary table. A subsequent package performs additional transformations that apply to the entire dataset, before inserting them to their final destination. Along the way, I reject some records based on the business rules applied. Each package in the entire Job is logging(OnError, TaskFailed, Pre and Post execute). Their are no errors being generated. No rows are being rejected to my reject tables either.

Without getting into the specific transforms, etc. being used in this complex process, has anyone seen similar unexplained behaviour? I've not been able to identify any pattern, except that it is usually only 1 or 2 of the record types (specific to a source file), that will ever not be loaded. No patterns around volumes for specific source files. There are some lookups and dedupes, however I've seen the records 'drop off' before reaching these transforms. I have noticed I had my final desination load not using fast load. However sometimes the records disappear before even getting to my final staging table which is inserting using fast load. I am going to turn on logging the pipelinerowssent event. Any other suggestions for troubleshooting/tracking down these disappearing records?

Thanks

View 3 Replies View Related

Problems With Deleting Rows In Destination Table When Exporting To Excel

Mar 29, 2006

Hi,

I export one table to Excel using Export Wizard. Second time I have used this creator I have checked option "Delete rows in destination table" because table already exists in Excel file.

During execution I get an error:

"Error 0xc002f210: Preparation SQL Task: Executing the query "DELETE FROM 'MyTable'" failed with following error: " and here is my translation from my language: "Deleting data from linked table is not supported by this version of ISAM"

I have also saved this package to a file and here is the full connection string to my Excel file:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsprzemekPulpitmyfile.xls;Extended Properties="EXCEL 8.0;HDR=YES";

Few days ago I have been using SQL2000 on the same machine and the same export was working properly.

Any suggestions?

Thanks.

Przemo

View 1 Replies View Related

Only Create Destination Flat File When There Are Rows Returned From Ole DB Source

Oct 25, 2007

Hi,

I have a data task with the following requirements:
1) Run query against database to retrieve rows
2) Add header and footer row to the result set. The footer row must contain a count of the records.
3) Write the rows to a fixed width file if there were any data rows

I have got to the point that I can create the file (using a set of tasks that includes derived columns, sorts, aggregation and merges). However the file is created regardless of whether there were data rows returned.
I can't check the row count before proceeding as this isn't set until the data task ends. And if I try to split them into separate data tasks (so that I can access this variable and perform conditional execution) it becomes harder to access the original rows.

Do you have any recommendations on the best way to achieve this?
It all seems to be very complex and I'm starting to feel that it would be easier to do this outside of SSIS...
Please help me to keep the faith!

For those interested this is a slightly simplified version of what I have so far (all within a single data task):

1.Run dummy sql to create header row 2.Run main SQL to retrieve rows
| |
| 3.Multicast
| | |
| | 4.Create footer row by doing sum() in aggregate task
| | |
| 5.Merge body and footer
| |
6. Merge header with body and footer
|

7. Create fixed width file

View 5 Replies View Related

Returning Table Rows From Custom Code?

Sep 28, 2007

I wish to call a custom code function from a table control that would return rows of data to be displayed in the table. Is this possible?

Specifically, I'd like to pass a large text string to the function, have the function break the string into smaller strings, and then have the smaller strings displayed in the table. The number of lines returned may vary, depending on the original string passed in.

View 5 Replies View Related

After SSIS Package Runs All Rows, All Fields Are NULL In Destination Table ???

Jul 21, 2007

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

Integration Services :: Loading Flat Files Without Duplicate Rows Into Destination Server

Sep 25, 2015

I have some duplicate records in my flat file. But i don't want to load those duplicate rows into my destination.

View 2 Replies View Related

Displaying Custom Properties For Custom Transformation In Custom UI

Mar 8, 2007

Hi,

I am creating a custom transformation component, and a custom user interface for that component.

In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.

I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.

How do I go about getting the properties for my transformation component listed in this property grid?

I am writing in C#.

View 5 Replies View Related

Expression Editor On Custom Properties On Custom Data Flow Component

Aug 14, 2007

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:

Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";

But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

View 6 Replies View Related

Expression Issue With Custom Data Flow Component And Custom Property

Apr 2, 2007

Hi,

I'm trying to enable Expression for a custom property in my custom data flow component.

Here is the code I wrote to declare the custom property:

public override void ProvideComponentProperties()

{

ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();

IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();

prop.Name = "MyProperty";

prop.Description = "My property description";

prop.Value = string.Empty;

prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

...

}

In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime

Here is my expression (a file name based on a date contained in a user variable):

"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"

@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time

So the expression is evaluated as: "DB189912189912.VER".

My package contains 2 data flow.

At runtime,

The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)

The second one contains my custom data flow component with my custom property that was set to an expression at design time

When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"

Any idea ?

View 5 Replies View Related