Hi people,

I need to extract 6 views to the same Excel file. I have to extract many
times. So I think: create a DTS package and create a Global Variable
with the name of the file, so I can run the package passing the name of
the file.

Does anyone have any example of this kind of configuration?

Thanks a lot,
Cristiana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK but Excel doesn't work quite like a Text file.

I have a package called XLRename which pumps to an XL spreadsheet defined by
a Global variable. I have set the GV to the same path as when I originally
set the connection.

Works beautifully

C:\>dtsrun /E /NXlrename /S.
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 24 Rows have been transformed
or
copied.; PercentComplete = 0; ProgressCount = 24
DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.


Now watch what happens when I change the datasource.


C:\>dtsrun /E /NXlrename /S. /AXLSource:8=c:\DelBadBoy.xls
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217865 (80040E37)
Error string: The Microsoft Jet database engine could not find the
object 'a
uthors'. Make sure the object exists and that you spell its name and the
path n
ame correctly.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003011

Error Detail Records:

Error: -2147217865 (80040E37); Provider Error: -543884569 (DF94FAE7)
Error string: The Microsoft Jet database engine could not find the
object 'a
uthors'. Make sure the object exists and that you spell its name and the
path n
ame correctly.
Error source: Microsoft JET Database Engine
Help file:
Help context: 5003011

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.


OK so how do I fix it.?

Easy

In the package I set up a task that will CREATE the authors table for me
before the pump happens. I do this with an ExecuteSQL task and it needs the
Excel connection as the data source.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Cristiana Martins" <crisgmartins (AT) yahoo (DOT) com.br> wrote

Hello Allan, thanks a lot for your reply!

I new with DTS packages... I've got the same error than you, so I
thought I was doing something else wrong

When you say I need to create a ExecuteSQL task to Create the authors
table, it is the table where am I extracting from correct?

Thanks again!
Cristiana

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Just one more question, how can I associate the global variable to the
Connection Data Source? (Dynamic Properties?)


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

In the Dynamic Properties task you

Click ADD | Navigate to the connection |find the dataSource property |double
click | Choose Global Variable as the Source and drop down the box to choose
the variable

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Cristiana Martins" <crisgmartins (AT) yahoo (DOT) com.br> wrote

Hi Allan,
I did the following:
Opened the Import & Export Wizard and made an export the 6 views to
Excel. At the end, I saved the package.

After this, I have opened the DTS Designer. I can see 6 groups like
this:
Execute SQL Task -> Connection to DB -> Excel File

In the icon of Excel file, I have the 3 names 'Connection 2' and the
other ones are 'Connection 4'

So, I have created the Global Variable called filename.

After this, I put a Dynamic Properties Task, pointing the Connection 2
DAtaSource to Global Variable filename and pointing
Connection 4 DAtaSource to Global Variable filename.


After this I run the package in Query Analyser:

exec master.dbo.xp_cmdshell 'dtsrun /Smyserver /E /Pmscsql /NTeste21jan'


It works ok, creating the file that was defined in Import/Export Wizard
(exportado.xls).

After this, I tried:
exec master.dbo.xp_cmdshell 'dtsrun /Smyserver /E /NTeste21jan
/Afilename:8="c:\testecomVAR.xls"'

and

exec master.dbo.xp_cmdshell 'dtsrun /Smyserver /E /NTeste21jan
/Afilename:8=c:\testecomVAR.xls'


So, I have received many messages like this:

DTSRun OnFinish: Create Table 7_HC_EXP Step

DTSRun OnProgress: Copy Data from 7_HC_EXP_18_20040117 to 7_HC_EXP
Step; 1 Rows have been transformed or copied.; PercentComplete = 0;
ProgressCount = 1

DTSRun OnFinish: Copy Data from 7_HC_EXP_18_20040117 to 7_HC_EXP Step

DTSRun OnError: Create Table 6_AIR_EXP Step, Error = -2147217900
(80040E14)

Error string: Table '6_AIR_EXP' already exists.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003010



Error Detail Records:



Error: -2147217900 (80040E14); Provider Error: -534840599 (E01EFAE9)

Error string: Table '6_AIR_EXP' already exists.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003010



DTSRun OnFinish: Create Table 6_AIR_EXP Step

DTSRun: Package execution complete.

============

I can see the package run and mades a append in the file exportado.xls.
I think it is not reading my global variable.... What I'm doing wrong?

Thanks a lot~!
Cristiana


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

You need to make sure that the Dynamic Properties fires before your datpumps
so


Dynam P Task -->ExecuteSQLTask -->Source ------->Destination



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Cristiana Martins" <crisgmartins (AT) yahoo (DOT) com.br> wrote

You are fantastic!!!
It worked!

Thanks a loooooot for your attention and now I will study more about
packages ! ;-)

Cristiana



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!