111 How to export data to new excel file every time in ssis

hi friends my name is akhil Ahmad and in this particular video tutorial I will show you how to export data to new Excel file in ssis every time you run the ssis package so recently I got a question from one of my subscriber Anna and she was like is there a way to create a new Excel file every time I run a package project instead of rewriting it so I thought to make a video on this one so let's jump to the demo so I have SQL Server 2019 instance and in the test database I got a table currency table here so this table contains 2652 records and the table contains six columns in it okay and if I check the metadata of the table so the First Column ID it is of type integer and rest of the columns are of type worker okay but when we export the data to the Excel file then the Excel file supports the N worker instead of the worker now there are two options here either we can type cast the data to the and where care while selecting the data so we can do an explicit type conversion in the SQL server itself or another thing what we can do is that we can use a data conversion transformation in ssis and we can do the transformation there as well but for now what we will do we will do a conversion on the SQL Server itself so we will convert the first name from where current 50 to the NBA record 50 and similarly we will convert the last name from where 50 to the and very care 50 and because we want to make the destination file name as Dynamic and unique so what we will do we will append the current date time to the file name so for example if you want to create a file with the name as currency so what you can do we can append the current date time like 2023 0 1 0 0 3 and the current R and the current winners and the second as well okay so this way it will become a unique file name and whenever we will execute the ssis package every time it will create a new file and the package should not fail okay so that's how we can make the file as Dynamic and you tuning okay so we will export the data and we'll make a file at the D files location okay so we can make an ssis variable as folder path so in case if you want to export the data to another location then you can just change the folder path and the data should be exported to the new location okay so now instead of selecting the data like using this query select star from currency we can explicitly Typecast these columns and in case if in your Source table if the data type is anywhere then you don't need to Typecast it you can just select the data using this query like select star from currency otherwise what you can do you can right click on table script table as select to new query window editor so here what we need to do we need to Typecast these columns like first name last name IDs of type integer so that should be fine we just need to type cause the worker or care or the text columns okay so because these columns are the worker so we can type Customs so either we can use the cars function or we can use a convert function as well so I will use the cast function here cast first name as 50 as and then the column name okay so we have type casted the first name similarly we need to type as the last name email currency gender okay that's how it will be typecasted so I already written a query here type cast first name last name email currency and the general okay so what I can do I can copy this query from here right click click copy and now I can open the SSS package so this is my blank exercise package that I will be using today and because we want to export the data so we will be using a data flow task here so I can just drag and drop the data flow task into the control flow window and now I can configure the data flow task because our source is a SQL Server table so we can use the oledb source here so I can just drag and drop the oledb source into the data flow task now I can right click and configure the oledb source I can click new to create a new oledb Connection Manager we already have a connection manager to the test database onto the SQL Server to the 19 instance so I will select this Connection Manager and from data access mode I will select SQL command and then I can paste the query that I selected from the SQL Server management Studio I can click on the columns to see the list of available columns and then I can click on ok now because you want to export the data to the Excel file so we can just drag and drop the Excel destination into the data flow task and then we can connect the oledb source with the Excel destination you can right click on the Excel destination click edit what I can do I can actually go to the this location D files location and I can create an empty file here and I can call the file as currency dot xlsx okay and now I can go back to the ssis package and I can click new to create a new Excel Connection Manager and from the Excel version I will select the Microsoft Excel 2007 to 2010 you can select the appropriate version according to the Excel installation on your machine now I can browse the file so I can select this file currency Dot xlsx and I can click on open I can click on ok now I can click new to create a new Excel sheet so I can click on OK and this will generate the Excel create table statement okay so this is the create Excel sheet statement for creating a new sheet okay and what I can do I can call this particular Excel sheet as currency and I can copy this particular statement from here and I can place the statement here because we will be using this statement later in the ssis package now I can go back to the ssis package I can click on OK I can click on OK and from the name of the Excel sheet I will select the sheet name without the dollars so I created the currency Excel sheet so I will select the currency sheet without the dollar and now you can click on the mappings I can make sure that all input columns have been with the destination columns and I can click on OK so now if I will execute the SS package then it will select the data from the SQL Server table and it can export the data to the Excel file now if I click on the start button and if I try to run the SSS package then let's see okay so the data has been exported from the SQL Server table to the Excel file okay and I can stop the execution of the ssis package and if I go to the D files location so I can see a file here currency Dot xlsx and if I open this particular file then this should contain the data in the currency sheet so I can see the two sheets here sheet 1 and the currency sheet 1 is the default sheet so you can actually delete this sheet because this is one time task and now the data has been exported to the currency sheet okay and all data 2653 records have been exported and the first record is the Header information so 2652 records should be exported so this is fine now you can just save the changes to the Excel sheet and I can go back to the ssis package and now for example if I rerun the ssis package then it will export the data to the same file again okay so we need to make the Excel file path Dynamic so if you right click on the Excel Connection Manager and if you go to the properties so you can see a property here Excel file path so right now this is pointing to the D files currency dot xlsx okay so the Excel file name currency dot xlsx this is hard coded as of now so when you to make this particular Excel file name Dynamic so that every time when the ssis package will run it will export the data to the new file okay so what I can do I can go to the variables pin and I can just create two variables here the first variable I will create is is the folder path okay and the data type of the variable will be string and I can provide a value as D files okay so that every time it will export the data to the D files location so if you want to export the data to another location then you can just change the folder path here okay now I can create another ssis variable and I can call it as file name okay and the data type will be string and the value will be like something currency underscore 2023 0 1 0 3 and HH mm SS okay something like this dot XLS X okay the value will be something like this and then what I can do I can create another variable and I will call it as full file path okay and the data type will be string here and now I can concatenate the value from the file path with the file name okay so how I can do this I can just drag it off the folder path here plus sign backward slash two times plus sine and then the file name okay so if I click on evaluate expression so now this is the full file path so I can click on OK now what I need to do I need to get the value into the file name dynamically okay so how I can do that I can just close this one and I can just drag and drop the execute SQL task here and I can get the file name dynamically okay so I can write something like this now I can right click and click on edit I can make a connection to the SQL server and in a SQL statement I can write a select query here okay so I have already written a select query here so for example if I execute this query then this will return with the current date time okay and what I can do I can just append the file name here as well so I can write like currency and plus and then I can just execute this one currency underscore okay so I can append the underscore as well so now this will be the file name currency underscore this time this value so I can just simply copy this value from here and I can paste this value here click OK from the result set I will select the single row and then I can go to the result set I will set the result set name to 0 and I can assign this value to the file name ssis variable so that's fine so I can click on OK and now I can connect the execute SQL task with the data flow task okay so this is fine so now what I need to do I need to make the Excel Connection Manager Dynamic so right now the Excel Connection Manager is hard coded and it is pointing to the D files currency dot xlsx so I need to make this Connection Manager as Dynamic so how I can do that I can right click on The Connection Manager go to the properties and then I can click on Expressions click on these three dots from the property I will select the Excel file path and then I need to assign the value to the Excel file path from the full file path ssis variable if I click on evaluate expressions so this will return me the full file path of the Excel file so I can click on OK OK so this is failing because it is doing an authentication of the Excel file path and the Excel file path like this file path right now does not exist on the folder path okay so what we can do we can right click on the control flow go to the properties and we can set the delay validation to true so what this will do this won't authenticate the file before starting the execution of the package okay so I can just save the changes and now what I need to do that I can just delete this particular connection and I can just drag and drop the execute SQL task here so you will actually create a Excel file before loading the data to the Excel sheet so I can connect the first execute SQL task with another one and I will call this modular execute SQL task as create Excel sheet okay and I can right click and configure this one from the connection type I will select the Excel connection and from the connection I will select the Excel Connection Manager under SQL statement I can actually copy this particular create Excel files statement from here and I can paste it here so this will create the currency Excel sheet now I can click on OK and I can click on ok now I can connect the create Excel sheet execute SQL task with the data flow task and now RSS package is ready to be executed if I go to the D files location so at the moment I only have one file currency dot xlsx okay so I can click on start button and this should export the data from the SQL Server table to the Excel file so the process ran fine in the first task we got the file name dynamically and then we created the Excel sheet name into the new Excel file and then we exported the data okay so if I go to the D files location and if I refresh the files so I can see a file here with the current date time and I can open this particular file to see what it contains so it contains the currency sheet and the data from the currency SQL Server table has been exported 2652 records have been exported and if I go to the end of the file so yeah 2652 records have been exported yeah so this worked great and for example if I rerun the ssis package then a new Excel file should be created again so none of the data should be overwritten okay so I can just stop the execution of the package and I can rerun the ssis package and it should export the data second time as well without an issue yeah so the package ran fine and if I go to the D files location and if I refresh the file so I can see a new file the first file got created is 625 and the second file got created at the 626 okay yeah so this is working fine and if I open the second sheet as well then it should also contain the same data yeah so it contains the data it contains the same 2652 records yeah so this is working perfectly fine and I will share all the code with you whatever code I used like maybe this particular code and like the code to create the Excel file and I can also share the create table statement for currency table and the insert queries for inserting the 2652 records to the currency table so that you can use the same code and you can just try on your system and once it works then you can try exporting some other data as well so I think that's it for today's video thank you guys for watching the video and if you like the video then please click the like button do subscribe to our Channel press the Bell icon and click on also that you'll be notified every time I upload a new video thank you so much

Channel: Learn SSIS

Views: 13,080

Keywords: How to export data to new excel file every time in ssis, How to dynamically create Excel file in SSIS?, How do I export data from SSIS package to Excel?, How do I overwrite data in Excel destination in SSIS?, How do I create an Excel file with date time on each package execution in SSIS package?, How, how, to, export, data, new, excel, file, every, time, in, ssis, do, create, Excel file, SSIS, learn ssis, sql server integration services, msbi

Id: OkSOBRaTq74

Length: 14min 35sec (875 seconds)

Published: Wed Jan 04 2023