Is is possible to sum data being imported in DTS without actually putting the
data details on the 'hard drive'.

I want to SUM fields on some incoming .TXT records. I don't actually want
all the indiidual recods just the sums.

Hi Mike,

In SQL Server 2005, this is easy to do as you have two options: (1)
selecting from the text file directly (2) using SQL Server Integrations
Services (SSIS), an in-memory data migration and transformation engine.

In SQL Server 2000, you have to use the DSO Rowset provider, which allows
your package to be queried using SELECT instead of writing to a data
destination:

1) Create a package and configure a Transform Data task to take data from
your text file to a table. (This is just setting it up; you wont be writing
to the table).

2) Right-click on the Transform Data task and choose Workflow Properties.

3) On the Options tab, select the option: DSO Rowset Provider

4) Save the package.

Now you can query the package and it will not write the data to the
destination table. Instead, it will return it as a result set.

If you saved the package to SQL Server, you query the package from SQL using
Openrowset and the DTSPackageDSO OLE DB provider:

select * from Openrowset('DTSPackageDSO', '/SMyServer /Usa /PMyPassword
/NMyPackage', 'SELECT *')

If you saved the package to a file, you query it like this:

select * from Openrowset('DTSPackageDSO',
'/Fc:\MyPackagesFolder\MyPackageFile.dts, 'SELECT *')

You can modify the main SELECT statement to aggregate the data as you wish.
You can use SQL query as a source for the next package step.

Hope this helps.

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" URL: http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services" URL:
http://www.learningtree.com/courses/523.htm



"MikeChicago" wrote:

I got it to work fine now on my machine at home thanks. But I can't seem to
set the userid and password at work. Is there a way to use a trusted
connection with the OPENROWSET?

"Charles Kangai" wrote:

Instead of using /U and /P, use /E.
/E means "trusted connection".

For example:
select * from Openrowset('DTSPackageDSO', '/SMyServer /E /NMyPackage',
'SELECT *')

Charles Kangai, MCT, MCDBA

Author of Learning Tree's 4-day course: "SQL Server 2005 Integration
Services" http://www.learningtree.com/courses/134.htm
Author of Learning Tree's 4-day course: "SQL Server Reporting Services"
http://www.learningtree.com/courses/523.htm





"Mike" wrote:

Thanks got it to work fine now

"Charles Kangai" wrote:

Sorry for jumping in. Could you explain the SQL 2005 options in more detail?
I am import a Excel file and would like to sum the total and compare it at
end of SSIS in order to make sure nothing got missed.

Thanks,

"Charles Kangai" wrote: