Solved

Parameter substitution in DTS package

Posted on 2007-11-21
3
940 Views
Last Modified: 2013-11-30
I have a DTS package containing two global variables, FromEmailAccount and ToEmailAccount.  (These variables are set at design time to normal looking email addresses.)  

The package also contains an Execute SQL task.  Here is the code for the task:

      declare @FromEMailAccount nvarchar(255)
      declare @ToEMailAccount  nvarchar(255)

      set  @FromEMailAccount = ? --Global Variable 'FromEMailAccount' assigned here
      set  @ToEMailAccount  = ? --Global Variable 'ToEMailAccount' assigned here

      update CheckValues
      set
            CheckValStr1 = @FromEMailAccount,
            CheckValStr2 = @ToEMailAccount



When I try to parse the query in the Execute SQL Task Properties dialog, I get a cryptic error message saying "Syntax error, permission violation or other non-specific error".  I get a similar error when I try to use the Parameters button.  

In order to assign the input parameters, I used disconnected edit and set the InputGlobalVariableNames property to "FromEMailAccount";"ToEMailAccount".    

Unfortunately, when the code runs only the first character of the global variables values is written into the CheckValStr1 and CheckValStr2 fields.  Why?  
0
Comment
Question by:DrLechter
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 20336726
Does this work:

update CheckValues
      set
            CheckValStr1 = ?
            CheckValStr2 = ?

Are the global variables set the correct data types? You might want to use the Set Prpoerties task rather than a disconnected edit.

Lastly, this ~may~ help. It has some tricks you can use with parameters (when using SP's)

http://www.sqldts.com/234.aspx
0
 
LVL 4

Author Comment

by:DrLechter
ID: 20382004
Unfortunately, I couldn't solve this issue.   The article was interesting (I had already read it) but none of the infomration helped.  I wound up removing all the substituted parameters as a workaround.  Simpler is better in this case.

0
 
LVL 4

Author Closing Comment

by:DrLechter
ID: 31597117
I couldn't get it working.  But thanks for the effort anyhow!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now