November 17, 2009 at 2:00 pm
I am trying to load data from sql 2008 to another server where an oracle-like database resides (long story but it looks like Oracle but it's not and no one will tell me what it is). I got the connection to work fine and the data loads gently into the data table. The problem is this.
We use a unique number for the file number and this number needs to consist of the value in a holding table and then the next consecutive number. So I have two challenges - calculating a consecutive file number and then loading the last file number back into the holding table for next time data is loaded.
I got the consecutive 1 - ? working for the incoming data; I got the update code working on SQL2008; but when I put the code into a OLE DB command, the file numbers come out consecutively (Yay) but these aren't the droids, oops, numbers we are looking for.
Here are my examples:
This is before I run the update
EntityNum EntityID Name
1 12314 Smith
2 14839 Kenobi
3 44949 Oscar
This is if I run the update query in sql2008 where the holding table id is 239119
EntityNum EntityID Name
239120 12314 Smith
239121 14839 Kenobi
239122 44949 Oscar
However, when I put the update query into the OLE DB command, the data inexplicably comes out as this:
EntityNum EntityID Name
1912953 12314 Smith
1912954 14839 Kenobi
1912955 44949 Oscar
Any suggestions?
November 17, 2009 at 3:03 pm
I know that they won't tell you what the target DB is, but can they confirm that the target field data type is integer (big or std)? Th enumbers you supplied don't line up exactly, but it almost smells like the target DB could be interpreting your integers as dates, and then storing these in a Julian format.
Assuming the above *sin't* the case, can you post some SQL to show what the update statement/s look like?
Steve.
November 17, 2009 at 3:07 pm
I knew if I had an extra pair of eyes looking at this besides my cat, who doesn't care, it would make more sense to me. I'll look at it first thing in the morning and let you know!
Cheers!:w00t:
Peggy
November 18, 2009 at 7:11 am
As far as I can determine, everything is int, int, int. However, I've narrowed it down to the query I set up to update the entitynum. It is apparently iterating through the records, instead of just concentrating on the individual record. I'll try to explain the steps.
First, I create a temporary table from a variety of sources where the EntityNum is null. I then use this query to put a temporary consecutive EntityNum(I've edited out some of the fields and changed the names to protect the innocent):
if not OBJECT_ID('dbo.tmp_ZW_intakeEntities') is null
drop table dbo.tmp_ZW_intakeEntities;
go
create table dbo.tmp_ZW_intakeEntities(EntityNum int not null,EntityRole varChar(16),
CaseMarker int not null,EntityID nvarChar(16),cLassType varChar(1) not null,
LastName nvarChar(502), FirstName nvarChar(250),MiddleName varchar(1) not null,
RefName nvarchar(101));
go
insert into dbo.tmp_ZW_intakeEntities(EntityNum, EntityRole, CaseMarker,EntityID,ClassType,
LastName, FirstName,MiddleName,RefName )
SELECT distinct (SELECT COUNT(*) AS EntityNum
FROM dbo.qry_ZLoanData AS b
WHERE (a.EntityID > EntityID) OR
(a.EntityID = EntityID) AND (a.EntityID >= EntityID)) AS EntityNum,
MAX(CASE WHEN a.Loan_Class = 'HOLIDAY' AND
a.State = 'OZ' THEN 'AUSTRALIA' ELSE CASE WHEN a.Loan_Class = 'HOLIDAY' AND
a.State = 'US' THEN 'UNITED' ELSE CASE WHEN a.Loan_Class = 'VACATION' AND
a.State = 'OZ' THEN 'NEW ZEALAND' ELSE CASE WHEN a.Loan_Class = 'VACATION'
AND a.State = 'US' THEN 'CANADA' ELSE ' ' END END END END)
AS EntityRole, 1 AS CaseMarker, a.EntityID, ' ' AS ClassType, LTRIM(MAX(SUBSTRING(a.Property_Address_1, CHARINDEX(' ', a.Property_Address_1) + 1,
LEN(a.Property_Address_1))) + ', ' + LEFT(a.Property_Address_1, CHARINDEX(' ', a.Property_Address_1) - 1)) AS LastName, MAX(a.Investor_Loan_Number) AS FirstName, ' ' AS MiddleName, MAX(dbo.qry_MtgrAddresses.MtgrName) AS RefName
FROM dbo.qry_ZLoanData AS a INNER JOIN
dbo.qry_ZAddresses ON a.NTRID = dbo.qry_ZAddresses.[File Ref#]
GROUP BY a.EntityID, a.NTLID, a.Property_Address_1,a.EntityNum
Next, I want this to run.
update dbo.tmp_ZW_intakeEntities
set dbo.tmp_ZW_intakeEntities.EntityNum = dbo.qry_NewEntityNum.NewEntityNum
FROM dbo.tmp_ZW_intakeEntities INNER JOIN
dbo.qry_NewEntityNum ON dbo.tmp_ZW_intakeEntities.EntityID = dbo.qry_NewEntityNum.EntityID
Seems pretty simple and the above update query does its thing quite well manually run on the server, but not in the ssis package. Fyi - there is a one-to-one correlation for the EntityID between the temporary table and the query.
November 19, 2009 at 11:49 am
Today I tried a few more things. I took the update query and turned it into a stored procedure which, I figured would run on the server, not within the package. I did call this procedure (there are no parameters) on the sql command and when I ran the step, nope, no improvement. I've tried changing the fields to strings, but it didn't help. I just don't understand why it runs perfectly on the server but not within the SSIS package. Really would appreciate some guidance here. :ermm:
November 19, 2009 at 12:27 pm
Is the problem really that you're seeing different entity Nums between your SQL (dev?) environment and the production target environment? If so, are the record counts identical between the two tables? Your entity num is being generated by count(*) across the table where the entity_id is >= the other entity id's in the table. So different record counts in the table could/will influence the entity nums being produced.
Just a note, for executing against SQL Server, this part of your code
SELECT COUNT(*) AS EntityNum
FROM dbo.qry_ZLoanData AS b
WHERE (a.EntityID > EntityID) OR
(a.EntityID = EntityID) AND (a.EntityID >= EntityID)) AS EntityNum
where you may be expecting this to be the equivalent of ( (a.EntityID > EntityID) OR (a.EntityID = EntityID) ) AND (a.EntityID >= EntityID) - it's not due to the precedence of operators. It will evaluate the AND before the OR. Not that it overly matters as you've basically declared the same thing twice.
Steve.
November 19, 2009 at 1:40 pm
Steve,
For the (a.EntityID > EntityID) OR (a.EntityID = EntityID) ) AND (a.EntityID >= EntityID) part, this is what I did.
When I took everything from OR on out, I got 0,1,2,3,4,5,6,7. When I took the AND out, I got 1,1,1,1,1,1,1,1. So apparently, having these two together gives me 1,2,3,4,5,6,7,8. And that was what I wanted.
However....:w00t:..you gave me a brilliant idea. Instead of bashing my head against my laptop, I thought why don't I just pull in the entity number from the staging table and add it to the above. It worked! No updating necessary!
Thanks! As soon as I get my handy,dandy SSIS book I'll look up this issue and maybe it was just something I missed setting up the package.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply