May 22, 2012 at 2:13 am
[font="Arial"]Hi All,
I hope someone would be able to help me.
I need to write a process in SQL Server that will:
1.Read through a specific directory and update a specific table with the path of each document in a specific directory,
2.as well as update the name of each of these documents.
3.Store each document in ExampleDB_DocLib (document_storage_tbl) linking it to the document_tbl. Column document_pk is the link.
4.It must be a scheduled task in the SQL Agent.
The documents are in the following directory:
•D:\Documents_1\Documents_2\
This directory path is mapped in the Example database (ExampleDB_Example_2005) in table document_tbl and the documents are stored in ExampleDB_DocLib in table document_storage_tbl.
Thanks![/font]
May 22, 2012 at 2:18 am
One way would be to design an SSIS package which takes advantage of a variable and a for each loop set at file enumeration then you can dump the file paths into the table or a staging table then do your manipulations into the main table for if it exists or not etc.
May 22, 2012 at 2:26 am
Thanks for the responce. I unfortunately have no idea what you are saying!
My SQL knowledge is limited to writing basic queries, or to update and replace data etc.
Could you perhaps point me in the right direction or give me some pointers?
thanks!
May 22, 2012 at 2:32 am
Sure, take a look in the stairways section of the site for Integration Services, it will provide you a good starting point on SSIS. Then take a look here for for each loops http://msdn.microsoft.com/en-us/library/ms141724.aspx
I would put a for each loop in SSIS, and give it a variable, tell it the directory to scan and get it to map the files it finds to a variable. Then use a data flow task to insert that variable value into a holding table. Then you can use the MERGE functionality to say if it already exists then do nothing, if it doesnt exist then insert, if its been deleted then delete the row. http://technet.microsoft.com/en-us/library/bb510625.aspx - Info on merge
May 22, 2012 at 2:36 am
Great! Thanks for your help!
May 22, 2012 at 2:38 am
No problem, this seems easy enough to do and would be a good learning curve as you get to learn SSIS as well. If you get stuck, come back and I/we will try and help when we can.
May 24, 2012 at 7:51 am
Hi there. I have had a look at the Stairways articles about SSIS, and I am sure this will work. However, I am having great difficulties finding out what software to use that would have the SSIS package needed. From the screen shots in Andy's article, it looks like he is using Visual Studio, but I am not sure which version. I have VS2010, and obviously that doesnt come with SSIS....
Any suggestions?
Thanks!
May 24, 2012 at 7:55 am
When you install SQL you have the option to install what we call BIDS (business intelligence development studio), if you dont have it installed, just simply run through the media installation again and select it as a option to install.
SQL 2008 installs VS2008, and BIDS is put in the SQL Server 2008 start menu folder.
Going forward and if you move to SQL 2012, it works off VS2010 but has been renamed to SQL Server Data Tools or SSDT again it puts SSDT in the SQL Server 2012 start menu folder.
May 24, 2012 at 7:59 am
Thanks! Does SSIS only come with the full server packages, i.e. SQL Server 2008 etc.? or would I be able to run it off SQL Server Management Studio 2008?
Appologies for all the questions, but one more: If I understand you correctly, I must install Visual Studio 2008 AND SQL Server?
May 24, 2012 at 8:02 am
It's a seperate check box on the SQL Server installation, so you will need the SQL Server media to install BIDS. Its a completly seperate program from SSMS.
The installation will install the "shell" version of VS2008 for you, so you dont need to have it installed before hand.
May 24, 2012 at 8:04 am
Once again; thanks for all your assistance!
May 24, 2012 at 8:05 am
No problem, happy to help as always.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply