28 Data Conversion Transformation in SSIS

Data Conversion Transformation in SSIS | How to use the Data Conversion Transformation
Download the file\script used in the Video from below link
drive.google.c...
SSIS Tutorials: • SSIS Tutorials
SSIS real time scenarios examples: • SSIS real time scenari...
SSIS Interview questions and answers: • SSIS Interview questio...
Data Conversion Transformation in SSIS
How to use the Data Conversion Transformation?
How do we convert data type in SSIS?
What is data conversion in ETL?
What are two basic data conversion types?
What is derived column transformation in SSIS?
Happy Learning.
If you have any questions or suggestions please comment on the video or write to me at “[e-mail]”

[@tizitaferede6869]

Hi Aqil, you made learning so simple because you shared your knowledge from the bottom of your heart. I will buy more courses from your website. Thank you!

[@learnssis]

Thanks so much for your kind words. Its good to know that someone finds it useful. Thanks.

[@finalcrusade777]

Thank you so much sir..Got the output..thank you so much, your video and guidence helped me a lot..GBU.

[@learnssis]

Thank you Pavan Babu, its nice to hear from you.

[@finalcrusade777]

@@learnssis i should say thank you sir..🙏

[@learnssis]

@@finalcrusade777 No problem. It was all your effort, keep going.

[@milanbarot873]

Great work Aqil sir. You rock

[@learnssis]

Thank you Sir.

[@saiabhilash3151]

Thanks for the video .Could you please make a video for Excel error stating Microsoft oledb 12.0 driver not installed something like that .Not able to load Excel sheet in Excel source .changed property to 32 bit ,installed Microsoft access driver for 32 bit .But still not working .

[@hafizsameerali1265]

how to close my source file CSV, Excel Or txt before inserting data to OLEDB destination ??

[@learnssis]

You can use this code inside Script task before loading data. This will close any instance of excel file or flat\csv file if those files are opened in notepad using System; using System.Diagnostics; --------------------- public void Main() { CloseProcessesByName("EXCEL"); // If CSV is open in Excel CloseProcessesByName("notepad"); // If CSV is open in Notepad Dts.TaskResult = (int)ScriptResults.Success; } static void CloseProcessesByName(string processName) { try { // Get all processes with the given name Process[] processes = Process.GetProcessesByName(processName); foreach (Process process in processes) { Console.WriteLine($"Closing process: {process.ProcessName}, ID: {process.Id}"); process.Kill(); // Force close the process process.WaitForExit(); // Wait until the process is terminated } Console.WriteLine($"All {processName} processes have been closed."); } catch (Exception ex) { Console.WriteLine($"Error while closing processes: {ex.Message}"); } }

[@Shirazshaik-y1o]

Hi,is data conversion transformation is only done by Excel source ? Can u make a video through Flat file souce to oledb destination

[@learnssis]

Data conversion can be applied to any source. You just need to delete the excel source and put flat file source there and you can still use the data conversion transformation to transform the column to a specific data type.

[@sirajshaik8495]

Thq

[@rejaulislamroyel216]

You are life saver. Thanks man

[@learnssis]

Thank you.

[@mychannel-fb9ro]

your videos are very very usefull.. amazing presentation

[@learnssis]

Thank you so much.

[@shamaangel5629]

So helpful and clear understanding this videos are

[@learnssis]

Thank you so much Shama Angel for your kind words.

[@konradlubaski7830]

man you are the best

[@learnssis]

Thanks for your kind words. Glad to know that you found the video useful.

[@mohinisharma6945]

Keep growing your videos are really helpful

[@learnssis]

Thanks Mohini.

[@alexandercoss]

Hi, I don't have the data conversion tool, how do I add it to the tools bar? Thanks

[@learnssis]

It should be there, search data conversion in the tool box when you are inside a data flow task.

[@alexandercoss]

@@learnssis Thanks for your quick response. I am using VS2019, but for some reason I can't find it. Additionally, I am using the community version. Is that Ok?

[@learnssis]

@@alexandercoss Yeah Community version is fine, ideally it should be there if it is not there, then may be do a uninstall and install again.

[@skcwebworld]

Sir, I am transferring data from MS SQL to MSSQL where datatype of columns in NVARCHAR(MAX) and VARCHAR(MAX). It is not transferring due to data conversion issue.

[@learnssis]

You can use data conversion transformation in this case. I have shown in this example how to use the data conversion transformation.

[@tejasbakade7852]

In source we having data as Y and N, and in target we need 1 and 0. What etl process we have to do and how to do?

[@learnssis]

There are multiple ways to do this. Either you can do this using Script component using C# or the easiest method will be that you can use a Derived column transformation and then there are further 2 ways to do this, one way will be that you can simply use a Replace function like below Replace(Replace(columnname, "Y","1"),"N","0") and replace the values of existing column. Another method will be that you can use a if else condition in derived column. I have shown how to use Replace function in below video kzbin.info/www/bejne/qGqrpH2fjdOFbs0

[@ksh-v8x]

Good video! Hey 👋 I have a question regarding SQL Command transformation - I am using update query for incremental load to overwrite existing record while doing that I have to use parameters using ? As you might know but I have large number of columns 200+ so manual mapping of column is a tedious job can you suggest if this can be done ✅ programmatically Thx

[@learnssis]

I would strongly recommend to replace OLE DB Command by inserting the records to be updated in a staging table and then run an update query in the Execute SQL Task, it will be a lot faster as compare to OLE DB Command transformation.

[@ksh-v8x]

Ok I will try that Thx

[@dileepdillu666]

thank you very much.. it's really very helpful

[@learnssis]

Glad it helped you.

[@pravindatir8097]

You are doing very well Useful thanks sir

[@learnssis]

Thanks Pravin for your comment.

[@pranaychonkar2037]

Thanks for the video 👍 The data conversion is feasible when we have less columns to convert. But in my case, I've many tables source tables with column data type as nvarchar and that data to be loaded into destination tables, which has columns with varchar type. This will be the tedious work to change the type to nvarchar to varchar for each column through data conversion task. Is there any solution which will automatically convert the nvarchar to varchar type? Note:- data type can not be changed. Thanks in advance 💐

[@learnssis]

If you are using ssis to insert the data then this is the only solution I am aware about, if you are inserting data from one table to another on the same server then you can directly insert it, it won't require the data conversion.

[@pranaychonkar2037]

@@learnssis thanks for your quick reply. Source and destination both are different servers. Please suggest any other option instead of changing data type for one by one. Can we write any code or expression to convert all the columns from nvarchar to varchar in one go.?

[@learnssis]

@@pranaychonkar2037 we can write a query in stored procedure or in c#.net to convert it to correct data type but it won't work in ssis package as the meta data will change at run time. You can try to make a linked server from one SQL server to another and try to insert data without converting the data type.

[@crisoke8081]

Hello Pranya, I just ran into your question while seeking for answers on how to solve same problem you presented. I have 75 columns in my table and whenever I create my connection source, the length of all columns automatically reverts to length 50 and I get a warning. Did you ever figure a kind of transformation that can be implemented in other to convert the lengths back to what’s on the table?

[@learnssis]

@@crisoke8081 What is your source ?

[@dilipkumar-ck1nc]

28 lesson is data conversion in google drive it is conditional split And the file is not excel it is csv

[@learnssis]

Thanks for pointing it out, the folder was there with another number, I have corrected it and will see where conditional split will fit in.

[@dilipkumar-ck1nc]

@@learnssis where is google drive link in the video

[@learnssis]

@@dilipkumar-ck1nc Thanks for letting me know, I have added it.

[@artisingh9165]

Hi Sir If column is (Total payment amount )what data type we should keep I have tried currency ,float but in ssis its showing error returned status value 2 and status text

[@learnssis]

It depends what data you are going to store in the column. float can work in SSIS. Share the error message screenshot to me at [e-mail]

[@artisingh9165]

@@learnssis sir i mailed you .Thanks!

[@aviavi2612]

i am not able to convert i got the same issue can you help me even after adding data conversion task i am extracting data to Excel destination

[@learnssis]

What error you are getting ?

[@dadabliss1954]

Please can you update your videos? I really like them... But needs more on SSIS. Thanks

[@learnssis]

If you check the description of the video, I have created a lot of videos on SSIS tutorials playlist, SSIS Realtime scenario examples, SSIS interview questions and answers, you can watch them and I am working to make more videos. Thank you.

[@dadabliss1954]

@@learnssis I will try to get in touch with you soon. You are a Pro have a ton of knowledge on SSIS. I'm very impressed! Thanks

[@learnssis]

@@dadabliss1954 You are most welcome. I don't know much but try to share with community what I know.

[@dilipkumar-ck1nc]

Iam using msoffice 2016 here excel manager showing error

[@learnssis]

Can you take a look at this video kzbin.info/www/bejne/Z2PCe6J9ibmofpo You might need to change the project property.

[@ryanfelton7385]

I can tell you know your stuff. For future videos, please please please SLOW DOWN.... And get a dedicated mic. It is a bit of a struggle keeping up.

[@learnssis]

Thank you Ryan for your suggestion.

[@sidhupolimetla]

Can you please explain the difference between varchar and nvarchar?

[@learnssis]

Think the following are major differences: 1. Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. 2. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1. 3. JOIN-ing a VARCHAR to NVARCHAR has a considerable performance hit. 4. Some experts recommends nvarchar always because: since all modern operating systems and development platforms use Unicode internally, using nvarchar rather than varchar, will avoid encoding conversions every time you read from or write to the database

[@tangooo6171]

I can’t convert Arabic words from oracle

[@obaidullah5798]

Thank You .Aqil. 👏I send you mail please reply with . Thanks again.

[@srinivaschalla3442]

QUERY For Learning SSIS........? Ans:- Select * from LEARNING SSIS WHERE Trainer = "Aqil"

[@learnssis]

ha ha. Thank you so much.

[@srinivaschalla3442]

@@learnssishii brother I'm doing project regarding SSIS ...kindly can u help me through any social media platform... whener I'm unable to do....plss