Error In Variable Mapping In Execute SQL Task
Apr 17, 2006
Hi,
I am getting an error message (mentioned below) in the variable mapping of Execute SQL Task in SSIS.
" Error: ForEach Variable Mapping number 9 to variable "User::Value" cannot be applied. "
" Error: The type of the value being assigned to variable "User::Value" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. "
Pls anyone have a look and give me a solution asap.
Thanks & Regards,
Prakash Srinivasan.
View 4 Replies
ADVERTISEMENT
Jul 23, 2007
Hi all,
I'm working with Execute SQL task. Connection type: OLE DB. With the following settings, the task works fine:
Parameter mapping:
Code Snippet
Variable Name Direction Data type Parameter Name
User::InputFile Input NVARCHAR 0
User::DesiredOutput Input NVARCHAR 1
SQLStatement:
Code Snippet
exec [spu_CreateOutput] ?, ?
However, I want to put in some conditions so I modified the task as:
Parameter mapping:
Code Snippet
Variable Name Direction Data type Parameter Name
User::OutputFile Input NVARCHAR 0
User::InputFile Input NVARCHAR 1
User::DesiredOutput Input NVARCHAR 2
SQLStatement:
Code Snippet
if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
End
But the modification doesn't work. The following message was thrown:
Error: 0xC002F210 at CREATE OUTPUT, Execute SQL Task: Executing the query "if(? <> 'NotUsed')
Begin
exec [spu_CreateOutput] ?, ?
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.
Task failed: CREATE OUTPUT
Can anyone tell me what I have been wrong with it? It seems that parameter mapping can only apply for a single select statement/function/procedure call :-?
Thank you,
GiaHi
View 5 Replies
View Related
Jun 20, 2007
hello
I have a problem with Sql task
when sql task tried to assing a value to my variable I have this error ""La valeur n'est pas comprise dans la plage attendue."
I'm using ODBC connexion for a csv file
someone can help me ?
thanks
View 4 Replies
View Related
Nov 8, 2006
hi chaps
i m getting the following ERROR:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "JDETimezone": "Unable to find column Timezone in the result set.".
i know what the problem is i.e. no row is returned then what is the problem
here you are.... i want to it work... strange... ok i explain...
actully i have some processign to do with variable JDETimezone even no row is returned.... can u tell me the alternative to do the follwing task...
I want to retrieve a record from some table and do some processing and if no row is present or returned then i want to do seperate processing.... can ne one help me out ?
regards,
Anas
View 4 Replies
View Related
Apr 17, 2008
I have 2 questions on this
(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?
View 3 Replies
View Related
Dec 13, 2007
Hi All,
I am using a stored procedure defined as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetPriority] @PriorityID TINYINT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT [Priority]
FROM [MTD Dashboard].[dbo].[Priority] WHERE [Priority ID]=@PriorityID
END
I want to use this stored procedure in a Execute SQL Task. What should be the SQL Statement, Parameter mappings and Result Set?
Can someone please help me in doing this.
Thanks
View 5 Replies
View Related
Mar 9, 2006
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.
Regards,
DO
View 13 Replies
View Related
Jul 12, 2006
Hi, I am trying to use an integer as input parameter for my task I get suck on the parameter data type.
The input parameter is define as @Control_ID variable as Int32 in SSIS. When I got into the parameter mapping of Execute SQL Task, I don't find the Int32 data type. I used to try Short, Numeric, Decimal and so on, but all of those data type didn't work. and it returns the following error message:
SSIS package "DCLoading.dtsx" starting.
Error: 0xC002F210 at Update Control_ID, Execute SQL Task: Executing the query "use DCAStaging
update DCA_HFStaging set
[dbo].[Control_ID] = P0 where [Control_ID] is null
" failed with the following error: "The multi-part identifier "dbo.Control_ID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Update Control_ID
Warning: 0x80019002 at DCLoading: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DCLoading.dtsx" finished: Failure.
Any help?
View 6 Replies
View Related
Dec 17, 2007
Hi ALL,
I have a Execute SQL Task to execute a stored procedure. It has no input and output parameters.
The stored procedure is defined as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[SetSLATimePriority]
AS
BEGIN
DECLARE @PriorityID tinyint,@MAXPriorityID tinyint
DECLARE @Priority NVARCHAR(MAX), @SLATime int
SET @PriorityID=1
SET @MAXPriorityID=0
SELECT @MAXPriorityID=MAX([Priority ID]) FROM [MTD Dashboard].[dbo].[Priority]
SET NOCOUNT ON;
WHILE @PriorityID<=@MAXPriorityID
BEGIN
SELECT @Priority= [Priority] FROM [MTD Dashboard].[dbo].[Priority]
WHERE [Priority ID]=@PriorityID
SELECT @SLATime= [SLA Time in hours] FROM [MTD Dashboard].[dbo].[Priority]
WHERE [Priority ID]=@PriorityID
UPDATE [MTD Dashboard].[dbo].[Remedy Dump-Filtered]
SET [SLA Time] = @SLATime WHERE [Priority] like @Priority
SET @PriorityID=@PriorityID+1
END
END
The Properties of Execute SQL Task are set as follows:
Result Set: None
Connection Type: OLEDB
SQL Source Type: Direct Input
SQL Statement: EXEC ? = [dbo].[SetSLATimePriority]
IsQueryStoredProcedure: True
ByPassPrepare: False
Parameter Mapping:
Variable Name : User::IntValue
Direction: ReturnValue
Data Type: Long
ParameterName: 0
I am getting the following error, when I run this package.
[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am not able to figure out, where exactly the problem is.. Can some one please help me out?
View 3 Replies
View Related
Dec 31, 2007
Good day everyone,
I have a question concerning the Execute SQL task.
Problem Statement:
I have a table containing the following sample data.
Catalog_ID, Product_ID, IsBlocked
56789, 1, 0
56789, 3, 1
23567, 1, 0
23567, 2, 1
The data types of the fields are:
Catalog_ID: bigint
Product_ID: bigint
IsBlocked: bit
I have two variables called "old_catalog_id" and "new_catalog_id" with the following values:
old_catalog_id: 56789
new_catalog_id: 11111
Now, I would like to select all the recods, whose Catalog_ID fields equals the value in the variable "old_catalog_id" and insert identical recods with the "new_catalog_id" value.
The result of that operation on my sample records is:
Catalog_ID, Product_ID, IsBlocked
56789, 1, 0
56789, 3, 1
23567, 1, 0
23567, 2, 1
11111, 1, 0
11111, 3, 1
blue: selected fields
green: inserted fields
My Solution:
In order to realize this solution, I have created the following tasks on the Control Flow.
1. Create an Execute SQL Task for the selection. (Name: Selection Task)
2. Create a For-Each-Loop for iterating on the result set. (Name: Loop on results Container)
3. Create an Execute SQL Task inside the For-Each-Loop container for inserting the new records. (Name: Insertion Task)
Configurations and Problems:
1. Selection Task:
General Tab:
Result Set: Full Result Set
SQL Statement:
select Product_ID, IsBlocked
from MyTable
where Catalog_ID = ?
Parameter Mapping Tab:
Variable Name -- Direction -- Data Type -- Parameter Name
User:: old_catalog_version -- Input -- Large_Integer -- 0
Result Set Tab:
Result Name -- Varibale Name
0 -- User::FullResultSet (which has the Data Type: Object)
When I execute this task, I get no records.
Thus, I have hard-coded the value of Catalog_ID in the Sql Statement parameter. Now, I get the correct 2 records in the result set.
Question 1: What am I doing wrong in the Parameter Mapping?
2. Loop on results Container:
Collection Tab:
Enumerator: Foreach ADO Enumerator
ADO object source variable: User::FullResultSet
Enumeration mode: Rows in the first table
Variable Mappings Tab:
Variable -- Index
User:: old_prod_id -- 0 (Data Type of "old_prod_id" is Int64)
User:: old_isBlocked -- 1 (Data Type of "old_isBlocked" is Boolean)
When I execute the package, it fails with the following error message:
Error: 0xC001F009 at SQL Tasks: The type of the value being assigned to variable "User:: prod_id" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Thus, I have changed the data type of the variable "old_prod_id" to Object. Now, the package runs successfully.
Question 2: Why isn't the package accepting "Int64" as the data type of my variable, although the corresponding DB field has the type "bigint"?
3. Insertion Task:
General Tab:
Result Set: None
SQL Statement:
insert into MyTable
(Catalog_ID, Product_ID, IsBlocked)
values (?, ?, ?)
Parameter Mapping Tab:
Variable Name -- Direction -- Data Type -- Parameter Name
User::new_catalog_version -- Input -- Large_Integer -- 0
User:: old_prod_id -- Input -- Large_Integer -- 1
User:: old_isBlocked -- Input -- Variant_Bool -- 2
When I execute this task, it fails with the following error message:
Error: 0x0 at Insertion Task: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Catalog2Context_CATALOGS". The conflict occurred in database "XS_EC_CCH_PEGXSAP2", table "eSearch4.CATALOGS", column 'ID'.
Well, the message implicitly states that the value of the variable "new_catalog_version" has violated the mentioned FOREIGN KEY constraint. But I have set a breakpoint and I saw that the value was set correctly. So, it seems that the Execute SQL Task is not able to read the value of the variable correctly.
Question 3: What am I doing wrong in the Parameter Mapping, which causes the task not being able to read the value of the variables correctly?
Thanks in advance for your help.
Regards,
Samar
View 3 Replies
View Related
Apr 19, 2007
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
View 5 Replies
View Related
Jan 3, 2007
Hi,
I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:
DECLARE @my_variable int
, <several_others>
SET @my_variable = ?
<do_some_stuff>
Of course, I also set up the parameter mapping.
However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.
I have several reasons for wanting to do this:
- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.
- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.
- Passing the variable once also makes it easier to design the script outside IS using Management Studio.
- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.
- I prefer to use the OLE DB connection manager because it's a de facto standard here.
Could anyone help me out with the following questions:
- Is the above possible?
- If so, how?
- If not, why not?
- If not, what would be the best way around this problem?
Thanx in advance,
Pipo
View 6 Replies
View Related
Feb 14, 2007
I have a variable SYear with the data type Integer and the value 2005.
Connection type is OLE DB, and my sql statement is:
DECLARE @MyYear Int
SET @MyYear = ?
I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @MyYear, it is fine. What is wrong in here? Somebody help me!
View 17 Replies
View Related
May 6, 2008
Hi,
How can I delete records from one table using date variable condition?
or should I use string?
here is the sql : DELETE FROM TABLE WHERE DATE = @VARIABLE
View 1 Replies
View Related
Aug 22, 2006
Hi!
I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.
How do I get the variable in my C# code?
Thanks
Carl
View 19 Replies
View Related
Mar 12, 2008
Hi
I need to use a variable as column in SQL statement in Execute SQL task of integration services. I am setting Parameter Setting to map variable use it in the query like; select ? , col1name from tablename. But its not working.
Anybody having any idea; would be of great help.
Thanks,
Salman Shehbaz.
View 3 Replies
View Related
Sep 7, 2006
Hi everyone,
How to define a Input variable in a Execute Sql Task??
I've defined a User::Inicio variable which contains 4 as value.
In Parameter Mappins it has been defined. Then, I've gone to General->Sql Statement and allocated the following SQL Statement:
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [Inicio]
or
UPDATE CARGAPROCESOS SET FECHAULTIMACARGA = [User::Inicio]
Anyway, I'm stuck, both did not work
Thanks in advance for your comments
View 7 Replies
View Related
Jan 25, 2007
Can I retrieve a result set from a sp into a variable within a Execute SQL Task?
View 23 Replies
View Related
Jun 13, 2007
Hi,
Let's say that the query in my SQL Task returns a single integer number.
How can I put that single number in a variable?
Thank you.
View 3 Replies
View Related
Oct 4, 2006
Please help!
I am designing an SSIS package and need to change the value of a DateTime global variable with the value returned from an Execute SQL task. The queary is as follows:
Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'
The error message is as follows:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at VersionDate_Set, Execute SQL Task: Executing the query "Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'" failed with the following error: "The type of the value being assigned to variable "User::VersionDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: VersionDate_Set
SSIS package "Package.dtsx" finished: Success.
The variable is set to data type DateTime, but the value will not change. Any ideas?
View 1 Replies
View Related
Jan 31, 2007
Hello,
I've asked this question before and I've read the answers before and I still cannot get it to work. My task is simple, I want to use the execute sql task container to grab a value from a database and put it in a variable. I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.
This is my sql command:
select output_location as output_location
from script_master
Result Set is set up like this:
Result Name: output_location ; Variable Name: User::output_location
Here is the error I get:
Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "output_location": "The type of the value being assigned to variable "User::output_location" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
".
I don't know what I'm doing wrong, I've followed all the instructions exactly on how to populate a variable in this container. My variable is set up as a string, if I change it to object I can get it to work. I think this is because the object is allowing nulls. I really believe that the variable is not populating and that is why I'm getting errors.
Please help. If you could provide step by step example's that would really make my day.
Thanks,
Phil
View 15 Replies
View Related
Dec 6, 2006
Dear all:
I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :
Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".
So this confused me, any one has any experience on this?
Many thanks,
Tomorrow
View 5 Replies
View Related
Nov 5, 2007
I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated!
SELECT count(*) FROM hsi.itemdata a
JOIN hsi.keyitem105 b on a.itemnum = b.itemnum
JOIN hsi.keyitem106 c on a.itemnum = c.itemnum
JOIN hsi.keyitem108 d on a.itemnum = d.itemnum
WHERE a.itemtypegroupnum = 102
AND a.itemtypenum = 108
AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + "
AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + "
AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")
View 6 Replies
View Related
Feb 13, 2008
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
View 8 Replies
View Related
Oct 23, 2014
I'm using a DateTime variable in SSIS 2008 that is used to set the SQLStatement property of an Execute SQL Task.
"DELETE FROM Labor WHERE Week = '" + (DT_WSTR, 100) @[User::Week] + "'"
Week is the next Sunday:
DATEADD( "day", @[User::DaysTillSunday] , @[User::TheDayThatIsTwentyMinutesPrior] )
DaysTillSunday:
DATEPART( "dw", @[User::TheDayThatIsTwentyMinutesPrior] ) == 1 ? 0 : 8 - DATEPART( "dw", @[User::TheDayThatIsTwentyMinutesPrior] )
TheDayThatIsTwentyMinutesPrior:
(DT_DATE)(DT_DBDATE)DATEADD("minute",-20,GETDATE())
The SSIS Package deletes the current week's data, reloads it with fresh data, then calculates the difference between the current week and last week.
The problem is that randomly, instead of deleting the current week, it will delete the previous week. This happens maybe 5-10% of the time. At least it does until I rebuild the package and import it into SQL Server again.
I'm guessing that the Execute SQL Task is not updating the value of the Week variable before it executes. I started with the source type being a variable. Then I decided to try Direct input and pass in the Week as a parameter (OLE DB Connection Type). That didn't work either.
Most recently I tried writing the Week variable to a table first, then having a sequence container with all the tasks second. Slightly better but I still saw the date was wrong 2 times in about 90 executions. I was hoping that writing the Week variable out to the database would force an update of any associated connections to it, but that didn't seem to work.
View 18 Replies
View Related
May 29, 2007
I'm trying to use "findstr.exe" to extract some lines of interest from a data file, which I will later load to a table. I'd like to issue this form of a command:
findstr.exe "^SEARCHSTRING" "srcfile" > "dstfile"
I build the arguments using expressions, and both the search string and source file get correctly set. However, the ">" seems to be ignored--I can see the lines spitting out to the temporary window when I run under VS.
QUESTION: how do you redirect the output of a command run under an Execute Process Task?
View 3 Replies
View Related
Dec 12, 2005
There are two options to specify the subpackage location (SQL Server or file location). I'd like to know how I can specify a variable name that points to the file location so I avoid hard coding the file location which could change during production installation.
View 4 Replies
View Related
Mar 29, 2007
I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:
1) In the First Execute SQL Task, I want to store a single row result of @@identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )
2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).
Please give me solutions for the above two..
View 6 Replies
View Related
Nov 18, 2015
In my ssis 2012 package, I have a 'object' type variable with some table like records. I want to do some SQL operations like insert/update on the records in another table based on this 'Object' type variable records. Basically I want to use a MERGE statement with another physical table with the records in the 'Object' type variable.how to map/use the Object type variable in Execute sql task.I am not good in script task. How to utilize this Object variable in a Execute sql task?
View 9 Replies
View Related
Jan 22, 2008
Hello,
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
Does anyone have a clue as to what is going on??
Mike
View 1 Replies
View Related
Jul 20, 2007
I set up a connection to mysql using ADO.NET's ODBC Data Provider. And I'm running a simple query to return one table's maximum ID(int32 unsigned). There is no problem to achieve that.
But when I bind the result to a variable and excute task. It gives out error message: "An error occurred while assigning a value to variable "MaxAuditLogID": "Result binding by name "MaxID" is not supported for this connection type. "
I tried to change the type of variable around but with no luck.
Could anyone help with this issue? Thanks!
View 6 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Jan 23, 2008
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" 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.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
View 12 Replies
View Related
