0

Can I parameterise the SqlCommand in a Lookup Transformation when using the Jet engine against a CSV file? Is there another way to work with CSV's and Lookups?

I have a JET OLEDB connection that uses an expression to get the folder location from a variable as follows:

"Data Source=" + @[User::SourceRoot] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"text;HDR=Yes;FMT=Delimited(,)\";"

Then in my SSIS Lookup Transformation I have the following SqlCommand:

SELECT * FROM Users.csv

This works fine, however, I don't want to hard-code "Users.csv". Is there a way to configure this? I've tried setting partial cache, but haven't had any luck using the Advanced screen "Custom query" or using a '?' parameter in the query. (I'm using SQL 2012).

PeterX
  • 2,713
  • 3
  • 32
  • 42

1 Answers1

1

I would create a data flow task that uses a flat file connection manager to read from the CSV and load that to a cache transformation. Then you can use the cache transformation file in the lookup task.

Steve Jones
  • 41
  • 3