How do you call a Stored Procedure from within a DTS Package?

[uncleroydee]

How does one call a stored procedure to insert a value into the data being imported by a DTS package?

The DTS package imports user information from an Excel spreadsheet. The spreadsheet contains a column, but no values, for the password. I have a SP to create random passwords and would like to run that SP as the DTS package executes.

Here's the DTS Transformation that I tried (it failed):

Function Main()
DTSDestination("username&quot = DTSSource("User Name-Note1&quot
DTSDestination("password&quot = (EXEC random_password @Password_type = 'complex')

Thanks,

Roy

 

[JFoushee]

Have you tried putting the owner's name (eg 'dbo', 'username') in front of the stored procedure?

EXEC dbo.random_password