Flat file variable -- NOT file name.
Greetings. SQL2008R2. This is not a current requirement I have. I was designing a similar IS Package and this question suddenly popped into my head, as I can see it being a future requirement for me. From there I started experimenting, and got stuck. For a setup that most can follow, I exported the customerID and territoryID columns from adventureWorks2008R2.sales.customer into a flat file. So lets say I want to match all records on customerID between the file and the sales.customer table on the customerID column, and return the sales table accountNumber for an ultimate output. Of course this will match on every row as the table and file have the same data, but thats OK for this experiment. To get this far I created a Data Flow Task. In that, I have Flat File data source, a Derived Column Task to convert the Flat File customerID column to INT, and a Lookup Transformation that joins the (converted to INT) customerID column in the Flat File to the sales.customer.customerID column, and then outputs the accountNumber. Easy enough. Finally, my question.... What if I only want to return the accountNumber where the Flat File territoryID value is 6 - 10 (or whatever specific value)? Yes, I know that value exists in the table too, but pretend it exists only in the file, and not in the table. How would you supply an input variable to be able to handle this? TIA, ChrisRDBA
April 26th, 2011 12:22pm

So your Input flat file has TerritoryID all over the range, and you want your OUTPUT dataset to be filtered to only include certain TerritoryID's? Did I get that right? If so, then I would suggest using a Conditional Split transform in the Data Flow between the input and output. Set the Expression to be "TerritoryID >= 6 && TerritoryID <= 10"Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
April 26th, 2011 12:33pm

1) You could also use a derived transformation for the flat file output and use the expression TerritoryID >= MinValue && TerritoryID <= MaxValue which extracts or creates a new column(territoryID) which has only the required values.Please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.
April 26th, 2011 12:40pm

Great ideas guys, thanks!TIA, ChrisRDBA
Free Windows Admin Tool Kit Click here and download it now
April 26th, 2011 3:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics