SQL Task with String Paramters doesn’t create data

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  September 22, 2009 at 10:03 am

  #156243

  Hi

  I Execute a SQL Task in Integration Services. The SQL Task contains a Stored Procedure which contains string parameters:

  @AC_YR

  @AC_YR_T1

  @AC_YR_T2

  @AC_YR_T3

  @AC_YR_T4

  @AC_YR_T5

  @AC_YR_T6

  I’m using an ADO Net Connection Type. In Parameter Mapping I have under variable Name For example USER:AC_YR_T1. Direction Input. Data Type String. Parameter Name @AC_YR_T1 and Size -1.

  In the Variable properties I have added the Value as ‘T1 Autumn’ Value Type String. Everything seems to look OK but the parameters aren’t used and everything gets deleted from the table leaving me with NULL records.

  Running the same thing in SQL Server as

  EXEC dbo.usp_FACT_SA_SESSIONS_AND_TOTALS_COUNT_e

  'T1 Autumn','T2 Autumn','T3 Spring','T4 Spring','T5 Summer','T6 Summer','AC 08-09'

  Does return data to the table as it should. I cant figure out why no data is being returned?

  Any ideas at all. I have asked a few people and they cant figure it out.

  Thanks

  Debbie

• Fausto Echevarria

  SSC Eights!

  Points: 921

  More actions

  September 22, 2009 at 10:33 am

  #1056945

  why are you using size = -1?

  just dont specify the size value? i think that would work

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  September 23, 2009 at 3:16 am

  #1057353

  Hi,

  the default value is (-1) which means that the correct size is guessed at.http://blogs.msdn.com/mattm/archive/2007/02/27/sql-server-sp2-what-s-new-for-ssis.aspx

  I will certainly have a go at putting another size in to see what happens though

  Debbie

• Debbie Edwards

  SSCrazy Eights

  Points: 9217

  More actions

  September 23, 2009 at 4:14 am

  #1057387

  I think it was because in value I had added ''. E.g.'Term 1' rather than just putting Term 1. Fingers crossed its working fine now.

  Debbie