PARAMETERIZING A DTS PACKAGE 3

[PrgrmsAll]

I have a DTS package that perfomrs several transformations, namely moving data from an ODBC (Oracle) source to a SQL Server table and from a SQLServer table to a flat-file.

The SOURCE tab on the 'Transform Data Task Properties' dialog specifies a SQLQuery that I wrote as the source.

My question is this: can I prompt the operator to enter a parameter for this SQLQuery? I have looked into the parameters functionality and this would be fine if I knew the parameter variable (which is a date). But this date needs to be entered by the user/operator.

Any suggestions?

To compound the problem, I am actually remotely running the DTS from a VB application that I wrote. I would ideally love to pass in the variable from it but, baby steps.

Thanks in advance.

 

[Catadmin]

I know of no way to make a DTS job prompt for user interaction or accept any input what so ever. Sorry.

Even the dtsrunui command doesn't appear to accept input other than where the package is located and what username/password is required to run it.

The only other option I can think of is by creating a stored procedure which uses dynamic SQL to create the DTS job on the fly. The SP would allow the input of a date.

BTW, the above suggestion has no warranties on it. I have never used dynamic SQL to create a DTS job on the fly and generally don't advise it to anyone due to issues that dynamic SQL can cause with your server.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ProdAir]

If you're running the package from EM, you can use an ActiveX Script task to prompt the user for input using the InputBox function.

 

[JayKusch]

Always thinking out of the box here BUT ... you could accept the user-defined-date via your web app and load it into a table within a DB or you could create and dynamic INI file and load the date in there.

Both can server the purpose later to dynamically load a DTS global variable that can be leveraged in your master and possible sub-packages.

Thanks

J. Kusch

 

[PrgrmsAll]

My SQL query looks like the following. If I was to have my application enter a date into a table (great idea), to be retrieved by the query, how would that be incorporated into the SQL query?
==

CURRENT CODE:
=============
SELECT FIELD_LIST
FROM VIEW_A
WHERE ENDDTE > getdate()
==

POSSIBLE CODE:
==============
SELECT FIELD_LIST
FROM VIEW_A
WHERE VIEW_A.ENDDTE > (SELECT MyDT FROM DT_TABLE)

 

[ProdAir]

Thanks for the star. If you need to enter a parameter value for a query in DTS, there are 2 ways to do it. Using the execute SQL task, you can do this:

SELECT FIELD_LIST
FROM VIEW_A
WHERE VIEW_A.ENDDTE > ?

and then click on the parameters

Or using ActiveX script task, prompt the user for the criteria, store it in a variable (and/or DTS global variable). Once you have it stored, you can either build your query string inside your ActiveX script and use the DTS object model to change your query or do the above (query with a question mark). Hope that helps. You can find more info about DTS in sqldts.com.

 

[PrgrmsAll]

My solution was actually a mix of people's suggestions. I did develop the table method where my application inserted a value into the table which was retrieved by the DTS easily. This worked remarkably well. However, always wanting to be slimmer, more efficient I continued to try to eliminate the table, if possible. Thanks to a post, I got some brilliant code off sqldts.com which enabled me to remotely set my GlobalVariable to a date of my choice using the DTS objects. This removed the need for the table. It is all working perfectly. Thanks to everyone who posted, much appreciated.