Merge Join conditional split - HeadBanger

I have a merge join that does a full outer join. I then have a conditional split that will breakout by unchanged, insert and update. The update is what I am having a problem with. The conditional split for the update is a follows:

!ISNULL(HISTORICAL) && !ISNULL(TRANSFORM)

Now I believe the problem is related to spaces in the key field let me explain.

The join field is defined as [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

There was an original mismatch between the two tables of VARCHAR(14) & Char(14) but now there both Char(14).

What's interesting is that the few records with Alpha characters correctly does the update but the records with starting numeric data all go down the insert path.

The data of the join columns look like

'308811151 ' - 5 Spaces This Data incorrectly goes to insert path

'TSTRWR02 ' 6 Space This Data correctly goes to update

The data is grouped and sorted by the Key field and the historical and transform column contain a 1 for all records.
while the Doc_nbr which is the key

I tested the join in SQL server and it works with and without the spaces.

From SSIS I also tried RTRIM in the SQL command of the source in addition to substring the first 9 with the right trim and I always received the same results.

I am guessing there is something going on with the not isnull in the conditional split but I can't figure it out if I am missing something or if this is a BUG.

Three paths of data after the merge join and the conditional split

Insert
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
206352185 ,,1,
209900165 ,,1,

Update
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
NEE ,1,1,NEE
New ,1,1,New

Unchanged
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
,1,,0000521096
,1,,0000685216

Any suggestions, am I missing the obvious?

LL

I beleive this to be a bug relating to a combination of the data relating to a char 14 which gets populated with space to the right on short columns in conjunction with the issorted property and the sortkey position of the oledb data source for sql server with the access mode SQL Command.

A work around on this was to remove the order by in the sql statements and to add a sort component before each input to the merge join.

So if anyone receives the same behavior, replace with sort components at least until you can find another solution like the look-up mentioned on this forum. You will take a little bit of a performance hit but at least you will get the anticipated results.

On a lighter note, Currently It's 78 degrees here in Miami and I didn't need a web service to figure that out.

LL

sql

Merge data from two tables into one table - no updates/only insert and no insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

sql

Merge data from two tables into one table - no updates/only insert and no insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

Merge data from two tables into one table - no updates/only insert

Hi all,,

I posted the questions in sql forum and got good sql statement to work with it.. However, I want to see if there is a way to do it in SSIS..

May be this is really basic questions but I am having hard time to do it in sql server 2005 SSIS..

I have a flat file that I want to merge with table in SQL server 2005.

1> I have successfully created a data flow task to import data from flat file to Table X (new table I created for this package).

Now here is my question.

I have a Table A already in the database with the same column structure as of TableX (Both the tables have 20 columns/same Name/Same design).

I want to merge Table A and Table X and stored the data in TableA. However, I just don't want to merge blindly, I need to insert a new row in Table A only if the same row does not exist in Table A (there is no primary key, i am looking certain fields to see if the rows are same)..

Here is an example:

Table A

--

1 test test1 test2 test3 test4 test5

2 test test6 test7 test8 test9 test10

Table X

1 test test1 test2 test99 test4 test5

2 test test98 test97 test 96 test95 test94

--

Now, I want to only insert row 2 of Table X since there is match on 4 of the fields in row1..

The new Table A should look like

NEW Table A'

--

test test1 test2 test3 test4 test5

test test6 test7 test8 test9 test10

test test98 test97 test 96 test95 test94

I think, I could do this using Execute SQL task and write all the code in sql, but that will be cumbersome and time consuming.. Is there a simpler way to achieve this?

Thanks in advance.

You can use the flat file source and couple that with a "lookup" transform pointing to table a and define your conditions here (i.e. flat file input.column1 == tablea.column1, flat file input.column2 == tablea.column2 ... NOTE: you do this by drag and drop of columns from your input to the lookup values, you don't write them out as above)

When you are configuring the lookup set the configure error to redirect rows. Use the red output ("error" ... really just row not found) and connect it to a ole db or sql destination. If you want all of the rows from the flat file to go to the table x, you can use a multicast upstream and send one output to the lookup and one output to the oledb / sql destination for your table x.

EDIT: this will not give you the flexibility of matching based on 4 of 5 or any other heuristic... to do this you would probably have to use a script task (control flow) or some other mechanism...

|||Amazon,

You can use a lookup component against table A and then based on whether the row already exists or not; the package will do an insert; otherwise nothing.

This post has some of that; just ignore the update part:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1|||

Thanks Rafael,

this is what I was looking for..

|||

Thanks Eric..

Rafeal post has some useful information about script task..

Thank you

Memory used

I've got this problem: I execute a bulk insert via a
stored procedure of about 500000 records. The bulk insert
works fine, but the memory used grows up very fast. Then,
it never goes down.
So if I try to do another bulk insert via the same stored
procedure, the sp doesn't work and the statement have been
terminated without any error.
The server is a pentium 4 (single processor) 1,5 Ghz and 1
Gb of memory, with windows 2000 server service pack 3 and
it is used for development purposes.
Thank you.marco
Are you sure there are no other activites on the server at the same time?
Have you looked at Profiler ?
"marco" <angilerm@.lloydadriatico.it> wrote in message
news:21e701c3a925$4cad3650$a601280a@.phx.gbl...
> I've got this problem: I execute a bulk insert via a
> stored procedure of about 500000 records. The bulk insert
> works fine, but the memory used grows up very fast. Then,
> it never goes down.
> So if I try to do another bulk insert via the same stored
> procedure, the sp doesn't work and the statement have been
> terminated without any error.
> The server is a pentium 4 (single processor) 1,5 Ghz and 1
> Gb of memory, with windows 2000 server service pack 3 and
> it is used for development purposes.
> Thank you.sql

Memory Low after running for a day

A program is used to insert data record by record to a SQL Server (2000)
using transaction. After a day, 300M or memory was used up. What would be
the cause of such "memory leak" and how to releasing the memory other than
reboot? Thank you.> A program is used to insert data record by record to a SQL Server (2000)
> using transaction. After a day, 300M or memory was used up. What would be
> the cause of such "memory leak" and how to releasing the memory other than
> reboot? Thank you.
This is not a memory leak, this is how it has to work. SQL Server caches
execution plans and the data, grabs memory when it needs it, but releases it
only on a demand. If you have to, you can limit the memory SQL Server uses
with the maximum memory configuration option
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org