SQL job failing but it is successful in BIDS
Hi ALL
i have one Execute SQL task in SSIS package where i am calling below query . It is successful in BIDS but failing in SQL Job . what would be the reason.
DELETE
ODS_Schema.PI_PaymentInterfaceDetails
WHERE
CONVERT(VARCHAR(10),GENERATIONDATE,101)
<
CONVERT(VARCHAR(10),GETDATE()-60,101)
the Variables collection on a container during execution of the package,
and the variable is not there. The variable name may have changed or the variable is not being created.".". End Error Error: 2011-06-22 17:23:02.94 Code: 0xC002F210 Source: EST- Removing Records more
than Retention period days Execute SQL Task Description: Executing the query "DELETE ODS_Schema.PI_PaymentInterfaceDetails WHERE..." failed with the following error: "The statement has been terminated.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Surendra Thota
June 22nd, 2011 7:55pm
Typical reasons that a package will run in BIDS but not SQL Agent are a) permissions and b) 32 v. 64 bit.
Does the SQL Agent account have the proper access to this table? Try creating a simple SQL Agent job that executes the T-SQL statement just as you have it shown. Does the job run?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
June 22nd, 2011 7:59pm
Do you use configurations? Seems like you have a mishap with a variable, perhaps not getting populated.Arthur My Blog
June 22nd, 2011 8:44pm
Why are you converting your dates to strings and then comparing them? Your query will also not use any indexes implemented on the GENERATIONDATE column.
WHERE CAST(GENERATIONDATE AS DATE) < CAST(DATEADD(day,-60,GETDATE()) AS DATE)
Apart from that, your problem is probably to do with permissions as per Todd's post
Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2011 7:52am
From FAQ : http://support.microsoft.com/kb/918760.http://deepaksqlmsbusinessintelligence.blogspot.com/ Happy to help! Thanks. Regards and good Wishes, Deepak.
June 23rd, 2011 8:10am
Hi ALL
It is not a permission Issue .we have source files with Datetime as filename . Here the GenerationDate Column is computed column (
convert(datetime, cDate+'/'+CMonth+'/'+CYear+' '+cHour+':'+cMin+':00.000')) in Table .
The strange thing is the package is working fine (loading data in the table and even in Computed colum also ) in BIDS but failing
in SQL job with the below error .
Error Description : The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
End Error Error: 2011-06-23 10:33:02.37
Code: 0xC002F210 Source: EST - Insert New Records Based on File Name Execute SQL Task
The strange
Regards
Surendra ThotaSurendra Thota
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2011 12:33pm
This is a completely different error message than you previously provided. The problem is simple, your values are not valid datetime values.
SQL Server requires dates in mm/dd/yyyy format but it is best to use yyyy/mm/dd to stop any confusion of what is the day or month value.
EDIT: Sorry, forgot to add that the regional settings for the SQL Server Service Account will be set to US which forces date formats of mm/dd/yyyy.
Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
June 23rd, 2011 12:40pm
The Day value like 09 and Month value is 06 for 9th juneSurendra Thota
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2011 12:52pm
The Day value like 09 and Month value is 06 for 9th june
Surendra Thota
what about 13 June? Your code would format this as 13/06 however in US date format this would represent 6th Day of the 13th month which is invalid. See my post above regarding regional settings of the SQL Agent Service AccountJeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
June 23rd, 2011 12:57pm
There is the table schema and the
GenerationDate column getting value without error .
when we the data . the initial problem got when we use
WHERE CAST(GENERATIONDATE
AS DATE) <
CAST(DATEADD(day,-60,GETDATE())
AS DATE . if we wont use this statement the JOb is executing perfectly .
CREATE
TABLE [ODS_Schema].[PI_PaymentInterfaceDetails](
[FileName] [varchar](100)
NULL,
[ServerName] [varchar](50)
NULL,
[CYear] [char](4)
NULL,
[CMonth] [char](2)
NULL,
[cDate] [char](2)
NULL,
[cHour] [char](2)
NULL,
[cMin] [char](2)
NULL,
[GenerationDate]
as (convert(datetime, cDate+'/'+CMonth+'/'+CYear+'
'+cHour+':'+cMin+':00.000')
),
[CounterId] [smallint]
NULL,
[value] [int]
NULL
)
ON [FS_DATA]Surendra Thota
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2011 12:59pm
There is the table schema and the
GenerationDate column getting value without error .
when we the data . the initial problem got when we use
WHERE CAST(GENERATIONDATE
AS DATE) <
CAST(DATEADD(day,-60,GETDATE())
AS DATE . if we wont use this statement the JOb is executing perfectly .
CREATE
TABLE [ODS_Schema].[PI_PaymentInterfaceDetails](
[FileName] [varchar](100)
NULL,
[ServerName] [varchar](50)
NULL,
[CYear] [char](4)
NULL,
[CMonth] [char](2)
NULL,
[cDate] [char](2)
NULL,
[cHour] [char](2)
NULL,
[cMin] [char](2)
NULL,
[GenerationDate]
as (convert(datetime, cDate+'/'+CMonth+'/'+CYear+'
'+cHour+':'+cMin+':00.000')
),
[CounterId] [smallint]
NULL,
[value] [int]
NULL
)
ON [FS_DATA]
Surendra Thota
As previously advised, that date format will NOT work for US regional settings (which is what the SQL Server Agent Service Account is more than likely using) as it will produce an invalid date. You need to use yyyy/mm/dd or mm/dd/yyyy.
yyyy/mm/dd is better as there will be no confusion about what is the month and what is the day.
The reason why it works in BIDS is because it's running under the context of the user logged into the PC (not the service account) and their date format is probably dd/mm/yyyy.
Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
June 23rd, 2011 1:04pm