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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics