CODEWITHSUNDEEP
sql-serverssisetlnetezza
Russtopher
Russtopher

Reputation: 123

Select statement to grab data from a Netezza database and insert into a SQL Server database (SSIS)

I'm using SSIS to grab data from a Netezza db and inserting into SQL Server db. I have the queries working, and the first 2 queries use a data flow task to grab from Netezza (based on an ID_NUMBER) and inserted into SQL Server.

I have 15 different ID_NUMBERs to run against, and I want to run them one at a time, but I don't want to hard code the ID_NUMBER into the SQL statement each time I want to run a different ID_NUMBER.

Is there a way I can loop through each ID_NUMBER without hardcoding them in the SQL Statement?

Upvotes: 2

Views: 1016

Answers (1)

Hadi
Hadi

Reputation: 37313

Update 1 Using ODBC Source

Based on your comments you are using ODBC source

In ODBC you cannot use parameters (same logic in link provided), you have to assign an expression to the SqlCommand Property of the ODBC Source

  1. Click on the DataFlow Task
  2. In the Properties Tab (press F4 to show it) go to expressions

  3. Assign a similar expression to [ODBC Source].[SqlCommand] property

    "SELECT * FROM MyTable WHERE [id] = " + (DT_WSTR,50)@[User::id]

  4. Click on the DataFlow Task and change the Delay Validation property to True

Use Execute SQL Task + Foreach Loop Container

  1. You have to use an Execute SQL Statement and store IDs in a result set.
  2. Then use a For each loop container to loop over IDs
  3. Inside the foreach Loop Container add a DataFlow Task
  4. In the Dataflow task use SQL command from variable as source and the variable must be built as expression

For a Step-by-Step Tutorial you can refer to the following Link:

Upvotes: 2

Related Questions