Passing a file name to a Connection Property

[GJP55]

Is it possible to pass a file name to the fileName in the connection property of a Source Text Box in a DTS ?

I would like to take the path of an email attachment and pass this as the File name in the Connection property.

Thanks

 

[Glowworm27]

Yes, you can

Create an ActiveX Script Task and copy this into the file

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()

'Create Variables to hold the objects
	Dim pkg
	Dim  conTextFile 

' Set the pkg object to this dts package
	set pkg = DTSGlobalVariables.Parent
'set the contextfile object to the text file connection
	set conTextFile = pkg.Connections("Text File (Source)")

'set the filename as the datasource to the text file connection object
	conTextFile.DataSource = DTSGlobalVariables("gv_EMPFileName").Value


	Main = DTSTaskExecResult_Success
End Function

I am using a global variable, gv_EMPFileName, that holds the filename but you can "hard-code" it if you want to

Hope this helps


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[GJP55]

That looks like just the thing I am looking for - thanks.

What I am not sure about is how to pass the global variable value into this code from outside the DTS ?.

Say I have a DTS saved and I uses a stored procedure or function to execute the DTS (which I have at the moment). How do I pass the value into the DTS Active X code ?

Cheers

 

[Glowworm27]

Check out this article,

It shows how to execute DTS packages from Stored Procedures, and includes a way to pass in global variables.

you need to download the 4 files on page 3 of the article and put those procedures in your database

msoleerror.sql
spDisplayPKGErrors.sql
** This will execute a DTS Package without Global variables
spExecutePKG.sql
** This will execute a dts package with global variables
spExecutePKGGlobalVariables.sql

I made a slight modification to the SPDisplayPKGErrors.sql I commented out the line

SELECT * FROM #PkgResult

so the procedure would only return back a scalar value that I check to see if the package completed succesefully.

Hope this helps

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

I guess it would help to include the URL to the article?

http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1

Sorry

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[GJP55]

Thanks for your reply.

Looks exactly what Im after.
Cheers

 

[GJP55]

I seem to be able to change the global variable but can not execute the DTS. The code below show run the DTS shouldn't it but it doesnt and does not return any errors.


declare @hr int, @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END
-------------------Set the Package--------------------------
EXEC @hr = sp_OAMethod @oPKG,
'LoadFromSQLServer("MyServer", "", "", 256, , , , "ExcelFileTest")',
NULL
IF @hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN
END

--Execute the package
EXEC @hr = sp_OAMethod @oPKG, 'Execute'