error using parameters in dts
-
I have a dts package "myDTSPkg" where I have a connection and an "Execute SQL Task". In the "Execute SQL Task" I have a stored procedure with 4 parameters like the following:
exec myStoredProc ?, ?, ?, ?
The parameters are all strings:
CREATE PROCEDURE [myStoredProc]
@AgentID VARCHAR(25),
@Year VARCHAR(4),
@Period VARCHAR(2),
@Day VARCHAR(2)
AS
.....
In the global variables list I have
Name Type Value
AgentID String <not displayable>
Year String <not displayable>
Period String <not displayable>
Day String <not displayable>
and I mapped the parameters of the "Execute SQL Task" to these variables.
If I physically put in values in the Value column for the Variables then the stored procedure takes the Year, Period and Day fields combines them and stores them into a SMALLDATETIME variable properly:
DECLARE
@_UpdateFromDate VARCHAR(20),
@UpdateFromDate SMALLDATETIME
SET @_UpdateFromDate = @Period + '/' + @Day + '/' + @Year
SET @UpdateFromDate = CONVERT(SMALLDATETIME,@_UpdateFromDate)
but when I use the DTSrun utility to do this it gives me an error saying:
Error: -2147217913 (80040E07); Provider Error: 295 (127)
Error string: Syntax error converting character string to smalldatetime data type.
my dtsrun command is :
dtsrun /S "MyServer" /N "myDTSPkg" /A "AgentID":"8"="13321" A/ "Year":"8"="2004" A/ "Period":"8"="7" A/ "Day":"8"="1" /E
According to :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp
"8" is the type id for String.
Please help. I have been stuck on this for the last week and it is driving me crazy!!
Thanks in advance.
-
I am almost certain it is because you are supplying single figures.
I.E in your example you get 8/1/2004.
You need to put some logic in to change this so it becomes 08/01/2004.
------------------------------
The Users are always right - when I'm not wrong! -
I have tried that also:
dtsrun /S "MyServer" /N "myDTSPkg" /A "AgentID":"8"="13321" A/ "Year":"8"="2004" A/ "Period":"8"="07" A/ "Day":"8"="01" /E
and still get the same error. I also tried creatind another package where the stored procedure has 2 parameters 1. AgentID (String - varchar(25)) and a thisDate (Date -smalldatetime) and in the global variable list I tried to create:
Name Type Value
AgentID String <not displayable>
thisDate Date
But I recieved error "Could not convert variable thisDate from type BSTR to type Date".
I cant create a variable of the Date. Can figure that out either.
-
You need to do a Dynamic Properties Task Properties to put the dts parameters to the global variables in the DTS Package. It will not automatically put in the global variables.
Hope it helps.
-
I have added a "Dynamic Properties Task" and created the following:
Destination Property Source Type Source Value
Value Global Variable AgentID
Value Global Variable Year
Value Global Variable Month
Value Global Variable Day
where are set to the global variables of the same name. the "Dynamic properties Task" executes fine but then I get the same error as before. what can I be doing wrong.
-
When the variables show <not displayable> for the value, you cant change them.
You'll have to delete the global variables and start again. When you create the variables, put in an initial value. Make sure its the right datatype otherwise the type will change when you close the window
--------------------
Colt 45 - the original point and click interface
-
I am not trying to change the value. I am trying to change the data type for the variable and it is not letting me. But I have worked around it. I am storing all the input parameter/value pairs in a properties table and pulling them up in the dynamic properties task. It seems to be working so far.
thanks for helping everyone.
-
As I was saying, if the global variable has <not displayable> for the value, you can't change it. You have to delete it and create it again.
--------------------
Colt 45 - the original point and click interface -
I think you will find that even though you were passing in "01" it was still treating it as 1.
If you put in an insert into a table in your code, you will find exactly what is going on.
------------------------------
The Users are always right - when I'm not wrong! -
I don't know if you already did that. But there is two thing that come to my mind :
- Check to make sure you binded the global variable to their alternative;
- You might want to use dtsrunui tool to generate the command shell statment for u with the parameters and the right data type.
-
I've been inherited a DTS project.
Can you explain why the global variable needs to be recreated when it shows <not displayable> ?
Is this a bug?
I'm in a situation where a lot of my global variables show <not displayable>.
The package uses Dynamic Properties Task, I always thought that it shows <not displayable> because the values are dynamic.
Help?
-
Basically the global variable has become "corrupted", the GUI interface can't handle correcting the problem, so you have to delete and re-create the variable.
You may be able to fix it programmatically, but I've never tried it, and I think it would be much easier to go via the re-create route.
--------------------
Colt 45 - the original point and click interface -
But what I don't understand is that the package runs without any problems. So is this just a UI problem?
-
Yes.
--------------------
Colt 45 - the original point and click interface
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply