I am trying to import data from an external source(website) to my local server database, do you have any advice? I am using visual studio 2010 on a windows server 2012R2.
Hi Aqil, Is there any specific reason to move the file to other location using File system task. Instead of File system task we can use DFT to load data right?
Yes you are right, we don't need to move the files using File System task. Same thing we can do using foreach loop container with ado enumerator to loop through multiple locations, as in this case the source files were placed at 2 different drives. So first foreach loop container can loop through multiple drives, and then second foreach loop container which will be placed inside the first loop container will be of type file enumerator and it can load the file from a drive to the sql server table. So instead of using file system task we can use data flow task.
Thank you. I had a small doubt. Pardon me if this sounds silly. In the DestFilePath you have set the value as the sample_data.csv. But the actual files have something more appended in the file name in c drive. Won't that create an issue while copying the file?
Hi Aquil, Thanks for making incredible session on SSIS. I want to knwo if there is a way we can import multiple csv files with diffrent schema into respective database tables. I dont want to use script activity(C#) for this. Thanks in advance
Thanks Imran for your comment. At the moment SSIS does not provide a way without using C# to import multiple files with different schema using a single data flow task.
Every package is different and thus deadlock in each package can be because of different reasons. I have some SSIS packages, some of the packages pulls data from a table and some updates data to the same table, thus sometimes deadlock can occur. Thus we need to make sure that no 2 processes are trying to update\insert\delete data to the same table at a time, and while reading data from the table use with (nolock) hint. These things can avoid the dead lock scenarios.
I am getting an error while executing package through sql server agent job. It is showing error that not able to acquired connection with default value that i passed in variable. It is not picking dynamic value in runtime. How to resolve this issue.
Can you set the delay validation property of ssis package to true, save the package and try again. Also make sure that the owner of the SQL services should have full access on the machine.
If you want to load only specific rows from csv file to sql server according to a date column, then you can use a conditional split transformation in the data flow and according to the values of dates, you can insert only specific data to sql server table. Once you process a file you can move the file to an archive folder. In Below video I have shown how to move a file to archive folder. ua-cam.com/video/RVRAuFWrMcE/v-deo.html If you can explain your case in detail then I can suggest you more.
Hi Aqil, Can please the upload videos same but file does not copy from other directory to move into common folder because each and every day new files comming we need track and file should be excel format. I am working real time on this please help me Thanks in Advance !!
Hi Sathiya, as soon as you load a file, you can move the file to an archive folder. I have shown in below video how to archive files. ua-cam.com/video/RVRAuFWrMcE/v-deo.html
Hi friend.. in server source any table in dynamic.. destination also dynamic table , insert any table but final data will came dynamic.. it's possible or not
There are 2 options I can think of, 1. Create a linked server and then execute the query like below select * into destTable from linkedservername.databasename.dbo.SourcetableName Now in above query you can dynamically pass the value of destTable and SourcetableName. 2. You would need to use C# code to implement this. I have not created this thing so far, its not easy but this can be done. In below video they have done second half of the task like creating the tables dynamically. ua-cam.com/video/lfGS7CVlMB4/v-deo.html
HI Aqil...I have requirement like need to build a ssis pacage from multiple excel files to sql tables based on one column values from excel files .........Load data to multiple company tables from excel .........Note the table has to be created dynamically if not available and to be truncated before loading (Table name example infosys(from Companyname column))
Hi Kalleshi, if you want the sql table to be created dynamically based on the value from the excel file, then first you would need to load the data from excel to a staging sql table. And then get the distinct company name in an Object variable, and declare an SSIS variable TableName as well and assign the value to that variable in the foreach loop container ado enumerator and then in the foreach loop, create the table if table not exists, otherwise truncate the table like below IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MyTable')) truncate table MyTable else create table MyTable(column list goes here) This query needs to be written in an execute sql task in expression, in the sql statement source, and table name needs to be passed from an SSIS variable TableName. Thanks.
Hi Paras, to get the data from API source you would need to write the code for example in C#, thus I would recommend if you are familiar with C#, then write the api code to pull the data in C# console app, test it and then you can put the same code in either C# script component or C# script task. Thanks.
Hi, you need to make an OLE DB connection for source server and then may be you can make the connection dynamic by providing the values of ServerName and DatabaseName from SSIS variables, so that if you change the value of ServerName or DatabaseName then the Source connection will change, and finally make an OLE DB Connection for database destination and insert the data into destination table. I think I should make a video on it how we can achieve it, I will make a video on it this week and will let you know.
Thanks, I was not aware about that there is "execute t-sql task" in SSIS, but it seems there is one and below are the main differences between two 1. And it seems that the execute t-sql task can only accept ado.net connection while the execute sql task can accept other connection types as well like oledb connection as well as ado.net connection. 2. We can't use parameterized queries in execute t-sql task but we can use it in an execute sql task. 3. execute t-sql task can't return the values to ssis variables, while execute sql task can return the values to ssis variables.
Hi Aqil,
Thanks a lot for your video!!
You are welcome Sathiya.
I am trying to import data from an external source(website) to my local server database, do you have any advice? I am using visual studio 2010 on a windows server 2012R2.
Sorry I don't have experience working with importing data from web sites.
Hi Aqil,
Is there any specific reason to move the file to other location using File system task.
Instead of File system task we can use DFT to load data right?
Yes you are right, we don't need to move the files using File System task.
Same thing we can do using foreach loop container with ado enumerator to loop through multiple locations, as in this case the source files were placed at 2 different drives. So first foreach loop container can loop through multiple drives, and then second foreach loop container which will be placed inside the first loop container will be of type file enumerator and it can load the file from a drive to the sql server table.
So instead of using file system task we can use data flow task.
How Can I load a multi-tab excel file into multiple tables by matching the table names with tabs name present in ExcelSheet. Please help Sir!
Can you take a look at this video
ua-cam.com/video/YWqO9KFTQCo/v-deo.html
@@learnssis Thank you So much, Sir!!
Thank you. I had a small doubt. Pardon me if this sounds silly. In the DestFilePath you have set the value as the sample_data.csv. But the actual files have something more appended in the file name in c drive. Won't that create an issue while copying the file?
No problem Deepali. The value I have given it will be over written during run time, thus it won't make any issue.
How will you improve the performance tuning if data is huge?
Hi Aquil,
Thanks for making incredible session on SSIS.
I want to knwo if there is a way we can import multiple csv files with diffrent schema into respective database tables. I dont want to use script activity(C#) for this.
Thanks in advance
Thanks Imran for your comment. At the moment SSIS does not provide a way without using C# to import multiple files with different schema using a single data flow task.
How you will add column to the exists slowly changing dimension
How to resolve if dead lock happens in ssis. If the package will run for 30 mins due to dead lock it's running from one hr.
Every package is different and thus deadlock in each package can be because of different reasons. I have some SSIS packages, some of the packages pulls data from a table and some updates data to the same table, thus sometimes deadlock can occur.
Thus we need to make sure that no 2 processes are trying to update\insert\delete data to the same table at a time, and while reading data from the table use with (nolock) hint. These things can avoid the dead lock scenarios.
@@learnssis there is no need to take care in package level
Can you do how will send the data to sql server if a source is API.
Sure, I will make a video on this topic.
I am getting an error while executing package through sql server agent job. It is showing error that not able to acquired connection with default value that i passed in variable. It is not picking dynamic value in runtime. How to resolve this issue.
Can you set the delay validation property of ssis package to true, save the package and try again. Also make sure that the owner of the SQL services should have full access on the machine.
Hi, I want a incremental load base on date column. Daily basis take csv file and load only T-1 date into sql server table
If you want to load only specific rows from csv file to sql server according to a date column, then you can use a conditional split transformation in the data flow and according to the values of dates, you can insert only specific data to sql server table. Once you process a file you can move the file to an archive folder. In Below video I have shown how to move a file to archive folder.
ua-cam.com/video/RVRAuFWrMcE/v-deo.html
If you can explain your case in detail then I can suggest you more.
Hi Aqil,
Can please the upload videos same but file does not copy from other directory to move into common folder because each and every day new files comming we need track and file should be excel format.
I am working real time on this please help me
Thanks in Advance !!
Hi Sathiya, as soon as you load a file, you can move the file to an archive folder. I have shown in below video how to archive files.
ua-cam.com/video/RVRAuFWrMcE/v-deo.html
Hi friend.. in server source any table in dynamic.. destination also dynamic table , insert any table but final data will came dynamic.. it's possible or not
There are 2 options I can think of,
1. Create a linked server and then execute the query like below
select * into destTable from linkedservername.databasename.dbo.SourcetableName
Now in above query you can dynamically pass the value of destTable and SourcetableName.
2. You would need to use C# code to implement this. I have not created this thing so far, its not easy but this can be done.
In below video they have done second half of the task like creating the tables dynamically.
ua-cam.com/video/lfGS7CVlMB4/v-deo.html
Hi Aquil, In the Data flow task what will need to configure the Source to the destination?
What is your requirement ?
How to save several files from database to folders through ssis?
Take a look at this video, I have shown same thing here
ua-cam.com/video/I7em3qfJ0N4/v-deo.html
How to import files from different file formats into SQL using SSIS?
Can you precise your question ? From which format you want to import it from ?
HI Aqil...I have requirement like need to build a ssis pacage from multiple excel files to sql tables based on one column values from excel files .........Load data to multiple company tables from excel .........Note the table has to be created dynamically if not available and to be truncated before loading (Table name example infosys(from Companyname column))
Hi Kalleshi, if you want the sql table to be created dynamically based on the value from the excel file, then first you would need to load the data from excel to a staging sql table. And then get the distinct company name in an Object variable, and declare an SSIS variable TableName as well and assign the value to that variable in the foreach loop container ado enumerator and then in the foreach loop, create the table if table not exists, otherwise truncate the table like below
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'MyTable'))
truncate table MyTable
else
create table MyTable(column list goes here)
This query needs to be written in an execute sql task in expression, in the sql statement source, and table name needs to be passed from an SSIS variable TableName. Thanks.
@@learnssis Thank you so much Aqil for quick response, it is working now as you suggested
@@prajaakeeyaparty6578 Glad it worked for you.
@@learnssis Thank you Aqil
@@prajaakeeyaparty6578 You are welcome.
Hi, is there any option in SSIS to get data from API source? If yes, then could you please let me know the steps.
Thank You
Hi Paras, to get the data from API source you would need to write the code for example in C#, thus I would recommend if you are familiar with C#, then write the api code to pull the data in C# console app, test it and then you can put the same code in either C# script component or C# script task. Thanks.
how to import tables from different server to one database destination
Hi, you need to make an OLE DB connection for source server and then may be you can make the connection dynamic by providing the values of ServerName and DatabaseName from SSIS variables, so that if you change the value of ServerName or DatabaseName then the Source connection will change, and finally make an OLE DB Connection for database destination and insert the data into destination table. I think I should make a video on it how we can achieve it, I will make a video on it this week and will let you know.
@@learnssis thanx you for your efforts and your reply, and I will wait your next vedio♥️ and this is my WhatsApp num 01002818537
Executive sql Task vs executive t-sql
Thanks, I was not aware about that there is "execute t-sql task" in SSIS, but it seems there is one and below are the main differences between two
1. And it seems that the execute t-sql task can only accept ado.net connection while the execute sql task can accept other connection types as well like oledb connection as well as ado.net connection.
2. We can't use parameterized queries in execute t-sql task but we can use it in an execute sql task.
3. execute t-sql task can't return the values to ssis variables, while execute sql task can return the values to ssis variables.
Hi
Hello Pavan, How are you doing ?
@@learnssis
I am good and u
@@pavan_YRCP Thanks I am good.
Really good sir 😀👍
Thank you so much Aqil sir 🔥
You are most welcome Naveen Sir.