DATALENGTH

Question

DATALENGTH

PSB

SSChampion

Points: 13195
More actions
January 25, 2011 at 2:37 pm

#149799

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

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

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 2

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

PSB SSChampion Points: 13195 More actions · Answer 3

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.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.