CODEWITHSUNDEEP
oledbssisjetssis-2012
PeterX
PeterX

Reputation: 2901

SSIS - Use file name parameter in SQL Lookup Command (JET OLEDB)

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).

Upvotes: 0

Views: 300

Answers (1)

Steve Jones
Steve Jones

Reputation: 41

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.

Upvotes: 1

Related Questions