Frustrated with the error "Too many fields defined" when create the table 'Mydata'. For me only works fine with one field. Any suggestion or advice? Thanks in advance!!
Thank you !! i used to have an empty excel file , move it to another dir , overrite destination and then put the data in it , with this way i can avoid the duplicated data issue. I didn't know that we can perform sql queries on excel connections. Thank You again. It would be very helpful if you show some select statements on Excel connections
When you export data to excel and you have INT in db (let's assume Product ID) how do you make it to be number data type in excel after export? every time I try different options its always text, I know I can convert this in excel manually, but I would like to be already converted with export
I was running the packageo but no data was found when I opened the Excel Sheet after running the package so I opened the excel sheet bfr executing the package and I was able to load the data into the spreadsheet.
For that you can follow the same steps as shown in the video, the only difference will be that instead of OLE Db Source as shown in the video, you will use the Excel Source.
I am doing the same but when i am executing the package then i am getting an error "[Execute SQL Task] Error: Executing the query "drop table 'ExcelDestination' " failed with the following error: "Syntax error in DROP TABLE or DROP INDEX.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly." What am i doing wrong here? Can you suggest/help me to fix it?
Hi Ramandeep, Copy the Create table script from Excel Destination, paste it in the "Execute SQL Task" and just change the name of sheet\table in your case, DO NOT remove the ' (single quote) from Table Name, its actually not the single quote but its a character left to 1 Key on your keyboard. So just change the name of table, and then copy\paste the same table name for the Drop table statement as well.
hi ther i m facing issue while importing exel fil here is the error " TITLE: Microsoft Visual Studio ------------------------------ Could not retrieve the table information for the connection manager 'Excel Connection Manager 9'. Failed to connect to the source using the connection manager 'Excel Connection Manager 9' ------------------------------ BUTTONS: OK ------------------------------ " could u help fr the same
Yeah there are some challenges storing and viewing data in excel. This is the method I told you about exporting data to excel using SSIS. I am not aware about other options.
Maybe after exporting the data to excel file, add a file system task to move the file to another directory and then the new file will be created at the same path with same name. Then even you don't need to add code to drop and recreate the sheet. Just use the data flow task and file system task and you should be good.
Hi , It's really very helpful. But after deployment i have to open the Excel file and save the data then next run will work otherwise return the blank sheet. Is manually file open and save required after autorun? Please help to automate this
No, there is no manual work needed here, just make the package as shown in the video and run it should run fine and should load the data to excel file. And then when next time you will rerun it then it will delete the existing data from excel sheet and will populate it again from sql server table.
@@learnssis good morning Akil, I created that package as shown in video which you have shared ,it is working fine without deployment but after deployment like autorun the package on a specific time ,its need always open the Excel file and save manually then it's extract the data after delete otherwise Blank sheet return.
@@Amit-q8lThat is strange. Can you do few things, 1. Make a template of the excel file. I mean delete the data from the excel file but keep the header row. Keep this file at a different\fixed location. We won't delete\insert data to this file. 2. Now in the ssis package, disable the code to drop and recreate the excel sheet. 3. Add a file system task instead in place of execute sql task. In the File system task, we will copy the template excel file from its original location to our destination location where your excel file will be created with the data. 4. Rerun the package. Now the package will copy template excel file to the location where excel file will be created and ssis package will populate data to the excel file at new location. test this package from visual studio first and then deploy it.
@@learnssis I tried ,might be I missed some steps...could you please share a video link of it ..if you created on this..overwrite Excel file using file system.kindly help me
hi aqil i have done as per ur video explanation, it was not showing the data sheet with out $ symbol.., then i used new table as source file, query executed with out errors but i could not able to find were is the file is.... help me out to check, 1. i need details of why without dolor $ sheet is not visible to me, 2. where data has saved after executed success fully, (where i made excel data connection i didn't see the file )
Hi, DO we have anything to do like (if exists), because the package should run even in our first attempt? In your example if you use drop and create for the first attempt it gonna fail as we don't have existing Excel file on your drive. Please let me know I struck thr :(
Hello Sunil, your point is valid. However I am not aware about any such thing. When you will be writing code for Data Flow Task, then for the Excel destination you need to create an excel file locally so that you can map the columns from source to excel destination. So when you will create the excel file locally, then the package should run even in first attempt because excel file got created manually while working on Data Flow Task.
I have taken one folder I that two files one file data flat file and another file data in XML file data as well data structure also same how can you do that plz do a vedio and explain
If you got 2 files one is CSV and another one is XML then you can either use 2 Data flow task, one for loading CSV file into sql server and another Data Flow task to load the XML file into SQL Server table. I have already created video about loading data from CSV file to sql server and loading data from XML file to sql server, there won't be any change here, the change is that for my videos I created 2 packages one for CSV and another one for XML, for this video you can create 2 Data Flow Task and can do the same thing in single SSIS package as well.
I put the drop table and it's ok, but when I create the table, I have the error: [Execute SQL Task] Error: Executing the query "CREATE TABLE `Tarifa_saque` ( `ID_TRANSACAO` Decim..." failed with the following error: "Table 'Tarifa_saque' already exists.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Can oyu help me, pls?
What you can do is disable your data flow task and execute only your script which drops and creates the sheet. Then enable your data flow task and execute. The problem arises because SSIS strictly binds metadata and checks during compile time. And in your case it seems like you already have created a sheet value in your excel destination. So, when you run only the script to drop and create, it will execute successfully and then you can run the entire program without any hasle.
when i run the execute SQL Task,i have found the following Error: Retrieving the com class factory for component with CLSID,Failed due to the following error: 80040154 class not registered. Please provide the solution, how can we remove this error
secondly, I am not sure why SSIS doesn't allow temp tables . I have a stored procedure (i used temp table in the SP ) and that SP is supposed to load data into a excel file. I create the SP and runs great in the MStudio. In SSIS , when i build query using sql command runs and shows data when i click on ok, i get an error message. Any pointers or workaround suggestions greatly appreciated. It shows following below error:- Exception from HRESULT: 0xC020204A Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'SELECT CAST(b.BusinessId AS VARCHAR(MAX)) as BusinessId , b.BusinessNumber' in procedure 'CAD_MagnifyBusinessOrgAndOutstandingPrincipalBalance' uses a temp table.". Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Try to call the Stored procedure like below EXEC('EXEC dbo.TestSSISTempTable') WITH RESULT SETS ( ( ContactID int, FirstName VARCHAR(50), MiddleName VARCHAR(50), LastName VARCHAR(50), Suffix VARCHAR(50), EmailAddress VARCHAR(50)) ) In above code stored procedure name is dbo.TestSSISTempTable, and from this stored procedure we are selecting below fields ContactID int, FirstName VARCHAR(50), MiddleName VARCHAR(50), LastName VARCHAR(50), Suffix VARCHAR(50), EmailAddress VARCHAR(50) So you can write the code as per above example. Thanks.
sir in execute sql task you wrote sql query to drop and create a table in excel sheet right?but my question is that can we create data in excel sheet using sql queries??till now what i know is that we just open excell sheet and write the data using keyboard. But can we create data using sql queries in excel sheet?
We can insert the data into excel sheet using a Data Flow Task. For example in Data Flow task we can take a source from where we can pull the data and then we can insert the data into excel using Excel destination. I don't know if it is possible to write the data into excel file using sql insert queries.
@@learnssis ha using data flow I got that but how did drop and create queries worked on excel sheet? I am confused here. You wrote queries in "execute SQL task " right?? those queries did dropping and creating of data in the excel sheet right??
@@lakshmanmudunuri8052 Yes, sql queries can drop and create the sheet. I think because SSIS is from Microsoft, SQL Server is from Microsoft and Excel is also from Microsoft, thus they made is possible to drop and create the excel sheet using sql drop and create queries.
not working, it is showing blank rows, for example previous data was in 10 rows, we drop it as explained in video so data removed but new insert start from 11th row. please check.
One workaround to this issue is to use file system task to delete the excel file instead of writing a script. Use the script only to create the sheet, but for dropping it, use file system task-> choose delete option and set the path to the excel file path. Make sure the file task is happening before the script.
Great explanation and video. Is there a way to create a new excel file every time i run a package project instead of rewriting it? I am trying to create daily reports but need the previous ones saved for audit reason? Thank you so much for this video. First time user right out of college, it is really helpful!❤
Hi Anna, You can easily create a new excel file every time you execute the SSIS package, but you would need to think what name you want to give to the excel file, for example one way to making the excel file unique can be if you can append the current date time like yyyymmddhhmmss to the file then you can do that and every time when the package will run it will create a new excel file according to the current date time and you can pass the Excel file path value to excel connection manager using an SSIS variable. You excel file name can be something like Customer_20230103102050.XLSX Let me know if that will work for you then I can share the syntax for getting this value in an SSIS variable and you can pass that variable to the excel connection manager or I can make a small video on that one.
Can you try deleting the file before doing the export, like you can use file system task to delete the excel file and then try to do the export ? In below video I shown how to export data to a new excel file every time th-cam.com/video/OkSOBRaTq74/w-d-xo.html
@@learnssis thanks s lot! I'll surely will try it. Thanks again for everything you do. I Don't know you but again... thanks for all of us that are struggling to be better and get a better job.
@@jphumaniNo problem, let me know how it works. I have some SSIS packages those I created 5-6 years ago and they read the data from sql table and export it to an excel file and sends the excel file to list of people and working fine using the method above, they are scheduled using sql agent jobs, although the packages were created in BIDS 2008, not sure what might be the reason for appending data in your case, try the above 2 methods and let me know.
good. I do these steps and it works perfectly, but I have a problem because I work with 3 environments that is, development, quality and production, when I am in development and I perform the deployment at quality the task executes it perfectly but the file does not suffer any change and when I go back to development if it takes the file. Do you know why this happens? The only thing that I modify is the datasource and the tables both in development and in quality are full
I have not tested it with multiple environments. But I have seen the similar issue sometime with few files. Still I don't have any idea why this happens sometime.
Hi, I have 2 different tasks for drop and create(Create followed by drop). Since a mobile app reads the excel sheet at the same time of create table task, the task fails rarely as well as the package. When I tried to run the package again it kept on failing until the excel file was replaced with a similar file having the sheet with the same name. Is there any solution for this? Is there any option like ''if exists, then drop else end'' as a sql statement for DROP task? If I change the transactionOption property to ''Required for the Create Table task, will it work? As it should rollback the entire package when the CREATE TABLE task fails. ' Please suggest
Hi Alenso, In this scenario, what I would suggest is that you should place a script task before the execute sql task that drop and create the excel sheet. In that Script Task, you can get the Excel sheet name from the excel file using C# (you check online how to get sheet name from excel file using C#) and populate an SSIS variable like SheetExists or SheetNotExists and then on the Precedence constraint to execute sql task to drop and create sheet, you can decide if sheetexists then you can make the execute sql task to run otherwise skip the execute sql task.
Hi Ahmed ..iam following your videos explanation was very good.when am trying to drop and recreate table shows an error like this [Execute SQL Task] Error: Executing the query "DROP TABLE 'MyDATA' " failed with the following error: "Syntax error in DROP TABLE or DROP INDEX.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task Drop and recreate the table failed...then how to solve this pls explain me.
@@manjulaannam257 Sorry did not get you completely on this one. Template file is a kind of sample file we will use that will have same header information that we want to export without data. So every time this empty header file will be copied to our destination location and then the export will run against this empty file which will insert data to empty file with header information.
This works fine as long as no data is deleted from the table. If deleted and try to run the package again then except header no data is available in the excel sheet. Please let me know if anyone gets the same result.
thnx for video its very helpful . I have build same SSIS package in VS 2015 it's is run fine in first time but second its throws an error [Excel Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Cannot expand named range.".
Thanks for the video. I'm trying to do the same but facing with below error Can someone please help ? TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Data Flow Task [SSIS.Pipeline]: "Excel Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA". Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ BUTTONS: OK ------------------------------
This error "Excel Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA" normally comes when excel sheets meta data has changed. Just drop the Excel destination, recreate it and make sure that the new excel sheet you are creating its meta data should match with the excel destination sheet meta data, they should not change.
I encountered an error showing: TITLE: Microsoft Visual Studio ------------------------------ Exception deserializing the package "The process cannot access the file 'C:\Users\Parag\source epos\Integration Services Project2\Integration Services Project2\bin\Development\Integration Services Project2.ispac' because it is being used by another process.". ------------------------------ ADDITIONAL INFORMATION: The process cannot access the file 'C:\Users\Parag\source epos\Integration Services Project2\Integration Services Project2\bin\Development\Integration Services Project2.ispac' because it is being used by another process. (mscorlib) ------------------------------ BUTTONS: OK ------------------------------
I am new to SSIS and your video was very helpful. It was exactly what I was looking for without the bells and whistles. Thanks
I am so glad Ann that it helped you. Wish you good luck with learning SSIS.
Hi
I tried the exact same thing...but instead of deleting the sheet it is deleting the header.. please let me know why it is happening?
Frustrated with the error "Too many fields defined" when create the table 'Mydata'. For me only works fine with one field. Any suggestion or advice? Thanks in advance!!
I'm with the same problem.
Thank you !! i used to have an empty excel file , move it to another dir , overrite destination and then put the data in it , with this way i can avoid the duplicated data issue.
I didn't know that we can perform sql queries on excel connections.
Thank You again.
It would be very helpful if you show some select statements on Excel connections
6 years old video but still a PRECIOUS one 😍
Thank you Jeff.
When you export data to excel and you have INT in db (let's assume Product ID) how do you make it to be number data type in excel after export? every time I try different options its always text, I know I can convert this in excel manually, but I would like to be already converted with export
I was running the packageo but no data was found when I opened the Excel Sheet after running the package so I opened the excel sheet bfr executing the package and I was able to load the data into the spreadsheet.
Nicely Explained.Thank you
Good to know that you liked the video.
Hey Aqil , I'm nt able to create an EXCEL with col. nvarchar(3600) column and fix?
Hello, and if the origin and destination are Excel, what button or plugin should I use? to not duplicate values or rewrite
For that you can follow the same steps as shown in the video, the only difference will be that instead of OLE Db Source as shown in the video, you will use the Excel Source.
@@learnssisthank you. New suscriptor.
I am doing the same but when i am executing the package then i am getting an error "[Execute SQL Task] Error: Executing the query "drop table 'ExcelDestination'
" failed with the following error: "Syntax error in DROP TABLE or DROP INDEX.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
What am i doing wrong here? Can you suggest/help me to fix it?
Hi Ramandeep, Copy the Create table script from Excel Destination, paste it in the "Execute SQL Task" and just change the name of sheet\table in your case, DO NOT remove the ' (single quote) from Table Name, its actually not the single quote but its a character left to 1 Key on your keyboard. So just change the name of table, and then copy\paste the same table name for the Drop table statement as well.
Thanks for the reply I changed the single qoute with [] and after put this package is executing successfully.
Good to know that.
@@ramandeepsingh8227 The [] worked for me as well! Thank you!
@@ramandeepsingh8227 hi...i have tried with [ ]..but its not working...what might be the mistake
Hi @aqil Previous data is not deleted it appends the data
can you please help me out ..
Thank You..
Hi,I am facing some where I have blank row then the excel sheet is not working properly.please help
Why would this work for some excel files and does not work for other? What can be the issue there? any input would be appreciated.
Check you connection manager for the excel sheet
hi ther i m facing issue while importing exel fil here is the error " TITLE: Microsoft Visual Studio
------------------------------
Could not retrieve the table information for the connection manager 'Excel Connection Manager 9'.
Failed to connect to the source using the connection manager 'Excel Connection Manager 9'
------------------------------
BUTTONS:
OK
------------------------------
" could u help fr the same
Hi, download and install this and try again
www.microsoft.com/en-za/download/details.aspx?id=13255
I want the same type (number) of data in excel which is not happening it comes as WSTR... please help
Yeah there are some challenges storing and viewing data in excel. This is the method I told you about exporting data to excel using SSIS. I am not aware about other options.
thank u so much u saved my life
ha ha, thanks. You can use this method as well as an alternative.
th-cam.com/video/y5kvWjVNVfc/w-d-xo.html
I followed the steps, only the heater getting dropped and getting recreated, but the data still remains the same. What could be the reason?
Maybe after exporting the data to excel file, add a file system task to move the file to another directory and then the new file will be created at the same path with same name. Then even you don't need to add code to drop and recreate the sheet. Just use the data flow task and file system task and you should be good.
Will the excel connection still be intact, if the initial file is moved?
@@SuccessPradhan-h2n, Take a look at this video, you would need to create the excel sheet.
th-cam.com/video/NPYxOpS-kLg/w-d-xo.html
Hi Aqil, I could not choose the "Sheet name from the destination excel file. I am using office 365
Hi, download and install this and try again
www.microsoft.com/en-za/download/details.aspx?id=13255
When I click the New button, the window closes. I cannot see the script and choose the sheet.
It is not working.
Hi ,
It's really very helpful.
But after deployment i have to open the Excel file and save the data then next run will work otherwise return the blank sheet.
Is manually file open and save required after autorun?
Please help to automate this
No, there is no manual work needed here, just make the package as shown in the video and run it should run fine and should load the data to excel file. And then when next time you will rerun it then it will delete the existing data from excel sheet and will populate it again from sql server table.
@@learnssis good morning Akil,
I created that package as shown in video which you have shared ,it is working fine without deployment but after deployment like autorun the package on a specific time ,its need always open the Excel file and save manually then it's extract the data after delete otherwise Blank sheet return.
@@Amit-q8lThat is strange.
Can you do few things,
1. Make a template of the excel file. I mean delete the data from the excel file but keep the header row. Keep this file at a different\fixed location. We won't delete\insert data to this file.
2. Now in the ssis package, disable the code to drop and recreate the excel sheet.
3. Add a file system task instead in place of execute sql task. In the File system task, we will copy the template excel file from its original location to our destination location where your excel file will be created with the data.
4. Rerun the package.
Now the package will copy template excel file to the location where excel file will be created and ssis package will populate data to the excel file at new location.
test this package from visual studio first and then deploy it.
@@learnssis thanks,I will share an update ASAP.thanks once again for your support and quick response.
@@learnssis I tried ,might be I missed some steps...could you please share a video link of it ..if you created on this..overwrite Excel file using file system.kindly help me
hi aqil i have done as per ur video explanation, it was not showing the data sheet with out $ symbol.., then i used new table as source file, query executed with out errors but i could not able to find were is the file is.... help me out to check, 1. i need details of why without dolor $ sheet is not visible to me, 2. where data has saved after executed success fully, (where i made excel data connection i didn't see the file )
Hi, DO we have anything to do like (if exists), because the package should run even in our first attempt? In your example if you use drop and create for the first attempt it gonna fail as we don't have existing Excel file on your drive. Please let me know I struck thr :(
Hello Sunil, your point is valid. However I am not aware about any such thing. When you will be writing code for Data Flow Task, then for the Excel destination you need to create an excel file locally so that you can map the columns from source to excel destination. So when you will create the excel file locally, then the package should run even in first attempt because excel file got created manually while working on Data Flow Task.
Can't we automate that? :(
@@Sunil-gb5mp you can use file system task to create the files
Hi Sunil: Have you resolved this issue because I am also stuck in that part. When package runs the first attempt it is throwing error.
I have taken one folder
I that two files one file data flat file and another file data in XML file data as well data structure also same how can you do that plz do a vedio and explain
If you got 2 files one is CSV and another one is XML then you can either use 2 Data flow task, one for loading CSV file into sql server and another Data Flow task to load the XML file into SQL Server table. I have already created video about loading data from CSV file to sql server and loading data from XML file to sql server, there won't be any change here, the change is that for my videos I created 2 packages one for CSV and another one for XML, for this video you can create 2 Data Flow Task and can do the same thing in single SSIS package as well.
I put the drop table and it's ok, but when I create the table, I have the error:
[Execute SQL Task] Error: Executing the query "CREATE TABLE `Tarifa_saque` (
`ID_TRANSACAO` Decim..." failed with the following error: "Table 'Tarifa_saque' already exists.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can oyu help me, pls?
What you can do is disable your data flow task and execute only your script which drops and creates the sheet. Then enable your data flow task and execute.
The problem arises because SSIS strictly binds metadata and checks during compile time. And in your case it seems like you already have created a sheet value in your excel destination. So, when you run only the script to drop and create, it will execute successfully and then you can run the entire program without any hasle.
when i run the execute SQL Task,i have found the following Error: Retrieving the com class factory for component with CLSID,Failed due to the following error: 80040154 class not registered.
Please provide the solution, how can we remove this error
Under Project, Properties, Configuration properties, Debugging. Try changing the Run64BitRunTime to different options False OR True.
name of the exec sheet iam unable to open it when ever i click on new its always closing
Hi, download and install this and try again
www.microsoft.com/en-za/download/details.aspx?id=13255
secondly, I am not sure why SSIS doesn't allow temp tables . I have a stored procedure (i used temp table in the SP ) and that SP is supposed to load data into a excel file. I create the SP and runs great in the MStudio. In SSIS , when i build query using sql command runs and shows data when i click on ok, i get an error message. Any pointers or workaround suggestions greatly appreciated. It shows following below error:-
Exception from HRESULT: 0xC020204A
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'SELECT
CAST(b.BusinessId AS VARCHAR(MAX)) as BusinessId
, b.BusinessNumber' in procedure 'CAD_MagnifyBusinessOrgAndOutstandingPrincipalBalance' uses a temp table.".
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
Try to call the Stored procedure like below
EXEC('EXEC dbo.TestSSISTempTable')
WITH RESULT SETS
( ( ContactID int, FirstName VARCHAR(50), MiddleName VARCHAR(50), LastName VARCHAR(50),
Suffix VARCHAR(50), EmailAddress VARCHAR(50)) )
In above code stored procedure name is dbo.TestSSISTempTable, and from this stored procedure we are selecting below fields
ContactID int, FirstName VARCHAR(50), MiddleName VARCHAR(50), LastName VARCHAR(50),
Suffix VARCHAR(50), EmailAddress VARCHAR(50)
So you can write the code as per above example. Thanks.
Temp tables from SQL are not adviced while using SSIS. You can either extract the data directly from the staging table or use CLE.
sir in execute sql task you wrote sql query to drop and create a table in excel sheet right?but my question is that can we create data in excel sheet using sql queries??till now what i know is that we just open excell sheet and write the data using keyboard. But can we create data using sql queries in excel sheet?
We can insert the data into excel sheet using a Data Flow Task. For example in Data Flow task we can take a source from where we can pull the data and then we can insert the data into excel using Excel destination. I don't know if it is possible to write the data into excel file using sql insert queries.
@@learnssis ha using data flow I got that but how did drop and create queries worked on excel sheet?
I am confused here. You wrote queries in "execute SQL task " right?? those queries did dropping and creating of data in the excel sheet right??
@@lakshmanmudunuri8052 Yes, sql queries can drop and create the sheet. I think because SSIS is from Microsoft, SQL Server is from Microsoft and Excel is also from Microsoft, thus they made is possible to drop and create the excel sheet using sql drop and create queries.
Keep going very nice video
Thank you Aditya.
not working, it is showing blank rows, for example previous data was in 10 rows, we drop it as explained in video so data removed but new insert start from 11th row. please check.
I have the same problem, did you solve it?
One workaround to this issue is to use file system task to delete the excel file instead of writing a script.
Use the script only to create the sheet, but for dropping it, use file system task-> choose delete option and set the path to the excel file path.
Make sure the file task is happening before the script.
Nicely explained :)
Good to know that you liked the video.
Great explanation and video. Is there a way to create a new excel file every time i run a package project instead of rewriting it? I am trying to create daily reports but need the previous ones saved for audit reason? Thank you so much for this video. First time user right out of college, it is really helpful!❤
Hi Anna,
You can easily create a new excel file every time you execute the SSIS package, but you would need to think what name you want to give to the excel file, for example one way to making the excel file unique can be if you can append the current date time like yyyymmddhhmmss to the file then you can do that and every time when the package will run it will create a new excel file according to the current date time and you can pass the Excel file path value to excel connection manager using an SSIS variable.
You excel file name can be something like Customer_20230103102050.XLSX
Let me know if that will work for you then I can share the syntax for getting this value in an SSIS variable and you can pass that variable to the excel connection manager or I can make a small video on that one.
I made a video on this one
th-cam.com/video/OkSOBRaTq74/w-d-xo.html
Hi there! Excellent tutorials! BUT...this doesnt seems to work anymore. I've done everything just like this and it keeps appending information.
Can you try deleting the file before doing the export, like you can use file system task to delete the excel file and then try to do the export ?
In below video I shown how to export data to a new excel file every time
th-cam.com/video/OkSOBRaTq74/w-d-xo.html
@@learnssis thanks s lot! I'll surely will try it. Thanks again for everything you do. I Don't know you but again... thanks for all of us that are struggling to be better and get a better job.
@@jphumaniNo problem, let me know how it works. I have some SSIS packages those I created 5-6 years ago and they read the data from sql table and export it to an excel file and sends the excel file to list of people and working fine using the method above, they are scheduled using sql agent jobs, although the packages were created in BIDS 2008, not sure what might be the reason for appending data in your case, try the above 2 methods and let me know.
good.
I do these steps and it works perfectly, but I have a problem because I work with 3 environments that is, development, quality and production, when I am in development and I perform the deployment at quality the task executes it perfectly but the file does not suffer any change and when I go back to development if it takes the file. Do you know why this happens? The only thing that I modify is the datasource and the tables both in development and in quality are full
I have not tested it with multiple environments. But I have seen the similar issue sometime with few files. Still I don't have any idea why this happens sometime.
Hi, I have 2 different tasks for drop and create(Create followed by drop). Since a mobile app reads the excel sheet at the same time of create table task, the task fails rarely as well as the package. When I tried to run the package again it kept on failing until the excel file was replaced with a similar file having the sheet with the same name. Is there any solution for this?
Is there any option like ''if exists, then drop else end'' as a sql statement for DROP task?
If I change the transactionOption property to ''Required for the Create Table task, will it work? As it should rollback the entire package when the CREATE TABLE task fails. '
Please suggest
Hi Alenso,
In this scenario, what I would suggest is that you should place a script task before the execute sql task that drop and create the excel sheet. In that Script Task, you can get the Excel sheet name from the excel file using C# (you check online how to get sheet name from excel file using C#) and populate an SSIS variable like SheetExists or SheetNotExists and then on the Precedence constraint to execute sql task to drop and create sheet, you can decide if sheetexists then you can make the execute sql task to run otherwise skip the execute sql task.
Nice sir !!
Thank you Harsha vardhan for liking the video.
Hi Ahmed ..iam following your videos explanation was very good.when am trying to drop and recreate table shows an error like this [Execute SQL Task] Error: Executing the query "DROP TABLE 'MyDATA'
" failed with the following error: "Syntax error in DROP TABLE or DROP INDEX.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task Drop and recreate the table failed...then how to solve this pls explain me.
Can you try this method ? This is a better approach to handle this.
th-cam.com/video/y5kvWjVNVfc/w-d-xo.html
@@learnssis Yes..now its working .......i have one doubt what about the template file (data is loading or not)
@@manjulaannam257 Sorry did not get you completely on this one. Template file is a kind of sample file we will use that will have same header information that we want to export without data. So every time this empty header file will be copied to our destination location and then the export will run against this empty file which will insert data to empty file with header information.
@@learnssis Thanks Aqil
Fixed length notepad value is not showing preview the data...how to solve it
This works fine as long as no data is deleted from the table. If deleted and try to run the package again then except header no data is available in the excel sheet. Please let me know if anyone gets the same result.
yes, I am having same issue
Thank you
You are welcome.
thnx for video its very helpful .
I have build same SSIS package in VS 2015 it's is run fine in first time but second its throws an error
[Excel Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Cannot expand named range.".
Same problem with me pls respond shubham if get the anwer now. I am facing same problem
Precise
Its good to know that you liked the video.
Thanks for the video.
I'm trying to do the same but facing with below error
Can someone please help ?
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [SSIS.Pipeline]: "Excel Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
This error "Excel Destination" failed validation and returned validation status "VS_NEEDSNEWMETADATA"
normally comes when excel sheets meta data has changed. Just drop the Excel destination, recreate it and make sure that the new excel sheet you are creating its meta data should match with the excel destination sheet meta data, they should not change.
I encountered an error showing: TITLE: Microsoft Visual Studio
------------------------------
Exception deserializing the package "The process cannot access the file 'C:\Users\Parag\source
epos\Integration Services Project2\Integration Services Project2\bin\Development\Integration Services Project2.ispac' because it is being used by another process.".
------------------------------
ADDITIONAL INFORMATION:
The process cannot access the file 'C:\Users\Parag\source
epos\Integration Services Project2\Integration Services Project2\bin\Development\Integration Services Project2.ispac' because it is being used by another process. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
Please restart your machine to fix this issue.
@@learnssis ok thanks bhai