23 Foreach loop ado enumerator in SSIS example

[@michaelmuell]

Your Channel is an absolute goldmine. Thanks a lot!

[@learnssis]

Thank you so much Michael Muell.

[@SaraNeiman]

Amazing course, exactly what I need, thank you so much for creating it

[@learnssis]

Thank you Sara.

[@AshishPatel-yl1bk]

How to run same SQL script across different SQL servers using Each Loop ?

[@learnssis]

In the first execute SQL task you can write a query to get list of all servers assign it to an object variable. Now you will use the foreach loop container as shown in the video and will select the ado enumerator, will select the object variable, and will need to create an SSIS variable like ServerName whose value you can set in the foreach loop in the Variable mapping pane. Now in the oledb connection manager, you can go to expression and set the ServerName property from the ServerName ssis variable.

[@ritujavyavahare7328]

I think there is no need to create filepath variable, because we are not using it anywhere. I tried this without filepath var and it worked fine.

[@learnssis]

Yes you are right.

[@wil-fri]

You are a hero ❤🎉

[@learnssis]

ha ha 😀.

[@javaguitarist]

Thank you so much. This helped me enormously.

[@learnssis]

You are most welcome Scott.

[@khbeyonce]

Aqil bhai, just a quick question why did we create FilePath variable as I don't see we used it anywhere

[@learnssis]

You are correct. I created a FilePath variable but did not use it, good catch. We don't need to create the variable if we are not using it. Thank you.

[@SuccessPradhan-h2n]

Hey there, I have a question regarding my database setup. I have a table(Directory), there are few columns that store error flags, using "ERROR" or "OK" to indicate errors. I also have another table that stores the names of columns with error flags. I'm wondering if I can use a Foreach Loop to dynamically retrieve these column names and generate an Excel file for each column where the data is "ERROR." In my Execute SQL Statement, I'm currently using the command: select distinct ERROR from Error_Name; Where "ERROR" is the column name and "Error_Name" is the table storing all the columns with error flags. Within the Foreach Loop, I'm configuring the data flow in the OLE DB Source Editor, and for the Data Access Mode, I'm using SQL Command Text with the following query: select * from Directory where ? like '%ERROR%'; My question is whether this setup is appropriate for achieving my goal of dynamically generating Excel files for columns with "ERROR" data. Will the SQL Command inside the data access mode recognize the '?' parameter? I want to ensure that it properly substitutes the parameter with the column names when executed. Any advice or suggestions would be greatly appreciated!

[@learnssis]

Are you able to generate the excel file or are you getting any error ? If you are able to generate the excel file then I don't think there is any issue with this approach.

[@SuccessPradhan-h2n]

@@learnssis I have successfully executed the loop, but I am not receiving any data. The loop executed without errors, but I suspect there may be an issue with the SQL command. It seems that we cannot assign a column name as a parameter directly. Is there an alternative approach, such as using a script task?

[@MVinodKumar-tt1tu]

Hello Sir, When I used Objcity variable datatype as String i got this error " [Execute SQL Task] Error: Executing the query "SELECT DISTINCT CITY FROM PERSON.ADDRESS" failed with the following error: "The type of the value (DBNull) being assigned to variable "User::ObjCity" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. " As we know we got this error because I need to use Object as datatype bcz we are getting more than single row. But i didnt understood what is this error sir? Please Explain sir

[@learnssis]

Objcity variable datatype should be used as Object not string.

[@antoniolupen6138]

Simply amazing. Thaaanks

[@learnssis]

You are most welcome Sir.

[@govindasindhu3728]

When we want our result set as single row in execute SQL task such that it must iterate through each value then what to use in ado object source variable .? User:objcontractid I used but it is throwing error can you please suggest the changes

[@govindasindhu3728]

Can you please suggest me some video on how to know the difference when to use what type of result set single row, full result set ,etc and then what to use in ado object source variable

[@khaledmahmoud8803]

please in need the same example using excel becuse i have issue in connection string The connection string format is not valid. It must consist of one or more components of the form X=Y, separated by semicolons. This error occurs when a connection string with zero components is set on database connection manager.

[@parvanekhemati1015]

Hi please tell me where is the connection manager window, I can't create an Expression

[@learnssis]

If you right click on connection manager and go to properties, you will find the option to write the expressions.

[@satishchoudhury1996]

I want to extract data for 2 years.. but monthly wise extract i.e 24 files need to be generated automatically based on date How to loop this condition

[@learnssis]

I think you can use an execute sql task and in that task just create a staging table and insert 24 months value to that table. For example for 2021 and 2022, insert the values like this 202101, 202102 and so on till 202212. OR you can create 2 columns one for year and one for month and insert the value 2021 for year and 01 for month. Now just before the foreach loop container in the execute sql task select those 24 records Year and month from the staging table and assign the value to Object variable. Now declare 2 more SSIS variable Year and Month and map those 2 variables inside foreach loop container with ado enumerator and then fetch the data from source table using a sql query command and pass the value of these variables year and month to sql query so that sql query value will change for all 24 months during iteration of loop.

[@satishchoudhury1996]

@@learnssis will definitely give a try.... Thanks

[@learnssis]

@@satishchoudhury1996 great.

[@deepikarawat1005]

Sir, Thanks for your Videos... Really Helpful... :)

[@learnssis]

Thank you Deepika, glad you liked the videos.

[@dilipkumar-ck1nc]

In expression builder what u write is not visible can u make a note in notepad

[@learnssis]

Okay, I have uploaded a new file and put the expression code there.

[@kavithagunashekar5200]

Thank you for the video, it really helped my problem :)

[@learnssis]

Glad to know Kavitha that it helped you.

[@motoray1973]

Thanks for this video...just helped me solve a problem I was struggling with.

[@learnssis]

Good to know that it helped you. Thanks.

[@jorgemontecinosmeza]

Thit is excelent. Thanks.

[@learnssis]

Glad you liked the video. Thanks.

[@jhenai1mmmmmh]

did u know that u save lives? O.o

[@learnssis]

ha ha. Thank you Jhenai Dixon.

[@kevinalexismartinezherrera7004]

I agree you hahaha

[@vinayprasad2144]

Good video, got a question, in sql task properties when you are setting up resultset, you put 0 for the result name, can you please explain that briefly

[@learnssis]

If you are assigning some value to an SSIS variable under resultset, the result name will be assigned in sequential order. The first variable will be assigned result name as 0. If you are assigning values to multiple SSIS variables then second SSIS variable's result name will be 1, and for third one it will be 2 and so on. We assign the result name in sequential order starting from 0.

[@vinayprasad2144]

@@learnssis thanks for explanation

[@Shankar-zi3yk]

why u have taken newyork has cityvariable value because u know the value is present if u dont know how will u go for that value u should explained that wright please make a video on for and for each loop containers in detail each and every point

[@learnssis]

You don't need to give the City variable value to New York, you can keep this empty as well. You can give the value as Delhi or Mumbai as well, I just gave the value as New York because the variable name is City but the value of City will be assigned during for each loop iteration, so don't worry about its value.

[@Shankar-zi3yk]

u have used a filepath variable and u havent used it why confusing us

[@learnssis]

I am sorry for that, Initially I declared the variable FilePath because I thought I will use that in flat file connection manager to make the path dynamic, but during writing the code, a new idea came into my mind that we can write the code without using the FilePath variable thus I did not used it.

[@learner_SSIS]

Hi sir,i follow same video in odbc MySQL ( MySQL workbench. And Hiedisql) not working Very easy to work OLEDB side,but ODBC very difficult sir Please upload ODBC video sir