Hi All,

I am trying to execute a package with Global Variables which takes dynamic
date and excel filename and path from vb to global variables ,and creates
dynamic sql in ActiveXScript and executes the package.
Package runs fine from Sql Server for the first time but if i change the
excel filename each time it fails saying no table found. How do i get throu
this?

Any ideas appreciated.

Thanks
Sudha

In message <D4B988B5-42D8-4165-B32B-7D55285F9FB2 (AT) microsoft (DOT) com>, sudha
<sudha (AT) discussions (DOT) microsoft.com> writes
The Excel file must exist before you an export to it, and the sheet must
also exist with the correct name. The sheet is the table, as seen in the
DataPump task, destination object drop-down.

To ensure you have a correctly formatted Excel file I suggest creating a
"template" file. At the start of the package, copy the template to the
name of your required export file. This ensures the file is always
valid, and clean.


--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Should i do this from Active X Script ? How do i do this and from Where? VB
or Active X Script.

I am trying Something like this with the script to make it dynamic , Am i on
the wrong track,If possible can you help me with some code where to look for.



Function Main()
Dim oPkg, oDataPump, sSQLStatement

' Build new SQL Statement

sSQLStatement = " SELECT DISTINCT
tblFEDetails.fe_HRASerialNumber,ltrim(rtrim(upper( tblHouseholds.hh_LastName)))
+ ' ' + upper(tblHouseholds.hh_FirstName) as Name,convert(varchar(14),
tblenrollperiods.ep_adddate,102) as adddate " & _
" FROM tblFEDetails INNER JOIN " & _
" tblEnrollPeriods ON tblFEDetails.fe_epKey =
tblEnrollPeriods.ep_Key INNER JOIN " & _
" tblHouseholds ON tblEnrollPeriods.ep_hhKey =
tblHouseholds.hh_Key INNER JOIN " & _
" tluUsers ON tblEnrollPeriods.ep_addUser =
tluUsers.usr_Key " & _
" WHERE (tblFEDetails.fe_HRASerialNumber IS NOT
NULL) AND (tblFEDetails.fe_HRASubDate) > '" &
DTSGlobalVariables("EpDate").Value & "' "



msgbox (ssqlstatement)
Dim objconn2
Set objConn2 = DTSGlobalVariables.Parent.Connections("conn2")
objConn2.DataSource = DTSGlobalVariables("Hfilename").Value

msgbox ( DTSGlobalVariables("Hfilename").Value )
' Get reference to the DataPump Task

Set oPkg = DTSGlobalVariables.Parent
Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

' Assign SQL Statement to Source of DataPump
oDataPump.SourceSQLStatement = sSQLStatement
oDataPump.DestinationSQLStatement = "CREATE TABLE NewTable3
(ANumber Long , Name VarChar (61) , adddate VarChar (14) )"
oDataPump.DestinationObjectName = "NewTable3"
Dim oTransformation
Set oTransformation
=oDataPump.Transformations.New("DTS.DataPumpTransf ormCopy")
oDataPump.DestinationRowSet

msgbox oDataPump.DestinationSQLStatement

' Clean Up
Set oDataPump = Nothing
Set oPkg = Nothing

Main = DTSTaskExecResult_Success



End Function



Thanks For any Help
Sudha


"Darren Green" wrote:

You can use either an ActiveX Script Task to take your variables and use
them to change the SQL or you can do this all in VB up front before you
execute the package. Both will work, and it is a matter of personal
choice, and how encapsulated or independent you want the package to be
from the method of execution.

I you are just changing the where clause value of the SQL statement then
you could actually use a parameter in the query, through the designer.

SELECT * FROM Table WHERE DateCOl > ?

Click "Parameters" in the Source tab of the DataPump and then map a
variable to the parameter listed.

To set the filename you could use a Dynamic Properties Task to take the
variable and assign it to the connection.

Is a CREATE TABLE statement valid for the DestinationSQLStatement
property? I assumed not. I would do the CREATE TABLE up font on an Exec
SQL Task, or as this is really create an excel sheet, the template
method I described before could be used instead.

Using these method you could dispense with the scripting.

Cheers

Darren

In message <B613D69C-8D42-4D68-B9F1-8C147AEB1245 (AT) microsoft (DOT) com>, sudha
<sudha (AT) discussions (DOT) microsoft.com> writes
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org