Record difference in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Record difference in SSIS

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-04 : 17:31:47

Hi,

when I run this script on SQL Server Management Studio I get 50,000 records. If I run it in an SSIS package, it runs but I get 0 records returned. Why?

Please help.

Thanks,

Declare @year table
([Acreage] [varchar](5) NOT NULL,
[CurrentYr] [varchar](5) NOT NULL)

insert into @year values ('acres', '2008')

SELECT year.acreage, year.currentyr, property_char.property_id, ROUND(property_char.value,0) as PropID
FROM

property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS pc2 ON property.id = pc2..property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id, @year as year

WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE'
AND
property_char.tax_year = '2008'
AND
pc2.prop_char_typ_code = 'USECD'
AND
(pc2.value not in ('85','86','87','88','95')
AND
pc2.tax_year = '2008' AND
pc2.property_id = property.id
AND
property.pact_code = 'REAL'
AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())
AND
prop_valuation.property_id = property.id
AND
prop_valuation.tax_year = '2008'
AND
prop_valuation.local_assed_ind = 'Y'
AND
val_component.value_type = 'MKLND'
AND
val_component.property_id = property.id
AND
val_component.tax_year = '2008'
AND
val_component.modified_value > 0)
AND NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-04 : 17:48:01

How did you configure it for SSIS package?

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-04 : 18:14:48

In SSIS

I first got a Data Flow Task. In this task I have a "OLE DB SOURCE", In the OLE DB SOURCE I pick the server and the database and paste in my SQL Command, which is the script above. There is an option where I can preview it. When I preview it I only see the column namses. There is no data.

Thanks,

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-04 : 18:24:47

Did you try with Execute SQL task or OLEDB command?

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-04 : 18:50:29

I started an SSIS project.

I first got a Data Flow Task. In this task I have a "OLE DB SOURCE", In the OLE DB SOURCE I pick the server and the database and paste in my SQL Command, which is the script above. There is an option where I can preview it. When I preview it I only see the column namses. There is no data.

Thanks,

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-05 : 10:56:06

I tried it with the ole command

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-05 : 12:38:34

After doing a little testin. Line by Line I found these 2 lines are the the culprit. But why?

pc2.tax_year = '1998' AND
pc2.property_id = property.id AND

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-05 : 12:41:32

Actuall it is only this line here:

--pc2.tax_year = '1998' AND

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources