2

Is it possible to reference a SQL field in your SSIS variable?

For instance, I would like use the field from the "table" below

Select '999999' AS Physician_Profile_ID

as a dynamic variable (named "CMSPhysProID" in our example) here

enter image description here

I plan on concatenating multiple IDs into a In statement.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Steven
  • 139
  • 1
  • 11
  • Are you trying to use an expression to pass dynamic SQL command to a source? Or you need to store the value of a column within a variable? – Hadi Dec 27 '19 at 21:50
  • I need to store the value of a column within a variable so it can be referenced in another variable. – Steven Dec 27 '19 at 22:22

3 Answers3

1

Possible by using execute sql task


In left side pan of Execute SQL task, general tab


1.Select result set as single row
2. Connection type ole db
3. Set connection and form SQL statement, As you mentioned Select '999999' AS Physician_Profile_ID
4.Go to result set in your left side pan
5. Add your variable where you want to store '999999'
6. Click ok
Santhana
  • 407
  • 4
  • 16
1

If you are looking to store the value within the variable to be used later, you can simply use an Execute SQL Task with a single row result set. More details in the following article:

If you are looking to add a computed column while importing data, you must use a Derived Column Transformation within the data flow task to add a column based on another one, you can refer to the following article for more details about this component:

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

What are you trying to accomplish by concatenating the IDs into an "IN" statement? If the idea is to use the values of the IDs to limit the results, as a dynamic WHERE clause, you may have better luck just using a lookup against either a table you maintain with the desired IDs or even a static list generated in the package with a script task. (If you can use the lookup table method it will be much easier to maintain as you only have to update a table, not your source code.)

Alternatively, you may even be able to accomplish the goal with a join. Create a temp table from the profile IDs you want to keep and join to it, or, again, use it as a lookup component. Dynamically creating a where clause using IN will come in a lot slower and will be cumbersome to maintain.

Joey Morgan
  • 823
  • 6
  • 9
  • I trying to create an URL string with a Where clause. For example, `"https://openpaymentsdata.cms.gov/resource/bqf5-h6wd.json?$where="+ @[User::PhysicianProfileIDs] + "&$limit=100000"` The string is then used in Script Component Code – Steven Dec 27 '19 at 22:24