We help IT Professionals succeed at work.

RUN DTS With Parameters

943 Views
Last Modified: 2013-11-30
Hello,  I've been reading some posts here about creating  a DTS package with parameters but I can't get the hang of it.

Basically, what I want to do is to create a DTS package that will query a table from my database and then pour the results into an Excel Spreadsheet.  I'm supposed to supply this DTS 2 parameters, but let's say for the sake of learning I only need one.

Let's say for example that I want this query in the DTS:

SELECT TheNumber, TheName, TheAddress, TheCity  FROM TheAddresses WHERE   TheStatus = ?
(TheStatus field will be the one that needs the parameter)  it is a string and it's value is = closed.

I went back to the saved DTS and named the global variable I need and even provided the value for it.  

however, when I try to run the package I get an error.  

This is what I use to run the DTS

EXEC master..xp_cmdshell 'dtsrun /s myserver /E /N MySavedDTS '

Could someone please explain what I am doing wrong?   Why if I view the DTS in design mode I can see is trying to create a table called 'Results'?


When I try to run it, I get this errors:

DTSRun:  Loading...
DTSRun:  Executing...
DTSRun OnStart:  Create Table Results Step
DTSRun OnError:  Create Table Results Step, Error = -2147467259 (80004005)
   Error string:  The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
   Error source:  Microsoft JET Database Engine
   Help file:  
   Help context:  5003051
 
Error Detail Records:
 
Error:  -2147467259 (80004005); Provider Error:  -67568648 (FBF8FBF8)
   Error string:  The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
   Error source:  Microsoft JET Database Engine
   Help file:  
   Help context:  5003051
 
DTSRun OnFinish:  Create Table Results Step
DTSRun OnStart:  Copy Data from Results to Results Step
DTSRun OnError:  Copy Data from Results to Results Step, Error = -2147467259 (80004005)
   Error string:  The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
   Error source:  Microsoft JET Database Engine
   Help file:  
   Help context:  5003051
 
Error Detail Records:
 
Error:  -2147467259 (80004005); Provider Error:  -67568648 (FBF8FBF8)
   Error string:  The Microsoft Jet database engine cannot open the file ''.  It is already opened exclusively by another user, or you need permission to view its data.
   Error source:  Microsoft JET Database Engine
   Help file:  
   Help context:  5003051
 
DTSRun OnFinish:  Copy Data from Results to Results Step
DTSRun:  Package execution complete.
NULL


Open in new window

Comment
Watch Question

Author

Commented:
Ok,

I think I finally got it.  I can run DTS with parameters that are strings.  I can change the status value for the query and I do get the records I need.  However, I have some related issues.

First, Why does it need to have the Excel spreadsheet created first.  If it does not exists, it doesn't do anything.  Is there a way to run a package that checks whether the spreadsheet exists in the provided path and if not create it?

Second,  my parameter sometimes will have to be NULL.  I can't run the DTS if I set the parameter value to null.  

Here's the actual code I have in the stored procedure:
CREATE PROCEDURE procExportToSpreadSheet
(@TheStatus varchar(25)= NULL)
AS      
SET NOCOUNT ON

DECLARE @CMD varchar (1000)
SET @CMD = 'dtsrun /s myserver /E /N MySavedDTS /A Stats:8 =' + @TheStatus
EXEC master..xp_cmdshell @cmd

In the DTS Package , I have TheStatus parameter listed as a string and its value is <not displayable>.
Should I change it to something else?

If I run the stored procedure that runs the DTS  and I don't enter a parameter, I only get the word NULL as the output.

Thanks!
CERTIFIED EXPERT
Top Expert 2012

Commented:
>>I can run DTS with parameters that are strings.<<
Parameters can have any datatype, you just need to specify that.

>>Is there a way to run a package that checks whether the spreadsheet exists in the provided path and if not create it?<<
You can check for the existence of the spreadsheet and skip the create task.  See here:
Skip the execution of an individual Task
http://www.sqldts.com/214.aspx

Author

Commented:
Ok.. let's try this.

If the global variable for ? will be a string and I want to set this variable to NULL how can I accomplish so?

In the query it will always be ... where TheStatus = NULL and that doesn't work. It has to be ..where TheStatus IS NULL.  

SELECT TheNumber, TheName, TheAddress, TheCity  FROM TheAddresses WHERE   TheStatus = ?

I can't find a way to change the where clause to make it work when I search for Null values.

CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.