July 13, 2021 at 8:28 pm
Hi,
Is there a way for SSIS to read from a csv file stored in Sharepoint online ? The file name will have prefix "Flat File Source" followed by date and some other characters which will keep changing.
Example : Flat File Source __2021-07-13..............
Thanks,
PSB
July 14, 2021 at 2:45 pm
I am not sure if or how SSIS would natively handle working with SharePoint Online, but I know there are addons to SSIS (such as ones from CDATA) that can integrate with SSIS and SharePoint Online.
I think the challenge would come in when you are importing the data as you would need some way to identify that a file has been processed so it isn't re-processed for the next run. So you would need to either capture the file names and write them to a table and only process ones that are new OR you would need to clean up the folder as you go (ie move or delete the file after it is processed). OR you would need some logic in the SSIS to say which files you are needing to process such as if it has the date, then you would only process files created on the previous day.
Now, if you want to do it without buying new modules, I think you are going to need to use some scripting to pull the files out of SharePoint onto the local machine and then have SSIS loop through the files in the folder.
I have never done this before and, as I stated above, am not certain if SSIS can handle SharePoint Online natively. Other experts may have a better solution. But to summarize my suggestions - buy an addon or script it out. From my understanding of scripting in SSIS, there is very little you cannot do. Something else to note - I have not used scripting in any SSIS packages as I have not found a need for it so far. But I also have not been doing any fancy cloud based processing of data and for the most part my ETL process is a lot of E, very little T, and L. And my transform sections of the ETL that appear complicated are mostly glorified JOINs and filters inside SSIS that could likely be handled better with a stored procedure or TSQL. It was just the direction the boss wanted - do as much of the ETL processing inside SSIS as possible to keep the load on the SQL instances as light as possible. Which isn't a bad practice if your SSIS instance has its own box...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 14, 2021 at 8:11 pm
Did a quick search on: ssis sharepoint download file
There seems to be a lot of ways to accomplish downloading a file using SSIS - which would be the first step in importing that file. If you search just on 'ssis sharepoint' there are many articles on how to interact with sharepoint.
When I last looked at something like this - the biggest issue I found was being able to authenticate to the sharepoint site. And it is now even harder because a lot of organizations are enabling two-factor authentication to access the sites.
Look at the available options and review them to see if you can implement one of them for your particular issue.
I ended up backing off on trying to download, and made that portion a manual step for the end users. When they wanted that data to be loaded - then copy the file into a file share and my SSIS package(s) will pick up the file the next time they run.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 23, 2021 at 2:50 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply