0

A newbie to SSIS here, so if you are able to help.. please add as much detail to your answer as possible :)

My goal is to be able to parameterise my ado.net source based of values already in a table in my destination DB.

I've managed to test the functionality of the first part of this using a hard-coded value in my variable but i'm obviously going to need this flow to be dynamic (based on what existing in my lookup table).

Here is some detail of the steps i have taken so far.

  • I have a dataflow task in my a package, within this i have the ado net source connected to a lookup no match to a ole db destination.
  • I have set up a package level variable (with the hardcoded value)

  • After clicking on the dataflow task, i have placed my source query in the ADO NET Source.SqlCommand expression with the variable in the where clause. As like below.

    "SELECT
 columm1
,columm2
,columm3
,columm4

FROM
table_a

WHERE
columm1 in ='"+ @[User::varibable1] +"'

ORDER BY 
columm3
,columm4"

I run the package and only the records with the value name in my variable are returned. Perfect! Just what I need.

Now the second part... I need my ado.net source to receive the values from another query that should run prior. So somehow I need maybe another task that passes the values into this variable for the ado.net source but i'm not sure how to go about this.

Please let me know if you need anymore info.

Your help is much appreciated!

Thanks OluwaT

im using SSDT version 15.9.16

Hadi
  • 36,233
  • 13
  • 65
  • 124
OluwaT
  • 13
  • 4
  • 1
    check the third method in the following answer: https://stackoverflow.com/questions/43746258/query-a-database-based-on-result-of-query-from-another-database/43988356#43988356 – Hadi Feb 24 '20 at 20:33
  • Hi Hadi Thanks for your response. I've taken a look at your third method and Im not fully following the logic, however ive followed the steps thus far... Apologies as im new to to SSIS. - I created the execute sql task with your command, replaced the dbo.MyTable with my destination table to check. - The second select im not sure about..as i looks like this is where is should add my query from the ado.net source but this is from a different DB so im not sure how it will work..Please explain - Lastly how will this connect to my data flow. and do I keep my dataflow the same? - Now – OluwaT Feb 25 '20 at 10:05
  • ok i think I now understand whats happening in your Option 3. The 2nd select will be used in the variable right? A couple issues i have with the command is in the substring. I need single quotation marks around each value in the where clause as this is a text column. Also the sub-string is insert the comma after the first character of the each value, instead of after each value. Please help, Thank you – OluwaT Feb 25 '20 at 12:16
  • Sorry for the late response. I will try to provide an update in a while. – Hadi Feb 25 '20 at 18:49
  • I forgot to add a comma :) You can check my answer update. I think it should work now – Hadi Feb 25 '20 at 19:22

0 Answers0