"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:
>
> > 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:
> >
> > > 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:
> > >
> > > > 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