15 Execute SQL Task in SSIS | Use variables in Execute SQL task in SSIS

[@nivetha.meceskct6693]

Aqil you are the best SSIS person the video is very much helpful you have covered variable and parameters as well with execute SQL task....thanks a lot for your help

[@learnssis]

So nice of you.

[@christopherhorton9198]

Thanks. That was very informative. Keep it up !

[@learnssis]

Thank you Christopher.

[@Batatinhas8000]

if you have problems on the second sql statement (using variables), remove the 'go' from the query

[@Ankitmehta3115]

Great find man, great help😊

[@KRaJWanders]

Thanks, that helped

[@udithaliyanage6375]

Thanks. Saved lot of time.

[@mdsamiulislam2522]

Aqil, My dear brother, i am samiul from bangladesh... really helpful your guide line

[@learnssis]

Thank you Samiul for watching the videos.

[@dhirendramaurya5361]

Awesome all videos

[@learnssis]

Thank you Dhirendra.

[@vijaybodkhe8379]

Thank you for sharing Knowledge

[@learnssis]

Thank you Vijay.

[@joegodindata]

ERROR: [Execute SQL Task] Error: Executing the query "insert into persons values (?.?.?)" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. SOLUTION: I had the same issue and fixed this issue. In the video he actually copy pastes a different Query and you can see it for a short second to be able to copy it. Deleting GO and CREATE TABLE parts of the query wasn't enough because I needed to actually change the GO statement to 'ON [PRIMARY]' Query below: IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Person') CREATE TABLE PERSON( ID INT IDENTITY, NAME VARCHAR(50), GENDER VARCHAR(1), AGE INT) ON [PRIMARY] INSERT INTO PERSON VALUES (?,?,?)

[@learnssis]

Its good to know that you were able to fix it.

[@AOAMuse]

Or just remove GO

[@Mohamed-yb2xy]

hi i get the same error o tried different solution but i did not solve the probleme can you help me this is the query i use : INSERT INTO SSISParameterValues (ParameterName, ParameterValue, CreatedDate) VALUES ('PackageName',CAST(? AS varchar(500)), GETDATE()); i tried to not use parameters in the query and it runs correctly but when i use parameter or variable i get this same error ?

[@VISHVAPRASATH-e5h]

Thanks a lot

[@sathiyamoorthi9224]

Hi Aqil ,your video all are very useful.can you please share any video it,s getting data from one table based on smallest id and max id between data only daily .the daily trucate load. Thanks in Advance

[@learnssis]

Not sure if I got your question currently. 1. First you can use execute sql task to truncate the data from destination table. Put a truncate query in that. 2. Use a data flow task to pull the data from source table. And Use the OLE DB source, inside OLE DB source select sql command option and write your query similar to below one, it will select data ranging from min to max declare @min_id int, @max_id int set @min_id = (select min(id) from [Customer]) set @max_id = (select max(id) from [Customer]) select * from Customer where id between @min_id and @max_id 3. Now use the OLE Db destination to write the data to a sql server table that you truncated in step 1.

[@sathiyamoorthi9224]

Thanks Aqil

[@sathiyamoorthi9224]

@@learnssis I need to check duplicate data between the min id and Max I'd in the table .if any possible to store min id and Max Id store in variable.could you pls let me know Thanks for understanding!

[@learnssis]

@@sathiyamoorthi9224You can write a query similar to below in execute sql task and select the option single result set declare @min_id int, @max_id int set @min_id = (select min(id) from [Customer]) set @max_id = (select max(id) from [Customer]) select @min_id, @min_id Now in the result set, add first result set name 0 and select the ssis variable that will hold the min id and the add another result set and name it as 1 and select another ssis variable that will hold the max id. To select only the distinct records you can see how you can select only distinct records. You can use row_number function along with cte to select the unique records. kzbin.info/www/bejne/bqDLpGdpnJ6La7s kzbin.info/www/bejne/jYOmpmBtltuYmZo

[@sathiyamoorthi9224]

@@learnssis Thanks you!

[@iveebhuyan]

nicely explained

[@learnssis]

Thank you so much Bhuyan for appreciating the video.

[@VishalJaybhaye-kq4hh]

SIr it is giving me this error when we created parameters for Ram person, Execute SQL Task: Executing the query "insert into Person values (?,?,?)" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task. Can u pls tell me the issue?

[@learnssis]

Are you using Go statement inside the query ? Do not use any go statements.

[@rightplacetoeat7420]

Hi sir , Thanks for your video.You made a such a nice video. If you dont mind please make a video on real time example bY using the Execute sql task ..Thanks in advance

[@learnssis]

Its good to know that you liked the video. Sure I will try to make a video as per your suggestion. Thank you.

[@learnssis]

Its good to know that you liked the video. Sure I will try to make a video as per your suggestion. Thank you.

[@NandhithaM-z1u]

Hi Aqil, if i want to insert only the name. what format should be used. I created a table with one column (name alone), when i give the below insert under expression, the evaluate expression throws error. insert into Person values (@[User::Name] )

[@learnssis]

You would need to write "insert into Person values '"+ @[User::Name] +"' "

[@MVinodKumar-tt1tu]

Hello Sir, First Doubt: So through variables in this case each time we can enter only each record from variables into table? or can we enter multiple records at single time using variables?.... Second Doubt: INSERT INTO PERSONS VALUES ('"+ @[User::Name] +"','"+ @[User::Gender] +"','"+ (DT_WSTR,12)@[User::Age] +"') In this while using variables to enter the values into table, why we used single quote and double quote both. First single quotes and inside it double quotes and inside variable.... Please explain sir

[@learnssis]

1. At a time we can insert only one record from variable, because at one time you can store only one value in a variable. 2. If we need to use the string variable inside expression, then the syntax is that we should first write a double quote ", then + sign then drag and drop the variable then put the + sign then put the double quote ". This way we will get the value from variable. however if you have noticed if you need to insert a string value into a sql server table then we enclose the string in a single quote, same is the case here that we got the value but now to enclose the value within single quote we need to put a single quote ' before the double quote " and then in the end put a single quote ' after double quote ".

[@sureshareti2843]

Bhaiyya can we use variable object type in expression in ssis? If yes please tell how to do that

[@learnssis]

Sorry, don't know if we can use it, never tried that.

[@sureshareti2843]

@@learnssis no worries bhaiyya I tried it but it's not working

[@SoftwareCouple]

Sir after giving the server name in the top for the connection in the below drop down i am not able to find the database name.please help me

[@learnssis]

For Database Name it will be Initial_Catalog

[@sathiyamoorthi9224]

Hi Aqil, Thanks for videos, i have one doupt. To find duplicate data Select name,gender,memberid,,,,count(1 ) from ABC Group by name,gender,memberid Having count(1)>1 I need to insert above query duplicate result set in temporary table and mail send to user also copy of duplicate result set . After that If find any duplicate data i need to update . Could you pls let me know how will achieve in ssis package Thanks in Advance!!

[@oshriamir]

Hi all , i got this error : [Execute SQL Task] Error: Executing the query "insert into NPerson values (?,?,?)" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. What i can do ?

[@learnssis]

Are you providing 3 parameters to this query ? insert into NPerson values (?,?,?)

[@yuvanak1028]

Nice Lecture. But when I have tried using parameters. I was getting the following error. "Multi-step OLE DB operation generated errors. Check each OLE DB Status value, if available. No work was done ". Possible reason for failure with query,"Resultset" property is not set correctly. Can you please help.

[@learnssis]

Are you returning any value from execute sql task and assigning it to an SSIS variable ?

[@abhi-vt2xg]

@@learnssis [Execute SQL Task] Error: Executing the query "insert into persons values (?.?.?)" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ------- THIS ERROR IM GETTING

[@Jigyasuh]

@@learnssis Same error, what to do?

[@Jigyasuh]

Same error what to do?

[@abhi-vt2xg]

Hi sir help out with this error

[@sachinnavik4904]

Hello sir i am not able to see integreation servuce catalog node in ssms my sql server is 2019

[@learnssis]

Right click on Integration services catalog and click on Create Catalog (7:43) kzbin.info/www/bejne/rpKleoyumpqkjKM

[@Jigyasuh]

Hi Aqil, I got this error while executing using variable: "[Execute SQL Task] Error: Executing the query "INSERT INTO PERSONS VALUES(?,?,?)" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." Please suggest what to do?

[@caravindpandi]

have u found the solution for this error? I too got this error

[@Jigyasuh]

@@caravindpandiNo

[@MpT_Head]

I had the same. I removed the 'go' from the query and it worked fine. I had reused the first query and just changed the values but when I checked, there's no 'go' in the second 'variable' query. Did you do the same as I did?

[@LearnYouAndMe]

please provide sql expression script in your description. I am not to write it in right way.

[@learnssis]

You can download the script from the link in the description of the video if not exists(select * from information_schema.tables where table_name = 'Person') create table Person(Id int identity, Name varchar(50), Gender varchar(1), AGE int) go insert into Person values ('ABHISHEK','M', 35) --------------------- if not exists(select * from information_schema.tables where table_name = 'Person') create table Person(Id int identity, Name varchar(50), Gender varchar(1), AGE int) insert into Person values (?,?,?) ---------------------- "if not exists(select * from information_schema.tables where table_name = 'Person') create table Person(Id int identity, Name varchar(50), Gender varchar(1), AGE int) insert into Person values ('"+ @[User::Name] +"','"+ @[User::Gender] +"','"+(DT_WSTR,12) @[User::Age] +"') "

[@abhi-vt2xg]

Please do comment that expression which you used for dynamic entry

[@learnssis]

Hi, Can you try this one INSERT INTO PERSON VALUES('"+@[User::Name]+"', '"+@[User::Gender]+"', "+ (DT_WSTR,12) @[User::Gender]+") The Rule is simple. 1. When you start writing any code start the code with double quote " and close it with double quote " as well. 2. Any string value will be surrounded by single quote followed by double quote, then put a + sign and then put SSIS variable there and after it, again put a + single, followed by double quote single quote like below ' " + @[User::FILEPATH] + " ' 3. Any Int SSIS variable will be surrounded by just double quote. So put a double quote " then put a + sign, then place the SSIS variable there and put the + sign there and then put the double quote there. You also need to convert the Int to string so that it can be used in the expression. An example is below " + (DT_WSTR,12) @[User::COUNT] + "

[@learner_SSIS]

Lovely Sir 😀

[@sanjeevaranisalluri9976]

[Execute SQL Task] Error: Executing the query "create table persons (id int,name varchar(20),gend..." failed with the following error: "There is already an object named 'persons' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

[@learnssis]

In the execute sql task, before creating the Persons table add this clause IF NOT (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Persons')) Now Persons table will only be created if it does not exists in the database.

[@rammohan1935]

Hi, i have one doubt for last approach how do we pass variables. can you explain me

[@learnssis]

In this video I have shown how to pass variables to sql query. You can write the sql query in expressions, under sql query and then you can use system or user defined variable there.

[@rammohan1935]

@@learnssis my query is in .SQL file

[@learnssis]

@@rammohan1935 In that case, you would need to read the file in the script task using C# code and then if you know what vales you want to provide then you can replace the values with ssis variables using C# code there is a replace function in C# which can be used and then you can assign the final result \ final query to an ssis variable which can be executed using an execute sql task or you can execute it inside script task as well, its kind of pure C# work, there are no inbuilt tasks in ssis to perform this task.

[@rammohan1935]

@@learnssis thank you very much for information

[@rammohan1935]

I approached this way but getting error like Error Failed to lock variable

[@sonelsharma1687]

Hi, Getting error regarding parameters. Location_ID, Long,0 Location_Name,varchar,0 Sales_start_Date,Date,0 Total_Sales,Long,1 Kindly review

[@learnssis]

Hi Sonel, Can you email me with all details like your query, variables screnshot and parameters screeshot to [e-mail] then I should be able to tell what is going wrong here.

[@sanjeevaranisalluri9976]

I have executing error plz tell me how can I resolve this

[@learnssis]

In the execute sql task, before creating the Persons table add this clause IF NOT (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Persons')) Now Persons table will only be created if it does not exists in the database.

[@Maths-learn123]

Hi SQL code was not clear and can u the snd the code in Google drive also the file is not there

[@learnssis]

Sorry for that. I will try to find the code.

[@Maths-learn123]

I had error that "there is already an object named person in the database" when iam trying excute the variables it was throwing the above error

[@Maths-learn123]

@@learnssis the video was not properly zoomed the code is not clear kindly pls make this video clarity or provide the code

[@learnssis]

@@Maths-learn123 you can put below code before the drop statement that if person table will be there it will drop the person table IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'Person')) BEGIN drop table person END go

[@learnssis]

@@Maths-learn123 Okay, I will try to make another video on this topic.

[@kishanbhise4733]

Sir it will be better if you make the video little more lengthy but explanation needs to be little slower I mean the navigation was too fast to catch

[@learnssis]

Okay, got it. Will take care for future videos.

[@manishbaburaisingh1985]

AWSM

[@phaninath8237]

nice video bro can u give me the query to connect multiple Db.

[@learnssis]

Hi Phani, thanks for your question. In SSIS if you want to connect to a SQL Server database then you can drag and drop a Data flow task from SSIS toolbox into Control Flow window. After that double click the data flow task and if you are looking to read data from sql server database, then drag and drop an OLEDB Source from Data Flow Sources to the Data Flow Task Or if you want to write data to SQL Server then you can drag and drop the OLE DB Destination into the Data FLow Task. Once done you can connect to a SQL server database. If you need to connect to multiple databases at a time then you can drag and drop multiple OLEDB sources into the Data FLow Task. You can see my video "Copy data from one sql server instance to another" to see how to connect to a sql server database. Similarly you can either drag and drop multiple Data Flow Task and do the same thing what I have done for one Data Flow Task kzbin.info/www/bejne/sGibXoVjiKaNgZY

[@learnssis]

Okay, I have connected to multiple databases at a time in below video, just take a look at this video if it can be of your use kzbin.info/www/bejne/sGibXoVjiKaNgZY

[@phaninath8237]

thanks bro

[@krishnachaitanyareddy2781]

wow you can insert the sql statements with 5 different ways.

[@learnssis]

Glad you found it useful.

[@asadnaseer7291]

HI, I want to read from sql server database and convert the output into csv and place that into FTP server every 10 minutes, how can we do this? Can you please guide or make a video on this?

[@learnssis]

Hi, you need to use a data flow task and load the data from sql server to csv and then add a FTP task to move the file to FTP server. Below video can do the export thing. kzbin.info/www/bejne/pGqXXqN8jbVrm6M I don't have access to FTP server, thus I did not made a video on moving data to FTP server, probably you can find a video on moving file to FTP server. Then you can schedule the SSIS package to run on a particular schedule. I have a video to schedule the ssis package using SQL Agent job. Thanks.

[@asadnaseer7291]

@@learnssis Thank you very much for your response. Can you please mention the link for scheduling also? Then I have to create another package for FTP task, as I am unable to direct Flat File output to the FTP task

[@learnssis]

@@asadnaseer7291 Below link can help you to schedule an SSIS package kzbin.info/www/bejne/p6O5Y3mYhtWIaas

[@aniketshende4220]

bhai smjha rha hai ya read kr rha hai script tu

[@learnssis]

Sorry bhai, I know in some videos I was very quick, but I think if I create the videos lengthy then people also don't watch 😀

[@mrtorque4353]

bro ur eating words while telling i am not able to understand what ur saying

[@learnssis]

Sorry bro, yeah this is one of my oldest video and I am too quick for it. I am trying to improve.

[@learnssis]

I have created one more video recently on execute sql task you can take a look at it kzbin.info/www/bejne/lWnRk3SDfb16f7M

[@antwanwimberly1729]

@@learnssisyou are doing a phenomenal job teaching us the SSIS way. No worries!

[@learnssis]

@@antwanwimberly1729thank you.