February 26, 2010 at 12:12 pm
Hi Guys
I have a simple module in an Access 2002 Db -
Declarations -
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New DTSLib.Application
Dim pkgResults As DTSExecResult
Dim pkgContainer As IDTSContainer90
MODULE -
pkgLocation = "\\ABCD\DlyTrans.dtsx"
Set pkg = app.LoadPackage(pkgLocation, True, Nothing)
Set pkgContainer = pkg
'pkgContainer.Variables("var1") = 0
pkgResults = pkg.Execute()
The package is simple.
Data Flow task
OLE DB Source (SQL Server 2005 DB )
I have SQL Command text with i query in it.
What i would like to do is change the SQL within the package through my vba code.
So I'm looking for logic like this:
pkgContainer.SQLCommandText = "SELECT ABC FROM MyTable"
(NOTE: - I made up the property here called SQLCommandText)
Can this be done in some manner???
Thanks in advance for looking.
Peter
February 26, 2010 at 3:37 pm
Can you base your package on a view and then change your view through VBA.
DoCmd.RunSQL "ALTER VIEW...
February 26, 2010 at 3:47 pm
Other possibilities include package configurations http://www.sqlis.com/post/Easy-Package-Configuration.aspx or maybe calling DTEXEC and using the SET parameter to dynamically set a variable http://consultingblogs.emc.com/jamiethomson/archive/2005/02/28/1085.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply