Hi folks,

I'm pretty new in DTS (actually it's my first DTS project at all) and
thank you guys for every hint/help in advance.
I'm using VB.NET and MS SQL Server 2000
I need to do the following:
- Read data from SQL-Server and convert it to an excel file
- the SQL-Source-statement is different, every time (number/name of
columns, view/table ...)!
And that's my problem.
I know that there's the possibility to work with global variables,
dynamic properties, active x scripts, custom tasks etc.
But in the end I don't know which technique could solve my problem
best...and where to start, too.
At this point I figured out four steps to solve my problem:
1. Delete Excel worksheet (SQL Ta
2. Fill worksheet with columns
3. Read data from view
4. Fill this data in Excel worksheet
and again, the number/names of columns and the view change everytime!

thanks for your help

best regards

alex

Couple of ways

1. Use a linked server to the Excel spreadsheet and a SELECT INTO statement
2. Recreate the datapump each time. *

* You will need to
1. Read the source statement
2. Create a table in Excel to mimic the source
3. Drop all previous transformations
4. Create the new ones
5. Set the destinationObjectName to the new name of the table


#1 is definitely easier.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Alex" <alexander_simons (AT) web (DOT) de> wrote

regards alex

Yep

To add a linked server (Excel)

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\MyData\DistExcl.xls',
NULL,
'Excel 5.0'
GO


Then to use it do

SELECT * INTO TableMine FROM OPENQUERY(ExcelSource,'SELECT * FROM
[Sheet1$]')


--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Alex" <alexander_simons (AT) web (DOT) de> wrote