September 4, 2002 at 9:37 am
Dear all I have developed many a package on SS 2k using ASP front passing DTS parameters to package and using the params in DTS Source query.
Example:
Select * from Customers where custNumber=?
I am now doing some development on SS 7.0 and am now having trouble doing the same sort of thing. The param passed from ASP page are not being recognized in DTS transform source query select ...where...=?
In 2k simple click parameters button to link the ? to the appropriate Global variable but in 7.0 I am unable to do this.
ANy clues?
My thanks
September 9, 2002 at 11:58 am
Dim objPackage
Set objPackage = CreateObject("DTS.Package")
objPackage.LoadFromSQLServer "servname","","","256",,,,"Pkgname"
' set the global variables
objPackage.DTSGlobalVariables.Item("somevariable").Value=somevalue objPackage.DTSGlobalVariables.Item("somevariable").Value=somevalue
' Execute the Package
objPackage.Execute
objPackage.Uninitialize()
objPackage = Nothing
Checkout http://www.SQLDTS.com
I suggest calling a stored procedure to start your DTS pkg if your moving lots of data. You can find that code at SQLDTS.com also.
Good luck
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
September 9, 2002 at 1:22 pm
Thanks Rocker,
lol
I am not currently having the problem in the ASP. The problem I am experiencing is perhaps a difference between 7.0 and 2000.
From what I have read seems like 7.0 does not support sql loader parameters.
Can anyone confirm this?
Thanks
September 9, 2002 at 1:30 pm
SQL7 DTS only uses DTSGlobalVariables, sorry I thought I had made that clear. You must use DTSGlobalVariables. Theres no other way, period.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
September 9, 2002 at 3:36 pm
Thanks again Rocker,
Not there yet though. Back to my original post, if you cannot use sql source loader params with 7.0 like 2k, can I still do what I was attempting?
I have a package that is transforming a query to a xls file dynamically via a sql loader param. If I am stuck using DTSGlobals in 7.0 how does on go about this still?
Thanks
September 9, 2002 at 3:58 pm
Not sure I understand your problem but I'll give it another shot. The following is a sample script which re-directs an excel connection in a DTS Pkg.
'*****************************************
' Visual Basic ActiveX Script
'*****************************************
Dim objConn, objPkg
function main()
Set objPkg = DTSGlobalVariables.Parent
Set objConn = objPkg.Connections("excelconn")
objConn.DataSource=objPkg.GlobalVariables.Item("strpathfile").Value
Main = DTSTaskExecResult_Success
end function
'*****************************************
Open the DTS designer / Package / properties / Global Variables.
Set your variable name, type, & default value.
The ASP code I posted before will take charge of these, changing the value in your ActiveX script.
Set the script to run first then on success run your excel connection.
I think you've got it all now.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
September 9, 2002 at 4:14 pm
Rocker much thanks.
I get the jist of that and will give it a go.
September 10, 2002 at 8:59 am
Getting closer with firing an Active X script before rest of package.
*****************************************
' Visual Basic ActiveX Script
'*****************************************
Dim objConn, objPkg
function main()
Set objPkg = DTSGlobalVariables.Parent
Set objConn = objPkg.Connections("excelconn")
objConn.DataSource=objPkg.GlobalVariables.Item("strpathfile").Value
Main = DTSTaskExecResult_Success
end function
'*****************************************
I'm right there but in the above example you are changing the connections datasource on the fly.
I need to change the transformations sources sql query, what would be the path to drill in there?
Conn1 (Sql Server)
|
|
|
V
Conn2 (xls file)
In the Transformation, Source (Conn1) has a sql query to pull data dynamically using ex(select * from Customers where customerName=?)
Specifically, what objects do I use to drill in to the tranform sources '?' ?
Help appreciated
September 10, 2002 at 9:38 am
OK, same idea using the DTSGlobalVariable.
Go to your Transform data workflow connection(data pump). Open it's properties and go to the tranformations tab. Delete your existing mapping then select an ActiveX Script for your transformation. Using the same process as before, give the script a DTSGlobalVariable for your customer number.
Thats it, make sure your field mapping is correct.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
September 10, 2002 at 11:58 am
Got it!
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
dim mySQL
Function Main()
dim oPK,sqlParam,myTask
set oPK=DTSGlobalVariables.Parent
set myTask= oPK.Tasks("DTSTask_DTSDataPumpTask_1")
set sqlParam=myTask.properties("SourceSQLStatement")
sqlParam.value=LoadSQL
Main = DTSTaskExecResult_Success
End Function
Rocker you put me on the right path. Stuck an Active X script before the transformation and dynamically changed the SQL value for the Source.
Go get yourself a beer on me.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply