January 25, 2011 at 2:37 pm
I have a staging table with different number of records each time loaded. But the datalength of the single colom is always same e.g 100.
I need to check this condition. I am using
SELECT RowLength = DATALENGTH(TransRecord), TransRecord
FROM dbo.Staging
If all the rows have Rowlength = 100 then Insert into SQL table else send mail .
How do I do this in ssis.
I am using sql 2008.
January 26, 2011 at 5:12 am
PSB (1/25/2011)
I have a staging table with different number of records each time loaded. But the datalength of the single colom is always same e.g 100.I need to check this condition. I am using
SELECT RowLength = DATALENGTH(TransRecord), TransRecord
FROM dbo.Staging
If all the rows have Rowlength = 100 then Insert into SQL table else send mail .
How do I do this in ssis.
I am using sql 2008.
What is the datatype of the TransRecord column?
To do it in SSIS:
execute all your SQL code in an Execute SQL Statement. Do your check and store it in a variable. Use the variable in a precedence constraint to determine whether you'll insert the rows or send an e-mail.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 26, 2011 at 5:32 am
SELECT RowLength = DATALENGTH(TransRecord), TransRecord
FROM dbo.Staging
If all the rows have Rowlength = 100 then Insert into SQL table else send mail .
How do I do this in ssis.
I am using sql 2008.
Create a variable, with datatype boolean or int.. and change your query to ... and depending on the value of the count that you get, update the value of the variable to 0 or 1... and as quoted earlier... use the precidence constraints(setup and expression) to manage it...
SELECT COUNT(*) FROM dbo.Staging WHERE DATALENGTH(TransRecord) > 100
Sriram
January 26, 2011 at 8:40 am
Thanks All ! It worked.
I used this SP
ALTER PROCEDURE [dbo].[usp_PreInsertRowCountStaging]
AS
BEGIN
DECLARE @RowCount INT
SET @RowCount = ( SELECT COUNT(*) FROM dbo.Staging WHERE DATALENGTH(TransRecord) != 100 )
SELECT @RowCount AS [RowCount]
END
And then mapped it to a SSIS variable and used precedence constraint.
January 26, 2011 at 8:54 am
I'm with Koen. There was no need for an SP - running the count query and assigning the result to an SSIS variable can all be done in a single Execute SQL task.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply