I have read all the post about passing parameters from sql task in and out and have tried them all with no anvile. Frist of, they are too many types to deal with now, db types, SSIS types. Why the hausl. Can we make data type flow a bit more easy. Well, enough about the complain. I have been trying to dynamically set my sql query statement for my datasouce component using a variable set by a sql task. The tried a couple of methods.
Method 1
Using sql task, ole connection and stored proceduer( exec usp_mystoreprocedure ? OUTPUT) with output parameter (User::sql_query out varchar 0). My stored procedure generate a sql statement as nvarchar and sets the output variable with that. Now when i parse query, i get the error
TITLE: SQL Task
------------------------------
The query failed to parse. Syntax error, permission violation, or other nonspecific error
------------------------------
BUTTONS:
OK
------------------------------
So first of my parser is saying my syntax is wrong that that what most of the post in this form says.
When that didnt work, i decided to return the result and catch it with an returnvalue variable but that give the same error
Method 2:
I tried using ADO.NET instead of ole so did about the same thing except changed the parameter to @SQLQUERY which is the same name as my output parameter on my stored proceduere. Check the query to exe [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP] @SQLQUERY OUT and parameter settings on sql task as (User::sql_query out string @SQLQUERY OUT) and the error it generated when i run the task was
[[Execute SQL Task] Error: Executing the query "exe [dbo].[sp_GET_SQL_QUERY_FOR_SSISGRP] @SQLQUERY OUT" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@SQLQUERY"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Method 3:
I made a QUERYSTATEMENT bridge table. The stored procedue inserts the sql query statement into the QUERYSTATMET TABLE and then i use sql task to pull the statement, set it to a variable and then set my sqlstatement for my datasource component using experessions. Every things works fine till the sql task exectues to pull the sql statement from the QUERYSTATEMENT TABLE.
First, I used OLE connection so my statement was "SELECT ? = STATEMENT FROM QUERYSTATEMENT WHERE TBLNAME = ' MYTABLENAME' " and parameter was set up as (User::sql_query out varchar 0). I parsed this statement and got the error
TITLE: SQL Task
------------------------------
The query failed to parse. Syntax error, permission violation, or other nonspecific error
------------------------------
BUTTONS:
OK
------------------------------
So it looks like OLE doesnt like me. I tried ADO.NET connection and changed the variables ? to @SQLQUERY. So my statement was now "SELECT @SQLQUERY = STATEMENT FROM QUERYSTATEMENT WHERE TBLNAME = ' MYTABLENAME' " and parameter was (User::sql_query out string @SQLQUERY). When i executed the sql task, i got
[Execute SQL Task] Error: Executing the query "SELECT @SQLQUERY = QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" failed with the following error: "Must declare the scalar variable "@SQLQUERY".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So at this point, im really out of luck. I even tried setting an object variable with a record set from the result of sql task executing -> SELECT QUERYSTATEMENT FROM QUERYTEXT WHERE TBLNAME = 'CJP'" but when i tried casting the object variable into a string in my datasource component expression, it indicated that variables of type User::Object cannot be used in expression. If someone can help me, I will be very glad. I would also like to know what types to use in SSIS sql task when getting data from a database. Example, I was returning a datatype of nvarchar from my db, do i recieve this datatype as string, AnsiString etc. I know int, numeric can map to Int32 (if from 86x) or Int64. Can we have a table of data mapping if possible.
PLEASE PLEASE PLEASE HELP !!!!!!
I have a SQL Task that calls a stored procedure and returns an output parameter. The task fails with error "Value does not fall within the expected range." The Stored Procedure is defined as follows: Create Procedure [dbo].[TestOutputParms] @InParm INT , @OutParm INT OUTPUT as Set @OutParm = @InParm + 5 The task uses an OLEDB connection and has a source type of Direct Input. The SQL Statement is Exec TestOutputParms 7, ? output The parameter mapping is: Variable Name Direction Data Type Parameter Name User::OutParm Output LONG @OutParm
declare @string varchar (5000) select @string='Update product Set Product_End_dt = '12/31/05' from ' + @table_nm + ' c inner join Booktable b On (c.book_id = b.book_id).............
exec (@string) -- I need this kind of a proedure with the table name as avariable,but how do I concatinate the quotes in the date??Thanks for any help! Reddy.
I have two parameters I have dropdown with 2007 , 2008 values the second dropdown should be disabled until the user select 2007 or 2008. if the user select 2007 i will populate the second dropdpwn a list with 2007 weeks. if the user selects 2008 the second dropdown will be populated with the 2008 weeks. can i disable paprameter in sql server report
On production server, I have two tasks in status "performing completion action" for last couple hours. Ususally these tasks take seconds to finish. Do you know how can I kill or cancel these tasks? I tried to stop both the jobs couple times but nothing is happening.
There are no locks/blocks on current activity window.
I have a stored procedure which activates a task when ever that sp is called. and this procedure gets out of the loop.
the task get started running.
Is there any way in Sql server that the same stored procedures should wait until the Task finishes and return a code either 1 or 0 for success or faliure of the task.
I have a execute SQL task in which i have a delete statement with global params.
delete from <tname> where (strval=?) and (year=?)
both are varchar type variables.
my strval column gets a value something like "DecData1.0". But when i ran my DTS package it is ignoring this SQL statement. Might because of the special char '.' in "DecData1.0".
How to handle such situation.
I also tried by putting a single quote. delete from <tname> where (strval='?') and (year=?) But it is giving an error.
I have created a DTS package which should process an Incremental Update OLAP Cube ... however it is generating the following error message ... HELP has anyone seen this before ?
I need to parameterize some values in the data flow so that i can chnage the values directly in parameter file and re run the data flow for new value in the passed in the parameter. This can be easy for other who do not know about the flow of data flow task as to where to change the variable/parameter.
How can this be accomplished. I want the data flow task to refer to this file before it starts executing and pick the appropriate value from the file.
Or is their any better way to accompalish what i want to do here in SSIS.???
I have a problem that I can't find any information on and I bet someone here has hit the wall on this.
I have 2 sql server 2000 servers runnng sql std edition. One has 8.00.2039 SP4 and one has 8.00.818 SP3. The server with SP4 will not let me use a parameter in the DTS SQL task. I get the infamous Access Violation Error. If I replicate the SQL task on the SP3 server I have no problem works great and life is good.
Can anyone tell me if thre is a hotfix for this or something? I've googled this to death and tried various possible fixes with no positive result. Any help would be greatly appreciated!!
Parameter Mapping Variable Name Direction Data Type Parameter Name User::Variable2 Input LONG 0
When executing the SSIS Package I get the Following Error
SSIS package "Test.dtsx" starting.
Error: 0xC002F210 at Update Company ID and Name, Execute SQL Task: Executing the query "Update NewFile
Set CompanyID = 'S',
CompanyName = 'SA',
CustomerName = 'SA TEST',
CustomerCode = ?
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am trying to create an Execute SQL task that sets a variable.
This is my SQL
DECLARE @Period AS DATETIME
SET @Period =Parameter0 + '/01/' + Parameter1 SET @Period = DATEADD(m, -1, @Period)
SELECT DATEADD(s, -1, @Period)
This statement parses okay.
I mapped two variables called "User::PeriodMonth" and "User::PeriodYear" in the Parameter Mapping tab to the parameters.
In the Result Set tabl I have mapped a variable "User::PeriodStartDate" to Result Name "PeriodStartDate".
The error I get is the following:
[Execute SQL Task] Error: Executing the query "DECLARE @Period AS DATETIME SET @Period =Parameter0 + '/01/' + Parameter1 SET @Period = DATEADD(m, -1, @Period) SELECT DATEADD(s, -1, @Period) " failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The Online Books are not helpful. They just say you have to bind the parameters to the application variables.
What am I doing wrong?
I want to set another variable called "PeriodEndDate" also. Can both variables be set in the same task?
(1) I know how to use the ? ? ? and 0, 1, 2 notation in Parameter Mapping within Execute SQL Task. However, the interface allows me to give descriptive names to my parameters (other than the ordinals 0, 1, 2, ...). To be more clear, if you go into Parameter Mapping and click in Parameter Name column, you are not just restricted to typing in 0, 1, 2, ... You can type anything you want for the name. Does this suggest that I can use other things besides a "?" in my SQL command?
(2) What is Parameter Size? Is this like a data type? If so, why am I allowed to type in anything I want in there?
I am trying to assign the same package variable value to three different parameters in a query. The variable contains the name of a database which the user will input during package execution. First I check to see if the database exists (if it does I drop it), then in either case I create the database. See code:
if exists
(
select name
from sys.databases
where name = ?
)
begin
drop database ?;
end;
go
create database ?;
go
This is the error I am getting:
[Execute SQL Task] Error: Executing the query "if exists ( select name from sys.databases where name = ? ) begin drop database ?; end; " failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
My "User::DestinationDatabase" variable is mapped to 0,1,2 using an OLE DB connection. Any suggestions would be welcome.
I am trying to exectue SQL task as below by passing a parameter
If I try....
@v1 datetime
set @v1 = convert(datetime, ? ,103)
it fails with below error
" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
however the below code works well
delete from t1 where last_update = convert(datetime, ? ,103)
Is it not possible to have table name as a parameter? For example have the SQL something like:
Delete From ? Where ID = ?
.. I get error:
[Execute SQL Task] Error: Executing the query "Delete From ? Where ID = ?" failed with the following error: "Must declare the table variable "@P1".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have a Script task which generates a GUID and stores it in the variable @[User::AttachmentListId], which is of type System.Object since Guid wasn't an option. Following this is an Execute SQL task (with an OLE DB connection to an SQL Server 2000 database) whose SQL statement is
EXEC AddAttachmentListItem ?, ?, 0
My parameter mapping looks like this (variable name, direction, data type, parameter name, parameter size):
@[User::AttachmentListId], Input, GUID, 0, -1
@[User::AFilePath], Input, VARCHAR, 1, 260
When I execute my package I get the following error:
[Execute SQL Task] Error: Executing the query "EXEC AddAttachmentListItem ?, ?, 0" failed with the following error: "The type is not supported.DBTYPE_GUID". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This worked earlier when the first parameter was an int and not a uniqueidentifier, but I reworked my design because the GUID was a better choice for what I was doing. Well, at least 'til I got to this point....
Hi, I have declared an internal paramter and given the default non queried value as
select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end
and when i run it i am getting the error -- The property 'DefaultValue' of report parameter split doesnt have the expected type. What should i do to solve it. Any help is appreciated.
am trying to do something which I thought would be simple to do in SSIS, several hours am still struggling with it. Not sure if this a bug or a restriction of the product. Or if im hitting some kind of compatability issue because im trying to get to a Oracle database.
Have a sql task which passes in a parameter, I then query my Oracle database and am trying the result (single row) into another variable.
Variable:
Variable Name = Subsystem
Scope= Package
Value = pgc
Data Type = string
SQL:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?
Have also tried:
SELECT SUBSYSTEM_DS AS SUBSYSTEM_DS FROM SYS_SUBSYSTEM WHERE SUBSYSTEM_ID = ?0
Result Set = Single Row
Parameter Mapping:
VariableName = User:ubsystem
Direction = Input
Data Type=Varchar
Parameter Name= 0
Parameter Size= -1 (have also tried 3 - length of variable)
Oracle Table:
SQL> desc sys_subsystem Name Null? Type ----------------------------------------- -------- ---------------------------- SUBSYSTEM_ID NOT NULL CHAR(3) SUBSYSTEM_DS NOT NULL VARCHAR2(40)
....
....
...
The Error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "SubsystemName": "Single Row result set is specified, but no rows were returned.".
I have another SQL Task that performs an update on this same table and I also pass in the same variable but it works?
SQL:
UPDATE sys_subsystem SET as_process_fg = 'X' WHERE subsystem_id = ?0
In temp table there rae data which start with 1 and 2.I want to select only those record which start with 1 Zone is a parameter to the Execute sql task in ssis package..I have created sample code to test when I am running my query I am not getting anything
create table #temp ( zoneid bigint ) insert into #temp values(100000000000000000) insert into #temp values(100000000000000000) insert into #temp values(100000000000000000) insert into #temp values(100000000000000000) insert into #temp values(200000000000000000) insert into #temp values(200000000000000000)
I have a SSIS Execute SQL Task that calls a stored procedure with a date parameter. The text of the stored procedure is an "INSERT INTO .. SELECT ..." statement. When I run the text in Query Analyzer, it completes successfully. When I call the Stored Procedure, it executes but does not insert the data. Setting ByPass Prepare to True does not affect the outcome. I also used the query directly in the SQL task itself to no avail. Executing the query in Query Analyzer works. Any assistance would be greatly appreciated.
64 bit SQL 2005 running on Windows Server 2003 X64
I have an exececute SQL task (in the control flow obviously)
SELECT MAX(last_update) AS OrdersLastUpdateFROM orders
This task executes successfully and I can see that my user variable called "User:tmOrdersLastUpdate" populates correctly in the "variables" pane.. ALL GOOD.
The next step of the Control flow is a dataflow task
Details
DataFlow Source = DataReader Source (MySQL .NET connector) DataFlow Dest = local SQL Server OLE DB.
In the DataFlow Source the DataReader SQLCommand property is Select * from orders where last_update >= @User:tmOrdersLastUpdate
I've tried every conceivable permutation and I can't get SSIS to itnerpret the variable as such...it always gets passed to the server as a literal.
How do I pass a user-defined global variable to the WHERE clause in a DataRader object?
As part of the logging process for data input, I want to update two fields in a logging table. The first is a datetime, derived from looking up the maximum value in another table (the table I've just imported), and the second is an integer - the number of rows captured in a variable during the task.
I can do this in two separate Execute SQL tasks as follows:
Task 1 syntax
DECLARE @maxDate datetime SELECT @maxDate = max(dtLastChangedDate) FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging SET PreviousFilterValue = CurrentFilterValue, CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue), DateSourceTableLastRead = GetDate(), RowsReturned= -1 WHERE SourceTableName = 'cancel'
Task 2 Syntax, with the variable user::rowsimported mapped to parameter 0
UPDATE dbo.tblLogging SET RowsReturned= ? WHERE SourceTableName = 'cancel'
However I cannot make this work with a single SQL statement such as
DECLARE @maxDate datetime SELECT @maxDate = max(dtLastChangedDate) FROM dbo.tblCancel_RAW
UPDATE dbo.tblLogging SET PreviousFilterValue = CurrentFilterValue, CurrentFilterValue = ISNULL(CAST ( @maxdate as varchar(25)),CurrentFilterValue), DateSourceTableLastRead = GetDate(), RowsReturned= ? WHERE SourceTableName = 'cancel'
because no matter how I try to map the parameter (0,1,2,3,4 etc) the task fails.
Is this behaviour by design, is it a bug, or is there something I've missed?
Hi, this might be a simple one, but I have been stuck on it for days. I am just getting into SSIS and have been muddling through it for the rest of this package but I am stuck on this. I am using this SQL Execute Task to run some lookup queries and then call the sp_send_dbmail stored procedure. I have this placed in a For Each loop container. I am using a SELECT DISTINCT Branch FROM table1 into an Object parameter, and passing that into the loop container. I am then using a Input Parameter into this SQL Execute Task of type String. I have run a Script task right before this step to ensure that the variable is populated and correct. Any assistance would be greatly appreciated!
Here is the SQL Execute Task :
DECLARE @SQL varchar(2400), @emaillist varchar(200), @branchMgrEmail varchar(100), @officeMgrEmail varchar(100), @branchMgrEmpNo varchar(5), @officeMgrEmpNo varchar(5), @subjectline varchar (100), @Today varchar(10), @BranchNumber varchar(2) SET @BranchNumber = ? SET @Today = convert(char(8),getdate(),1) SET @SQL = 'SELECT rtrim(CONVERT(char(10), PostedDate, 101)) AS Posted_Date, CAST(Branch AS CHAR(2)) AS Branch, CAST(Department AS CHAR(2)) AS Department, CAST(InvoiceNumber AS CHAR(7)) AS Invoice_Number FROM onbase.dbo.MHC_IncompleteRepairOrders WHERE Branch = ' + @BranchNumber + 'AND HardCardCount = 0 AND WorkAuthCount = 0 AND QualityControlCount = 0 AND MiscDocsCount = 0' SET @subjectline = @Today + ' - Repair Order Validation Notification for Branch #' + @BranchNumber SET @branchMgrEmpNo = (SELECT branchempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber) SET @officeMgrEmpNo = (SELECT officeempno FROM onbase.dbo.BranchMaster WHERE Branch = @BranchNumber) SET @branchMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @branchMgrEmpNo) SET @officeMgrEmail = (SELECT empemailaddress FROM onbase.dbo.ActiveDirectory WHERE CAST(empno AS integer) = @officeMgrEmpNo) SET @emaillist = @branchMgrEmail + '; ' + @officeMgrEmail BEGIN EXEC msdb.dbo.sp_send_dbmail @recipients = 'email@email.com', --@recipients = @emaillist, --@copy_recipients = 'email@email, @attach_query_result_as_file = 0, @subject = @subjectline, @body = 'This email contains a list of Stuff
This is a copy of the ERROR message that I am receiving. Any suggestions?
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Send email to Branch Mgr and Office Mgr
I have a execute sql task to create and drop logins. I want to create/drop the ASPNET login, but I need to pass the domain using a parameter. So I mapped a parameter:
Variable name: User::serverName
Direction: Input
DataType: Varchar
Parameter Name:0
and the sql is the following:
CREATE LOGIN [?ASPNET] FROM WINDOWS
But I get the error:
Executing the query "CREATE LOGIN [?ASPNET] FROM WINDOWS failed with the following error: "Windows NT user or group '?ASPNET' not found. Check the name again.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I'm trying to do something that should be fairly straightforward, but SSIS seems to be getting confused. I have a stored procedure which takes a timestamp as an input parameter. (NOTE: It's not a DateTime that's being stored as a DBTIMESTAMP, it really is a timestamp in the SQL sense.)
The command should be something like this:
Code Block
EXEC dbo.UpdateSynchTimestamp ? I tried to use my variable to pass the value through Parameter Mapping, but I got an unusual error:
[Execute SQL Task] Error: Executing the query "EXEC dbo.UpdateSynchTimestamp ?" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_DBTIMESTAMP)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is strange for a number of reasons:
1) The DBTIMESTAMP parameter has the Direction set to "Input", so it should not be interpreted as an Output or ReturnValue. 2) The Execute SQL Task has Result Set = "None", so it should not be trying to return anything.
If I change the code to include a value hard-coded it works:
Code Block
EXEC dbo.UpdateSynchTimestamp 0x00000000000013BD It is only when a variable is involved that it breaks.
Finally, here's the Stored Procedure itself:
Code Block
CREATE PROCEDURE [dbo].[UpdateSynchTimestamp] @NewValue TIMESTAMP AS BEGIN SET NOCOUNT ON;
UPDATE ServerSettings SET [Value] = @NewValue WHERE [Key] = 'SynchTimestamp' END Doe anyone have any suggestions as to why this isn't working for me? For the time being, I have a Script Task which constructs the command text and stores it in a variable. I can't even use an Expression because the DBTIMESTAMP is not supported.
