Setting an SSIS user variable

Question

Setting an SSIS user variable

sharon.swats

Old Hand

Points: 303
More actions
September 16, 2008 at 6:44 am

#125471

The SQL code below is in the SQL command text section of OLE DB Source:
Declare @NoRowsFound varchar(50)
SET NOCOUNT ON
select * from STS_FIle
where ssn_id = '99999999'
IF @@rowcount = 0
SET @NoRowsFound = '_NoRowsFound_'
Else
SET @NoRowsFound = '_'
I want to create a SSIS user defined variable and set it with the value of @NoRowsFound to use in title of text file created.
Is there a simple way to do this without creating a Script Task or EXECUTE SQL TASK.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you explain your reason for needing this? I think the designed way for SSIS to handle this is to use the Row Count transform.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sharon.swats Old Hand Points: 303 More actions · Answer 2

I wanted to use SSIS variable in file name to let user know that no records where returned without opening the file. Example:

STSfile_09152008_noRecordsFound.txt or maybe also

STSfile_09152008_450RecordsFound.txt

Thank you,

Sharon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I think the Row Count Transform is exactly what you are looking for.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sharon.swats Old Hand Points: 303 More actions · Answer 4

Hi.

I have created a variable called RowCount - at the package level,Int32,Value = 0.

I have created a Row Count task between my OLE DB Source and Flat File Destination. In VariableName property of Row Count Task, I have my user defined variable: RowCount.

In my Flat Fle Connection Property, ConnectionString, I have:

@[User::filePath]+(Dt_STR,4,1252)@[User::RowCount]+"RowsFound.txt"

1235460 rows are pulled into my text file correctly, but my file name is:

R:\Sharon\0RowsFound.txt INCORRECTLY instead of 1235460RowsFound.txt

What am I missing?

Thank you for you help.

Sharon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

(Dt_STR,4,1252) does not define a string large enough to hold the results you are returning. You need at least (Dt_STR,7,1252)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sharon.swats Old Hand Points: 303 More actions · Answer 6

I tried the (DT_STR,7,1252) it did not work.

I rerun the SQL using top 8, still 0RowsFound.txt

It seems that the row count task is not setting the variable.

I find SSIS to be a VERY frustrating tool!!!!!!!

Thank you for your help.

Sharon

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Apparently you cannot use a variable set in the Row Count transformation in the same Data Flow. Check out the discussion on this blog post:

http://blogs.conchango.com/jamiethomson/archive/2005/05/29/SSIS_3A00_-Using-the-Rowcount-component.aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

sharon.swats Old Hand Points: 303 More actions · Answer 8

Hi,

I had read that article but I thought that because I had the Row Count Task after the

OLE DB Source it would work. IT MUST BE AFTER THE COMPLETE DATA FLOW TASK!!!!

I will keep working on it.

Thank you again,

Sharon