Delay validation of a variable

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  April 4, 2019 at 2:54 pm

  #2780741

  Hi, I have a package that takes a value from a text file title and store it in a variable. The variable then gets passed along to a data flow task and ultimately is inserted into a table as part of a record.

  I use a string variable to provide the dynamic SQL for my data flow as follows:

  "select distinct trip, company, customer_ID, " + @[User::TMC_Ident_From_Filename] + " as [Ident] from [200_CGDS_VALIDATION].[dbo].[Vali_Data]"

  however the variable doesn't get populated until runtime so when I try to change the data source to SQL command from variable and then select this variable, I get the error that the sql is not well formed as the variable value is missing.

  How would I go about delaying the valiadation of the variable until runtime?

  I can't see a validate external metadata or a delay validation option in the variable properties.

  Many thanks,

  Dave

• palandri

  Old Hand

  Points: 387

  More actions

  April 4, 2019 at 5:37 pm

  #2793561

  Could you use a lookup, and craft the query of [200_CGDS_VALIDATION].[dbo].[Vali_Data] such that you could use @[User::TMC_Ident_From_Filename] as the input column?

• david_h_edmonds

  Default port

  Points: 1430

  More actions

  April 5, 2019 at 8:17 am

  #2848060

  Hi, thanks for this. I have set up a #tmp table that has 1 row 1 column and as an initial step I write the variable value to that table then join into it when I need the value later on. Final step in the loop is to drop/truncate the table so that the next files value replaces it.

  Cheers

  Dave