For seasoned developers looking for a quick tutorial, this is wonderful. Concise and to the point still packed with lots of information. Great Video. Thanks.
Hi Thanks for the video, I have tried to create table excel sheet using SQL task and load do loading. But I always get error on the connection manager not set properly or permission error. Anyway to fix this?
Hi, Here i am trying to connect sql server to excel sheet while i created a excel file and click new to create table but nothing is happened and why any idea
Hi Ahmad, I'm creating a package with Excel destination. When I tap 'New' to create a table for the sheet name, the Excel destination editor closes automatically and takes me to the data flow console.. Any idea what could be the reason?
In the above video my VS Code got executed even though i have runtime 64 bit operating system what would be the reson for this when i tried to export data from SQL Server to Excel
Hi Ahmed, how you are converting OLE db to excel without using the data convertion, i have been recieving the (Column "ENAME" cannot convert between unicode and non-unicode string data types) error.
Thank you so much for sharing this SSIS video! It's been incredibly helpful for my data integration projects. I do have a question, though. I'm working with really large datasets, Is there a way to split the data into multiple tabs based on the number of rows in SSIS? I'd love to know how to tackle this issue. Any guidance or tips would be greatly appreciated! Thanks again for your informative content.
I think this will be a nice scenario to handle and show in a video, I will try to write code for this one and will share it with you as soon as it will be available today. Probably what we can do that we can make multiple parts of a table using ntile, like 5 part or 10 part and create an staging table along with id from original table and new column part. Now we can join this new table based on id with our existing table and select data for part 1, this part1 data will go to sheet1, then part 2 data will go to sheet 2 and so on.
Thank you for this solution! I think it would make a fantastic video if you could explain this topic with various scenarios. I'm definitely going to give this solution a try. Looking forward to more content like this.
@@SuccessPradhan-h2nIn this video I used to C# to populate the data, I am thinking now to use data flow task to populate the data instead of C#. We can use C# to create the excel sheet, I will see if I will be able to make another video on this one.
But how to automate it? Because it works like you do if you run it under Data Tools but when I deploy the package it doesn't work, could you please do the same but with an automated package that can run periodically?
Take a look at this video, here you can create the ssis package that can be executed on a given schedule. Make the package, deploy it and then schedule it. th-cam.com/video/y5kvWjVNVfc/w-d-xo.html
@@learnssis no, this one is about using an Excel archive template. On the other hand there's a problem with int numeric columns, they're taking like char I think
@@MariadelCarmen-gj8ul If you are going to export data to excel file then in SSIS there are only 2 options available either make use of template file or delete and recreate excel sheet, otherwise it will end of adding duplicate data to the same excel file th-cam.com/video/NPYxOpS-kLg/w-d-xo.html
@@learnssis yes but I'm talking about not doing it opening the Data Tools but trough some other way than can automate it, automate means that the server does it everyday under a schedule
@@MariadelCarmen-gj8ul Yeah once the ssis package is ready then you can deploy it and schedule it like below, then it will be automatically executed based on the given schedule th-cam.com/video/VbxeM1K8LAU/w-d-xo.html
Hi, When I exported a column with datatype money to excel, I see error "The number in this cell is formatted as text or preceded by an apostrophe." How can I get rid of this error? If I want to do calculation(example: addition of all the row values of that column) for that column in excel it doesnt work because it is formatted as text. How can i make sure that when SSIS generates the data in excel, we dont have the error and user is able to do his or or calculation(example: addition) of that fields. Thanks for your help
Can you add a data conversion transformation before excel destination and typecast that column to money and map the new column to the excel destination.
@@learnssis Thanks for the response. It is already coming as currency(DT_CY) data type. But I still went ahead and added data conversion transformation and added the conversion as you suggested. I then mapped to new column. But it didnt work. IT populates data in the Excel with same error. I noticed when I right click Excel Destination Editor and click on Advanced editor, I saw that under External Columns, the data type is Unicode string [DT_WSTR] and on Input columns, the data type is DT_CY. If I change Unicode string[DT_WSTR] to DT_CY, it resets itself to Unicode string[DT_WSTR] . Not sure why the data type is not changing to DT_CY after I change it in External columns.
@@rockuu316 In the excel connection manager Can you check if IMEX=1 is written there, if yes then remove it. You can check this video in this video we are doing just reverse we are adding IMEX=1 if we need the data to be stored as text in the excel file. th-cam.com/video/i4zE9yP2grA/w-d-xo.html
Hi , When I tried to get the numerical data from SQL to Excel , the Excel copies as alfa-numeric , which stops me do further calculations. Any idea why there is a data type mis-match. Thanks,
This is one of the known issue with Excel it tries detect and set the data type for the data it has stored. When you are creating the excel sheet make sure you select the numeric data type.
@@learnssis I'm trying to automate the report, the ssis package will copy the dataset and inputs in Excel sheet for pivot refresh. Have created button to Refresh, as the datatype is not numeric the pivot fails to refresh.
Hi What if I want to schedule a daily job to export data from a stored query in disk to a excel file everyday.. How can i do it? Would be really great if you could give your thoughts on this..Thank you
Hi, with excel you have very little options available. May be we might need to write some C# code to do this. I have never done this kind of thing and searched on net and did not find anything which can do your job.
Hi Aqil, thanks for the wonderful tutorials. I followed your method it's working fine when I run on the Visual Studio but when I deploy it on the server and execute it through the SQL Server Management Studio it ends up with "Unexpected Termination". there is no other error can you please guide why it's throwing "Unexpected Termination"? thanks
You might be getting the detailed error messages in SSIS catalog. There can be multiple reasons for the failure for this. 1. Set the delay validation property of SSIS package to true. 2. Do you have excel or the drivers installed for excel on server ? you can try to install below drivers www.microsoft.com/en-za/download/details.aspx?id=13255 3. Check the access for sql services owner if it has the proper access on the sql server and to the excel file. th-cam.com/video/fIhkb3P3Jss/w-d-xo.html
Hi .. How to maintain the columns of Excel destination as such and load data from SQL , is it possible? For ex. Lets say Col A is formated with Accounting . How to load the values in it with same type. I Can see only the header remains the expected datatype not the data.
Hi, if you are using Excel destination to write the data into an excel file then column name and data type should not change. Although if you want to change the column name or data type then there are some videos on youtube where they have used C# script task to load the data dynamically into excel files. May be you can take a look at them.
Frankly speaking I have never done that, but I have seen some SSIS packages where they used ODBC data source for Oracle to read the data from Oracle and inserted it to sql server, you can try to download those ODBC drivers.
1. Sheet without $ (Normal name): This refers to a regular worksheet within the Excel workbook. For example, if you name your sheet "SalesData", it will appear in Excel without a dollar sign ($). 2. Sheet with $: This typically refers to a named range or a specific type of Excel object that can be accessed through a special syntax in Excel formulas or through other Excel features. For example, if you name your sheet "SalesData$" or "SalesData$A1 ", it could represent a named range (SalesData) or a structured reference within the workbook.
For seasoned developers looking for a quick tutorial, this is wonderful. Concise and to the point still packed with lots of information. Great Video. Thanks.
Thank you Ramesh.
Very concise to the point and good video length.
Thank you.
Great video like always. Concise and to the point. Thank you
Thank you Catusae.
Good video on the subject. To the point, great videos on SSIS topics.
Thanks for your comments, it means a lot to me.
@@learnssis Thank you.
Hi Thanks for the video, I have tried to create table excel sheet using SQL task and load do loading. But I always get error on the connection manager not set properly or permission error. Anyway to fix this?
Hi,
Here i am trying to connect sql server to excel sheet while i created a excel file and click new to create table but nothing is happened and why any idea
Are you able to import any excel file to sql using SSIS ?
Hi Ahmad, I'm creating a package with Excel destination. When I tap 'New' to create a table for the sheet name, the Excel destination editor closes automatically and takes me to the data flow console.. Any idea what could be the reason?
Are you able to import the excel file to sql server ? If no then you would the drivers to read\write to excel files.
In the above video my VS Code got executed even though i have runtime 64 bit operating system what would be the reson for this when i tried to export data from SQL Server to Excel
Hi Ahmed, how you are converting OLE db to excel without using the data convertion, i have been recieving the (Column "ENAME" cannot convert between unicode and non-unicode string data types) error.
In my source table the column data type might be nvarchar, so that's why it is not asking to convert. You can use the data conversion if you need to.
Thank you so much for sharing this SSIS video! It's been incredibly helpful for my data integration projects.
I do have a question, though. I'm working with really large datasets, Is there a way to split the data into multiple tabs based on the number of rows in SSIS?
I'd love to know how to tackle this issue. Any guidance or tips would be greatly appreciated!
Thanks again for your informative content.
I think this will be a nice scenario to handle and show in a video, I will try to write code for this one and will share it with you as soon as it will be available today. Probably what we can do that we can make multiple parts of a table using ntile, like 5 part or 10 part and create an staging table along with id from original table and new column part. Now we can join this new table based on id with our existing table and select data for part 1, this part1 data will go to sheet1, then part 2 data will go to sheet 2 and so on.
This is the code to achieve it. Suppose your table name is Email and unique column name is Id.
First of all execute this code on sql database. we will make an staging table named temp_Email_staging. Suppose you want to divide the data into 5 parts
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'temp_Email_staging'))
drop table temp_Email_staging
go
select Id, ntile(5) over(order by id ) as Part into temp_Email_staging from Email
go
create clustered index ix_id on temp_Email_staging(id)
go
create index ix_part on temp_Email_staging(part)
go
-------
Now below is the C# code which can fetch data from sql table and can generate the excel sheet containing data into 5 sheets.
public static string convertQuotes(string str)
{
return str.Replace("'", "''");
}
static void Main(string[] args)
{
string currentdatetime = DateTime.Now.ToString("yyyyMMddHHmmss");
string LogFolder = @"D:\Files\Logs";
OleDbConnection Excel_OLE_Connection = null;
OleDbCommand Excel_OLE_Command = null;
SqlConnection sqlCon = null;
try
{
//Provide source table list here
int maxLoopCounter = 5;
string TableName = "Email";
string SheetName = "";
//Provide Excel file path here without extension
string DestfileName = @"D:\Files\Customer";
if (File.Exists(DestfileName + ".XLSX"))
{
File.Delete(DestfileName + ".XLSX");
}
string connectionString = @"Server = DESKTOP-EKJ1P64\SQL2019; Database = Work; Trusted_Connection = True; ";
SqlCommand sqlCmd;
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DestfileName
+ ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
for (int i = 1; i < maxLoopCounter+1; i++)
{
SheetName = "Sheet" + i.ToString();
string CreateTableQuery = "Create Table [" + SheetName + "] (";
string columnList = "";
string Columns = "";
sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
sqlCmd = new SqlCommand(@"SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + TableName + "'", sqlCon);
using (SqlDataReader rdr = sqlCmd.ExecuteReader())
{
int j = 0;
while (rdr.Read())
{
var myString = rdr.GetString(0);
if (j == 0)
{
columnList = "[" + myString + "] Text,";
Columns = "[" + myString + "],";
}
else
{
columnList = columnList + " [" + myString + "] Text,";
Columns = Columns + " [" + myString + "],";
}
j++;
}
columnList = columnList.Remove(columnList.Length - 1);
Columns = Columns.Remove(Columns.Length - 1);
}
CreateTableQuery = CreateTableQuery + columnList + ")";
Excel_OLE_Connection = new OleDbConnection();
Excel_OLE_Command = new OleDbCommand();
Excel_OLE_Connection.ConnectionString = connstring;
Excel_OLE_Connection.Open();
Excel_OLE_Command.Connection = Excel_OLE_Connection;
Excel_OLE_Command.CommandText = CreateTableQuery;
Excel_OLE_Command.ExecuteNonQuery();
sqlCmd = new SqlCommand(@"select a.* from " + TableName + " a inner join temp_" + TableName + "_staging b on a.id = b.id where b.part= " + i + "", sqlCon);
string insert_query = "Insert into [" + SheetName + "] ( " + Columns + " ) VALUES(";
using (SqlDataReader rdr = sqlCmd.ExecuteReader())
{
while (rdr.Read())
{
string myString = "";
string insert_query2 = insert_query;
for (var b = 0; b < rdr.FieldCount; b++)
{
myString = rdr[b].ToString();
myString = convertQuotes(myString);
insert_query2 = insert_query2 + "'" + myString + "',";
}
insert_query2 = insert_query2.Remove(insert_query2.Length - 1);
insert_query2 = insert_query2 + ")";
Excel_OLE_Command.CommandText = insert_query2;
Excel_OLE_Command.ExecuteNonQuery();
}
}
}
Excel_OLE_Connection.Close();
sqlCon.Close();
}
catch (Exception exception)
{
using (StreamWriter sw = File.CreateText(LogFolder + "\\" + "ErrorLog_" + currentdatetime + ".log"))
{
sw.WriteLine(exception.ToString());
}
}
}
Thank you for this solution!
I think it would make a fantastic video if you could explain this topic with various scenarios. I'm definitely going to give this solution a try.
Looking forward to more content like this.
@@SuccessPradhan-h2nIn this video I used to C# to populate the data, I am thinking now to use data flow task to populate the data instead of C#. We can use C# to create the excel sheet, I will see if I will be able to make another video on this one.
But how to automate it? Because it works like you do if you run it under Data Tools but when I deploy the package it doesn't work, could you please do the same but with an automated package that can run periodically?
Take a look at this video, here you can create the ssis package that can be executed on a given schedule. Make the package, deploy it and then schedule it.
th-cam.com/video/y5kvWjVNVfc/w-d-xo.html
@@learnssis no, this one is about using an Excel archive template. On the other hand there's a problem with int numeric columns, they're taking like char I think
@@MariadelCarmen-gj8ul If you are going to export data to excel file then in SSIS there are only 2 options available either make use of template file or delete and recreate excel sheet, otherwise it will end of adding duplicate data to the same excel file
th-cam.com/video/NPYxOpS-kLg/w-d-xo.html
@@learnssis yes but I'm talking about not doing it opening the Data Tools but trough some other way than can automate it, automate means that the server does it everyday under a schedule
@@MariadelCarmen-gj8ul Yeah once the ssis package is ready then you can deploy it and schedule it like below, then it will be automatically executed based on the given schedule
th-cam.com/video/VbxeM1K8LAU/w-d-xo.html
Hi, When I exported a column with datatype money to excel, I see error "The number in this cell is formatted as text or preceded by an apostrophe." How can I get rid of this error? If I want to do calculation(example: addition of all the row values of that column) for that column in excel it doesnt work because it is formatted as text. How can i make sure that when SSIS generates the data in excel, we dont have the error and user is able to do his or or calculation(example: addition) of that fields. Thanks for your help
Can you add a data conversion transformation before excel destination and typecast that column to money and map the new column to the excel destination.
@@learnssis Thanks for the response. It is already coming as currency(DT_CY) data type. But I still went ahead and added data conversion transformation and added the conversion as you suggested. I then mapped to new column. But it didnt work. IT populates data in the Excel with same error. I noticed when I right click Excel Destination Editor and click on Advanced editor, I saw that under External Columns, the data type is Unicode string [DT_WSTR] and on Input columns, the data type is DT_CY. If I change Unicode string[DT_WSTR] to DT_CY, it resets itself to Unicode string[DT_WSTR] . Not sure why the data type is not changing to DT_CY after I change it in External columns.
And I forgot to mention that I am using template excel file that has prepopulated header columns.
@@rockuu316 In the excel connection manager Can you check if IMEX=1 is written there, if yes then remove it. You can check this video in this video we are doing just reverse we are adding IMEX=1 if we need the data to be stored as text in the excel file.
th-cam.com/video/i4zE9yP2grA/w-d-xo.html
@@learnssis Thanks. I got the solution. I need to add Macros to convert text to numbers and use .xlsm instead of .xlsx excel file format.
Hi , When I tried to get the numerical data from SQL to Excel , the Excel copies as alfa-numeric , which stops me do further calculations.
Any idea why there is a data type mis-match.
Thanks,
This is one of the known issue with Excel it tries detect and set the data type for the data it has stored. When you are creating the excel sheet make sure you select the numeric data type.
@@learnssis I'm trying to automate the report, the ssis package will copy the dataset and inputs in Excel sheet for pivot refresh. Have created button to Refresh, as the datatype is not numeric the pivot fails to refresh.
Hi
What if I want to schedule a daily job to export data from a stored query in disk to a excel file everyday.. How can i do it?
Would be really great if you could give your thoughts on this..Thank you
Hi, with excel you have very little options available. May be we might need to write some C# code to do this. I have never done this kind of thing and searched on net and did not find anything which can do your job.
Hi Aqil, thanks for the wonderful tutorials. I followed your method it's working fine when I run on the Visual Studio but when I deploy it on the server and execute it through the SQL Server Management Studio it ends up with "Unexpected Termination". there is no other error can you please guide why it's throwing "Unexpected Termination"? thanks
You might be getting the detailed error messages in SSIS catalog. There can be multiple reasons for the failure for this.
1. Set the delay validation property of SSIS package to true.
2. Do you have excel or the drivers installed for excel on server ? you can try to install below drivers
www.microsoft.com/en-za/download/details.aspx?id=13255
3. Check the access for sql services owner if it has the proper access on the sql server and to the excel file.
th-cam.com/video/fIhkb3P3Jss/w-d-xo.html
@@learnssis thanks for the prompt response I will check with all these points. thanks
Hi .. How to maintain the columns of Excel destination as such and load data from SQL , is it possible? For ex. Lets say Col A is formated with Accounting . How to load the values in it with same type. I Can see only the header remains the expected datatype not the data.
Hi, if you are using Excel destination to write the data into an excel file then column name and data type should not change. Although if you want to change the column name or data type then there are some videos on youtube where they have used C# script task to load the data dynamically into excel files. May be you can take a look at them.
How can take data from oracle source using ssis .?
Frankly speaking I have never done that, but I have seen some SSIS packages where they used ODBC data source for Oracle to read the data from Oracle and inserted it to sql server, you can try to download those ODBC drivers.
What is person&
1. Sheet without $ (Normal name): This refers to a regular worksheet within the Excel workbook. For example, if you name your sheet "SalesData", it will appear in Excel without a dollar sign ($).
2. Sheet with $: This typically refers to a named range or a specific type of Excel object that can be accessed through a special syntax in Excel formulas or through other Excel features. For example, if you name your sheet "SalesData$" or "SalesData$A1
", it could represent a named range (SalesData) or a structured reference within the workbook.