Hi to all!
I created a simple package – data flow task, containing OLE DB Source, with Data Access Mode – SQL command and OLE DB Destination.
My SQL command is
update support_incident
set date_closed = rn_edit_date
where status_text = 'Closed' and date_closed is null
DECLARE @.CMonth as datetime
DECLARE @.LMonth as datetime
SET @.CMonth = '10/1/2006'
SET @.LMonth = DateAdd (m, -1, @.CMonth)
select
@.LMonth as Reporting_Date,
(select count(support_incident_id) from support_Incident where rn_create_date >= @.LMonth and rn_create_date < @.CMonth) as Opened,
(select count(support_incident_id) from support_Incident where Date_Closed >= @.LMonth and Date_Closed < @.CMonth) as Closed,
(select count(support_incident_id) from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)) as Backlog,
(select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@.CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null))) as Critical_Backlog,
((select count(support_incident_id) from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)) - (select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@.CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)))) as Operational_Backlog
from support_incident
where support_incident_code = '1000'
The SQL statement is successfully parsed and I can see the columns and numbers in Preview Query Results. The package is executed without errors and warnings with success. In Destination server, the table is created with columns according to query, but the problem is no numbers are written in rows, all values are nulls.
I can’t figure out what is the reason. Can anybody to help me?
Thank you.
First avenue of investigation would be to use a data viewer to see if any data enters the pipeline (and what that data is).
-Jamie
|||
Thank you Jamie for your respond. Created data viewers as you suggested, and saw that at OLE DB Source Output Data Viewer appears : Total rows: 0, buffers:0.
So, although OLE DB Source is "successful'', I need to search another approach of sending the data. May be the reason in SQL statement?
Anybody had such problems?
Thank you.
|||Have you tried wrapping this script code in a stored procedure? I suspect that the nested select statement return columns could be confusing SSIS. It's possible that SSIS could detect the return columns differently when the code is in a stored procedure.I don't know if it will fix the problem or not. However, I have a lot of code like this in stored procedures that I'm calling from SSIS without any problems.
Thanks,
Greg Van Mullem|||Here is another idea. Try putting this command at the top of your script. This command fixes a lot of odd problems in SQL 2000 DTS. Definetly worth a try here too.
SET NOCOUNT ON;
Thanks,
Greg Van Mullem|||
Thank you Greg.
No, I did not try. I do not have much experience with stored procedures, only college projects practicing.
Now I am going to read MS SQL Server 2005 - how to create stored procedures - make this script to run monthly. Actually, I have to create and "automate" Crystal Reports using this monthly information. So, I need to change
SET @.CMonth = '10/1/2006'
in the procedure in such a way it would run monthly.
Thanks once again to all.
|||
Now, I received an Error Message and My OLE DB Source is red. I am afraid, I cannot use this statement. My Error message:
SSIS package "PivSupportmonthly.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC020901C at Data Flow Task, PivSupportIncident [1]: There was an error with output column "Reporting_Date" (625) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task, PivSupportIncident [1]: The "output column "Reporting_Date" (625)" failed because truncation occurred, and the truncation row disposition on "output column "Reporting_Date" (625)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "PivSupportIncident" (1) returned error code 0xC020902A.The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1657)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at PivSupportmonthly: The Execution method succeeded, but the number of errors raised (6) 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 "PivSupportmonthly.dtsx" finished: Failure.
Thanks Greg for your following.
|||"Text was truncated or "
That's your error, right there. Something is getting truncated somewhere. You are trying to push a string value into somthing that isn't big enough for it.
-Jamie
|||
You are right, Jamie. I changed the size of the default VARCHAR and it is successful. But there are no inserting data in the table:
SSIS package "PivSupportmonthly.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1657)" wrote 0 rows.
SSIS package "PivSupportmonthly.dtsx" finished: Success.
Thanks once again.
|||I think the issue might be with your query - although it may execute fine in Query Analyser - in SSIS source query - first statement should be SELECT (even if you have Stored Proc) - and in your case its UPDATE & then SET & finally SELECT|||So try a simple SELECT statement in the query to see if data passes through|||Thanks to all your suggestions, I could run successfully my package. I changed default type VARCHAR to DATETIME and used SET NOCOUNT ON. Thanks once again.
Now, I have to make the package would run monthly. Where can I find information about how to schedule?
Thank you very much.
|||Corres wrote:
Now, I have to make the package would run monthly. Where can I find information about how to schedule?
Use SQL Server's built-in scheduler (aka SQL Server Agent)
-Jamie
|||Thanks Jamie.
No comments:
Post a Comment