SS 7.0 problem with DTS Parameters

Question

Post reply

SS 7.0 problem with DTS Parameters

Mark_maddison

Right there with Babe

Points: 768
More actions
September 4, 2002 at 9:37 am

#357604

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

rocker333 Ten Centuries Points: 1315 More actions · Answer 1

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

Mark_maddison Right there with Babe Points: 768 More actions · Answer 2

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

rocker333 Ten Centuries Points: 1315 More actions · Answer 3

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

Mark_maddison Right there with Babe Points: 768 More actions · Answer 4

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

rocker333 Ten Centuries Points: 1315 More actions · Answer 5

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

Mark_maddison Right there with Babe Points: 768 More actions · Answer 6

Rocker much thanks.

I get the jist of that and will give it a go.

Mark_maddison Right there with Babe Points: 768 More actions · Answer 7

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

rocker333 Ten Centuries Points: 1315 More actions · Answer 8

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

Mark_maddison Right there with Babe Points: 768 More actions · Answer 9

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.