April 4, 2019 at 2:54 pm
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
April 4, 2019 at 5:37 pm
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?
April 5, 2019 at 8:17 am
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply