SSIS Tutorial Part 21-How to Load Data to Pre-Formatted or Template Excel File in SSIS Package

[@reynaldomorillo4271]

I have been watching for over 10 years. Every now and then, I go back for a refresh. You, you are amazing! Thank you, so much for taking the time to teach us! Reynaldo

[@JoshBurnsTech]

Awesome tutorial! Very well explained and easy to follow. Appreciate your efforts!

[@Scott_works]

Thank you so much for all of your videos. They help get us up to speed quickly!

[@krismaly6300]

I enjoyed watching this video and recommend other. Thanks for educating the community and appreciate your volunteership.

[@hutchm92]

Excellent instructions on how to create and use SSIS packages. Thank you!

[@TechBrothersIT]

Thank you for liking our effort. Glad you liked my way of teaching.

[@raghavendrapani6353]

Wow thank you!! I missed that delay validation and it cost me some time lol .

[@krismaly]

I enjoyed watching this video and tried to create Package successfully. Recommend others to watch. Thanks for educating the community. Thanks,

[@TechBrothersIT]

krismaly You are welcome Kris and Thanks for watching the video and providing valuable feedback.

[@Surendrapatle83]

Really helpful man. Thank you so much.

[@murthymamidi6639]

Thank u so much Techbrothers team for the wonderful videos..i use to follow all the videos from all the modules and it helps me a lot.Once again thank u for your patience to made all this videos

[@TechBrothersIT]

You are most welcome

[@jwh5093]

great info. just learning SSDT it helped me a lot

[@yohannesareda901]

THANK YOU VERY MUCH! YOU HAVE INDEED BEEN A GREAT TEACHER AND PERSON AS A WHOLE. WELL DONE!!!!

[@the420aditya]

THANKS MAN.I WAS LOOKING FOR EXACT SAME THING.

[@chadpab7142]

thank you for sharing your knowledge with SSIS!

[@tinger99]

Great video - easy to follow. Thanks for sharing this information with everyone!

[@awesome_gift7839]

Thank you so much, this was really insightful! God bless you

[@TechBrothersIT]

Thank you so very much for kind words.

[@rakeshkusuma8811]

Thanks for sharing these videos, really helpfull..... great knowledge.

[@TechBrothersIT]

Hi Rakesh, Very happy to hear that you liked my effort. Thanks for support.

[@mikecervi496]

This was really helpful. I have a feeling I'd learn a lot from watching your whole series. Thanks.

[@TechBrothersIT]

Thanks for kind words and glad to hear that my effort was helpful. Thanks for watching!

[@Stephen_T_Stephan]

Thanks for sharing. Let me point out an issue with the Pre-Formatted template. The data that you load into the excel always comes as text. So, where the data is concerned, you cannot format the excel as suggested. The work around that I have found is creating VBA scripts within your template to handle Text To Columns, Cell Justification, Date and Currency formatting, and even a Delete all VBA Code script with it being called from an Auto_Open script all written to your template. Simply calling the various macros in the Auto-Open will handle the other formatting for the Data portion. Hope that helps anyone who has also faced this issue.

[@chavayabeebee-galvao9376]

I am having this problem right now, everything else is working beautifully except for the fact that I can't get it to come through as a numeric format. GRRRRRRR! Microsoft should fix this.

[@mandadidivya4219]

Thanks for sharing.very helpful.

[@KalugampitiyaJayakody]

this is also very good video to learn. thank you very much Tech Brothers IT

[@TechBrothersIT]

Thank you, glad you liked it.

[@trinath89]

Very Nice. Thanks for taking your time to teach us :)

[@TechBrothersIT]

Trinath Guptha Thanks for watching dear. Glad you liked the videos!

[@mr.prasadyadav]

Thank you Sir valuable Tutorial

[@priyakarthik4505]

Thanks for sharing this video.

[@zemenubejiga966]

Very helpful. but it's better to make it short because this day's people are not interested in videos longer than 8 min

[@varshinishekar9553]

Hi.. This was much useful.. Can you help me with the same thing rowwise

[@Ренат-ц8ж]

Thank you!

[@AndrewJohnClive]

Thank you Sir. Great Tutorial!

[@TechBrothersIT]

Hi, thanks for watching the video, please join www.techbrothersforum.com to ask any question that you may have. Tech Brothers and other professionals are online at the forum to help you out quickly. Thanks

[@tomholden494]

Great video. Thanks for the information to also pull in a new template and overwrite destination. This video shows exporting records from SQL to columns found in a different place in the destination file. Do you have a video that allows exporting to columns of different formatting? For example, I want Calibri 8 but new rows are Calibri 11. I want yyyy/mm/dd custom mask. I guess my first question is, is this possible?

[@laswidyaratne8115]

Hi Tech Brothers, I really appreciate for uploading this video, I have an excel template with 800+ columns which i'm trying to upload data from SQL, i have noticed that SSIS support only 255 columns. Is there a way to get around this? Please advise on this. Thank you heaps!

[@beejunk549]

well done! thank you!

[@kwesimaison9958]

Thank you very much for this. where do I watch more of your stuff?

[@TechBrothersIT]

you are most welcome. our all tutorials are available on www.techbrothersit.com , totally free.

[@rcallain2001]

Thank you for this. I know it's been 2 years later but I am hoping you have some advice on something I'd like to accomplish. I have users generating 9-20 different xls outputs(one worksheet each) from Cognos. They then copy and paste that data manually into a template xls of many worksheets that has its own calculations. These Cognos outputs have their own internal styling and field formatting that also need to be retained. I'd like to set up a package that will can be run when all user data has been created and that package will load into the template's different worksheets that the package directs it to, and making sure to maintain each source xls' format and styling. I have successfully copied such data but the formats are not coming through and there are way too many unknowns. Any ideas? Thank you.

[@zuyihuang5539]

thanks, Great video!

[@TechBrothersIT]

+ZuYi Huang You are welcome. Thanks for watching!

[@zuyihuang5539]

hi Sir, BTW, if you do not mind, may I ask what is your name, how can I call you,as TechBrothersIT is just a nick name.. : ) thanks!

[@TechBrothersIT]

Our names are Aamir and Raza:)

[@nelsonjean5327]

Thanks for the great video. Do you know if there's a way to add data to a table in Excel? I'm able to append data to the worksheet as shown in the video but I can't get it into the Excel table range (named "Bookings"). There are other sheets in the file that summarize the raw data and the formulas reference the "Bookings" table name, so I need to append into the table range in order for the summary sheets to include the newest data.

[@TechBrothersIT]

+Nelson Jean Hi, I have create a lot of demos on Excel, please check the posts under Excel Source and Destinations (Script Task- Dynamic) heading on below link www.techbrothersit.com/2014/12/ssis-videos.html

[@jaganprasanth481]

Hi Aamir, By default, integer fields are exported as string fields, how to export fields with its data type as in the table.

[@phaniraghavendra1415]

Very usefull ...

[@SoumitaParui]

Why we are using "FullDestinationPath" variable value as 'ExcelFile Path' in excel connection manager? Why can we use that as connection string?

[@santhoshp6142]

Hello Aamir, great videos! Thanks. can you tell me how to read data from formatted excel sheet and load to SQL server DB? for me i need to skip first 2 lines and 3rd line is header and data starts from 4th line.

[@TechBrothersIT]

Hi, You are welcome and thanks for watching. I have covered these topics , please check the videos under Excel Source and Destinations (Script Task- Dynamic) on below link www.techbrothersit.com/2014/12/ssis-videos.html it is video 15 and there is one more. all the best!

[@sylvia3820]

What if I have multiple templates ( workbooks) and not just one as the video shows ? Do I just repeat the steps ?

[@donaldkidd7427]

Thanks, solved a problem for me.

[@TechBrothersIT]

Happy to hear that it was helpful. You are welcome!!

[@chrischristos4550]

how can we use functions for some columns with ssis ? for example if we have one column with points and we want to find the sum

[@rjmhmd]

Thanks for this video tutorial. It was really helpful with step by step explanation of the process. I've one doubt though. Is there a way to append time along with date to output file? Because, if I've to run the script more than once in a day, it's over writing the file that has already been created. If time is appended with the output file name, separate files would be created. Is there a way to execute this? Would really appreciate if you could help me on this. Many thanks

[@rjmhmd]

I've identified it by myself..instead of using getdate() function, if we use getutcdate() fucntion, we will be able to create files with different times.Thanks for this wonderful tutorial

[@TechBrothersIT]

Hi Raja Mohamed, Glad it was helpful and you are able to work it through. I will say that use System::StartTime variable instead of using getdate() or any other function in expressions. System::StartTime will be same across the SSIS Package execution and as you will move the template file and rename it with by using System::StartTime and then you can use the same System::StartTime in Excel Connection Manager Expressions. Getdate() can work fine sometime but if there is delay in copying and then wring the data to file. by the time SSIS Package will get to Write file and build file path by using getdate() on Excel Connection Manager, it will through error. To be on save side, use System::StartTime Thank you Aamir

[@waaaazaaaa2008]

Thank you so much

[@TechBrothersIT]

you are very welcome!!

[@bennydxb]

Recently I am started watching your SSIS videos. It is very useful within one week I build one project Now I am facing one problem. Can you explain how to write to excel date and varchar(max) columns.

[@TechBrothersIT]

Hi, I have a lot of videos on Excel, please check the videos under Dynamic Excel Loading heading www.techbrothersit.com/2014/12/ssis-videos.html

[@persianimmortal7642]

Excel designation keep storing number as text and I have tried data conversion , derived column and advance edit. None worked yet numbers are saved as text. Please help

[@gaganupadhyaya3562]

Thanks for sharing very helpful. I am implementing similar kind of scenario but when I try to execute, sometimes it runs successfully and sometimes it gives the unpredictable error "[Excel Destination [2]] Error: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database." when I run it for consecutive times . I have kept the delay validation to true as suggested in the video. The name of the template is also different as the output excels generated. Can you please help?

[@kfrederick1958]

Hi did, you ever get a solution for this intermittent issue. Same thing is happening for me.

[@TheAmateurPhilosopher]

Is it possible to do this in ssrs.. I really need to know

[@rocklogan6349]

The File with the date extension is getting created in the destination folder. However, the package fails and the data is not copied to the sheet. Can you please let me know what could be the reason. I am getting the error . [Excel Destination [24]] Error: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.

[@AmitSingh-er3lk]

How it identify to paste data from row 7?

[@ash3rr]

This doesn't tell you how to use conditional formatting in that report though, which is what I was looking for.

[@monaliy]

Error at Package: The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

[@mehranuddinr2639]

Not able to load the Excel as it is giving me connection error

[@pullaiahmamidala6754]

Thank You:)

[@purvidesai2556]

how to allow excel to maintain its formula and SSIS should load the data from row number 1

[@sajidrazakhan]

Thank you

[@TechBrothersIT]

+Sajid raza khan You are welcome and Thanks for watching!

[@mehranuddinr2639]

Unable to load the sheet

[@nadirkhazi2820]

Hello sir I had followed every step but iam getting this error in while executing in (2010 visual studio version) TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Data Flow Task [Excel Destination [134]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error at Data Flow Task [SSIS.Pipeline]: Excel Destination failed validation and returned error code 0xC020801C. Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation. Error at Package1 [Connection manager "Excel Connection Manager"]: 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: "Failure creating file.". (Microsoft.DataTransformationServices.VsIntegration) ------------------------------ BUTTONS: OK ------------------------------

[@TechBrothersIT]

Nadir, Here are couple of pointers 1-- You might not be copying the excel file to destination folder correctly. 2--If you are copying the you might have forgot to change the Connection String for Excel Connection Manager in expression so it can not find the file 3-- Maybe Delayvalidation is not set to True on Excel Connection Manager and Data Flow Task. That could cause this problem. My suggestion. Watch the video again. Stop on each point and perform the task as it is. All the best!

[@nadirkhazi2820]

Tech Brothers Thank you ...for guiding me ...the problem was on fulldestination variable EXPRESSION ,at start i couldnt concatenate getdate() function using (DT_WSTR,30)...then i changed to (@[User::dest]+ (DT_STR, 4, 1252) YEAR( GETDATE()) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE()), 2) + ".xls")...how does this varies ?

[@TechBrothersIT]

You should be able to do that by using (DT_WSTR,30), this script is doing the same thing but you are extracting one part such as year, month and day and contacting them. This will work as well. Stop the video and write exactly what I wrote, it should work just fine. Next time I will put the expressions in Description.