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

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

Or just remove GO

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 ?

Great find man, great help😊

Thanks, that helped

So nice of you.

Thank you Samiul for watching the videos.

Thank you Christopher.

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.

Thank you Vijay.

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.

Thanks Aqil

@@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!

@@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-8ohs43g9X5Y.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-WRCu-7avec8.html

@@learnssis Thanks you!

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

Thank you Dhirendra.

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

For Database Name it will be Initial_Catalog

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

@@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

@@learnssis Same error, what to do?

Same error what to do?

Hi sir help out with this error

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

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

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

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 ".

Hi Sonel, Can you email me with all details like your query, variables screnshot and parameters screeshot to aqil33@gmail.com then I should be able to tell what is going wrong here.

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] + "

Right click on Integration services catalog and click on Create Catalog (7:43) ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-xaBIYxe5qVA.html

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] +"') "

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

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

Thank you so much Bhuyan for appreciating the video.

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

@@caravindpandiNo

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?

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.

@@learnssis my query is in .SQL file

@@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.

@@learnssis thank you very much for information

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

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.

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

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z78-R-SAZK4.html

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 ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z78-R-SAZK4.html

thanks bro

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

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

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

@@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

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

Glad you found it useful.

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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-n94-pFXP8eA.html 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.

@@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

@@asadnaseer7291 Below link can help you to schedule an SSIS package ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-qrV2FbQpU3I.html

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 😀

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

I have created one more video recently on execute sql task you can take a look at it ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_8nbAMHXGIQ.html

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

@@antwanwimberly1729thank you.