George's BI Blog

Now you can add the code. Below is the sample code I used.

public void Main()

        {

            try

            {    

                string siteURL = Dts.Variables["User::siteURL"].Value.ToString();

                string DealershipReportsPath=Dts.Variables["User::DealershipReportsPath"].Value.ToString();              

                string Server = "<Your Server>";

                string Database = "<Your Database>";

                string ConnectionString = "Data Source=" + Server + ";";

                ConnectionString += "Initial Catalog=" + Database + ";";

                ConnectionString += "Integrated Security=SSPI";

                SqlConnection connection = new SqlConnection();

                connection.ConnectionString = ConnectionString;

                connection.Open();

                SPSite site = new SPSite(siteURL);

                using (SPWeb web = site.OpenWeb())

                {

                    SPList list = web.Lists[DealershipReportsPath];

                    SPQuery query = new SPQuery();

                    //Condition to check the item type is folder or not

                    query.Query = "<Where><Eq><FieldRef Name=’FSObjType’/><Value Type=’Lookup’>1</Value></Eq></Where>";

                    //Get all the items including subfolders from the list

                    //query.ViewAttributes = "Scope=’RecursiveAll’"; 

/*if you uncomment the above line,it will traverse all sub folders. At the

moment, it DOES NOT go to sub folders.*/

                    //Retrieve the items based on Query

                    SPListItemCollection items = list.GetItems(query);

                    //Get the name and Url for the folder

                    foreach (SPListItem item in items)

                    {

                        SqlCommand myInsertCMD = connection.CreateCommand();

                        myInsertCMD.CommandText = "insert into dbo.SharePoint_FolderList (SP_FolderName, SP_FolderPath) values(‘" + item.Name + "’, ‘" + web.Url + "/" + item.Url + "’)";

                        myInsertCMD.ExecuteNonQuery();

                        myInsertCMD.Dispose();

                    }

                }

                connection.Close();

                connection.Dispose();

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message + "—-"+ ex.StackTrace);

            }

            Dts.TaskResult = (int)ScriptResults.Success;

        }