loosing values when load from text file

When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.

- With low volumnes of data, less that 50,000 rows, no problems

- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.

But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.

Any help would be appreciated!

Greg

>>>- With low volumnes of data, less that 50,000 rows, no problems

Did you have success using both BIDS and SQL Agent for the 50K load?

>>>- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package.

While using SQL Agent did you chose the Command subsystem and use dtexec or the SSIS subsystem?

Looping rows to Increment a value

I currently have a table that contains a Serial Number and a quantity.

SN Qty
4352301 3
6892103 2

I need to be able to loop through this table and increment each unique Serial Number by the quantity. The desired result set is:

4352301
4352302
4302303
6892103
6892104

I can pull off a Do While loop in ASP however I am having trouble with the TSQL syntax. Any help would be GREAT !!

joeUPDATE table SET serial = serial + quantity ?|||Something like this?

Code:
-------------------------------------
create table #tmp(sn int, qty int)
insert into #tmp values(4352301,3)
insert into #tmp values(6892103,2)
select * From #tmp
declare @.sn int, @.qty int, @.cntr int
select @.sn = min(sn) from #tmp
while (@.sn is not null) begin
select @.qty = qty, @.cntr = 1 from #tmp where sn = @.sn
while (@.cntr < @.qty) begin
select @.sn = @.sn + 1, @.cntr = @.cntr + 1
insert into #tmp values(@.sn,@.qty)
end
select @.sn = min(sn) from #tmp where sn > @.sn
end
select * from #tmp order by sn
-------------------------------------

Results:
-------------------------------------
sn qty
---- ----
4352301 3
6892103 2

sn qty
---- ----
4352301 3
4352302 3
4352303 3
6892103 2
6892104 2
-------------------------------------

Loop with calculation

I have a table which contains numerical data in a field called active_en_del. I need to loop through this table and perform a calculation where row 2 minus row 1 = and store into another field; row 3 - row 2 = and store into another field etc. How would I perform this? Thanks

I am not sure which DB you are using. You also say that you want to store the difference in different "field"s. I am not sure if by fields, you mean columns or what. Using this below approach, you can get all the results in rows:

Assuming sql server 2005, you can get rownumbers for all the rows using row_number() over(order by id)

and then do a self join.

Code Snippet

select a.col1-b.col1 from

(select col1, row_number() over(order by id) as rowid
from tblData)
a,

(select col1, row_number() over(order by id) as rowid
from tblData)

b

where a.rowid=b.rowid+1

|||My appology for not specifying. Yes it is sql 2005 and it a calculation on the same column. Thanks for the above information.sql

Loop insert

Hi,

I will do my best to explain my question:

I have a field called CUSTOMER which contains a number such as C00001,
C0002 etc.

However i now have a new field called XCUSTOMER which is the new number that they relate to.

CUSTOMER C00001 now relates to XCUSTOMER 493845.
CUSTOMER C00002 now relates to XCUSTOMER 494343.

Basically there are hundreds of these and i dont have the time to manually enter the data. I just want to say "where customer = C00001 then insert 49494 into XCUSTOMER and then loop through and insert them all.

My table is called CUSTOMERINFO.

If anyone could help it would be much apprieciated as it would save me so much time.

Thanks:)update customerinfo
set xcustomer = case customer
when 'c00001' then 493845
when 'c00001' then 494343
...
end

lookup over 2 databases and three tables

ok i know i shopuld really put up all the code, but was hoping this would be
a simple one i have a table called table 1 on db1 that contains fields named
sortcode, accountno, licence
on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a
fiedl called id
tbl3 has a field called accountno and id. records in tb2 & 3 are linked by
the id field, what i need to do is find the licence from db1 wwhere the sord
conde = db2.tb2 sortcode and accountno = db2.tb3 accountnoHave you got Linked servers up between the 2 dbs?
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:123C059B-D6E8-416A-8B33-2D3B86EE2894@.microsoft.com...
> ok i know i shopuld really put up all the code, but was hoping this would
be
> a simple one i have a table called table 1 on db1 that contains fields
named
> sortcode, accountno, licence
> on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a
> fiedl called id
> tbl3 has a field called accountno and id. records in tb2 & 3 are linked
by
> the id field, what i need to do is find the licence from db1 wwhere the
sord
> conde = db2.tb2 sortcode and accountno = db2.tb3 accountno|||the two databases are on the same server
"Jack Vamvas" wrote:

> Have you got Linked servers up between the 2 dbs?
>
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
>
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:123C059B-D6E8-416A-8B33-2D3B86EE2894@.microsoft.com...
> be
> named
> by
> sord
>
>|||On Fri, 31 Mar 2006 05:32:02 -0800, Peter Newman wrote:

>ok i know i shopuld really put up all the code, but was hoping this would b
e
>a simple one i have a table called table 1 on db1 that contains fields name
d
>sortcode, accountno, licence
>on DB 2 i have two tables tb2 tb3 , tb2 has a field called sortcode and a
>fiedl called id
> tbl3 has a field called accountno and id. records in tb2 & 3 are linked by
>the id field, what i need to do is find the licence from db1 wwhere the sor
d
>conde = db2.tb2 sortcode and accountno = db2.tb3 accountno
Hi Peter,
Putting up all the code is really better. Especially since it allows us
to copy and paste your CREATE TABLE and INSERT statements and test our
proposed solutions, then check the results against your posted required
output. This also helps prevent misunderstandings about the
requirements. And finally, it clarifies the EXACT column names (in your
post, you're refering to one column as "sortcode" or "sord conde").
Oh, and BTW: "table 1" is a poor choice of tablename - the use of a
space in the name forces you to quote the name, which is frankly quite a
pain.
Anyway, here's an untested guess:
SELECT T1.licence
FROM db2.tb2
INNER JOIN db2.tb3
ON tb3.id = tb2.id
INNER JOIN db1."table 1" AS T1
ON T1.sortcode = tb2.sortcode
AND T1.accountno = tb3.accountno
Hugo Kornelis, SQL Server MVP

Lookup including looking up on null values possible?

In order to insert datekey values in I lookup datekey in the datedimension table. I join on the 'Date' column that contains dates. The datedimension contains one record for 'unknown date' for which the value of the 'Date' column is null.

The behavior that I desire from my lookup transformation is that for input records with a date the corresponding datekey from the datedimension is looked up and for records with date = null, the datekey for 'unknown date' is looked up.

The first part works well but the looking up on null fails, apparently because you can not say null == null. Does anyone know whether there is a setting in the lookup transformation to include null lookups?

Thnx,
HenkThe lookup transform can not do this. You would need to put a derived column in the flow and if the value is NULL then set it to the appropriate 'unknown date' value.

Thanks,|||Thanks Matt.|||In fact it can and it is quite easy! I found out in the documentation:

"A Lookup transformation that has been configured to use partial or no caching will fail if a lookup operation matches columns that contain null values, unless you manually update the SQL statement to include an OR ISNULL(ColumnName) condition. If full precaching is used, the lookup operation succeeds."

|||So by selecting the full precaching option for the lookup, you eliminate the need to modify the SQL with the ISNULL function?|||While this can work as described I would recommend against it and is, therefore, why I didn't mention it. You need to be careful if you do lookups in this way because unless you guarrantee that there is only one such value you will get the first one lookup happens to find with no warning.

Full precaching will not work because the cache is fully charged and doesn't issue the SQL statement again. The reason why partial or no cache works is because the SQL statement is issued if a match isn't found and will return success due to the ISNULL statement as long as there is a NULL in the table.

There are too many ifs and caveats to make this a good solution, IMHO.

Thanks,