I have experiencing an issue where I am using a Dynamic Properties
Task to set the Data Source of an Excel output file in a SQL Server
DTS package.

The Dynamic Properties Task uses a query to return the value and looks
like this:

select 'E:\Reports\Accounting\Daily\SalesReport_' + CONVERT(varchar,
MONTH(GETDATE())) + '_' + CONVERT(varchar, DAY(GETDATE())) + '.xls'

This works fine unless the length of the value returned is longer than
75 characters. I see that by doing a newsgroup search that other
people are seeing this as well.

I just tried the following, and it worked, so I thought I would pass
it on:

select CONVERT(varchar(250),
'E:\Reports\Accounting\Daily\SalesReport_' + CONVERT(varchar,
MONTH(GETDATE())) + '_' + CONVERT(varchar, DAY(GETDATE())) + '.xls')

Seems like a pretty good workaround for a clear bug in DTS.

DTS no. If the bug was with DTS then nothing would be able to set the
property which you have proved is not the case.
The Dynamic Properties task yes.

This also works in an Active Script task.

dim pkg
dim con
set pkg = DTSGlobalVariables.Parent
set con = pkg.Connections("Microsoft Excel 97-2000")
con.DataSource = "E:\Reports\Accounting\Daily\SalesReport_" & Month(Now()) &
"00000000000000000000000000000_" & DAY(Now()) & ".xls"

Main = DTSTaskExecResult_Success
End Function


--

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


"James Hatcher" <jhatcher (AT) g1c (DOT) net> wrote