2

While this seems like a basic problem, I've been ripping my hair out FOR DAYS trying to get an efficient solution to this.

I have a lookup table of values on a server that I read from and assemble into a string using a C# Script task. I write this string into a variable that I want to pass in as my WHERE parameters inside a large SQL query on a ADO.NET data source (from a different server which I only have read access to) in my data flow. For example, this string would just be something like

  ('Frank', 'John', 'Markus', 'Tom')

and I would append that as my WHERE clause. I can't read from a variable directly for an ADO.NET data source AND I can't use the 'Expression' property to set my SQL either as my SQL query is over 4000 characters. I could use an Execute SQL Task to run my query, load the results into a recordset and I assume, then loop through the recordset but that's extremely inefficient.

What would be the best way to do this? My end goal is to put these results inside a table on the first server.

Improve this question
6
  • Insert your parameter values into a table, then write your query to pull its parameter values from that table. Commented Oct 22, 2012 at 22:48
  • I assemble my parameters from a table on a server that I have admin access to. The problem is that I need to execute the query on another server (that I only have read access to) using these parameters. Or are you suggesting that I make the script task inside the data flow? Commented Oct 22, 2012 at 22:55
  • Nope, what you thought is what I meant. Commented Oct 22, 2012 at 23:01
  • I hope I'm not misreading this, but how would I pass the result of that script task to my ADO.net data source query then? Commented Oct 22, 2012 at 23:12
  • can you ask admin of that server to prepare view using this long query which will shorten your query to select * from viewName where col in ('Frank', 'John', 'Markus', 'Tom')? Commented Oct 22, 2012 at 23:14

2 Answers 2

Reset to default
2

You could try to set up Script Component as source - variables and strings inside scripts can be longer than 4000 characters so you can fit your query inside.

Setup your component similar to this article: http://beyondrelational.com/modules/2/blogs/106/posts/11119/script-componentsource-part1.aspx In this one you have example how to fetch data using ExecuteReader and put it to output of script component: http://beyondrelational.com/modules/2/blogs/106/posts/11124/ssis-script-component-as-source-adonet.aspx In this one you have instructions how to aquire connection properly: http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2011/05/30/use-connections-properly-in-an-ssis-script-task

By joining this pieces of information you should be able to write your source Script Component which can fetch data using any length dynamically constructed query.

Good luck :)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks! I never thought of using a Script Component as a source, now I finally have a way to programmatically make my SSIS queries :D
0

You can do a simple select statement to return a list of values that will include ('Frank', 'John', 'Markus', 'Tom'). So your select would return :

Name
----------
Frank
John
Markus
Tom

Then, in SSIS, use a Merge Join Component (that will act as a INNER JOIN) instead of a where clause in your main query.

This is the cleaniest way to achieve what you want.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.