hi, how to copy excel files from my C: drive( from remote desk) to sharepoint unc path (\\sharepoint.corp.********.com\sitename.......) PRO GRAMMATICALLY using ssis package. and i have to schedule the same in sql server agent. thanks,

You can map the sharepoint as a shared drive on to the system where you will deploy this code Also admin rights on the sharepoint are required for the credentials you would connect to sharepoint and run this code on behalf of, the sharepoint admin needs to enable the explorer view for that id Public Sub Main() ' ' Add your code here ' Dim FsourceDir, sourceDir, destDir, logdir, strLogFileName, fDir, currFolder As String Dim fRecursive, overWrite As Boolean logdir = (Dts.Variables("varLogFileLocation").Value.ToString()) fRecursive = CBool((Dts.Variables("bolRecursive").Value.ToString())) overWrite = CBool((Dts.Variables("bolOverwrite").Value.ToString())) strLogFileName = (Dts.Variables("varFileName").Value.ToString()) Dim fsStream As FileStream Dim sWriter As StreamWriter fsStream = New FileStream(logdir + "\" + strLogFileName, FileMode.Create) sWriter = New StreamWriter(fsStream) FsourceDir = (Dts.Variables("varSourceDirectory").Value.ToString()) For Each fDir In System.IO.Directory.GetDirectories(FsourceDir) Dim oNetDrive As New NetworkDrive() currFolder = fDir.Substring(fDir.LastIndexOf("\") + 1) oNetDrive.LocalDrive = FindNextAvailableDriveLetter() + ":" oNetDrive.ShareName = (Dts.Variables("varDestinationDirectory").Value.ToString() + currFolder) oNetDrive.MapDrive((Dts.Variables("varUserId").Value.ToString()), (Dts.Variables("varPwd").Value.ToString())) sourceDir = fDir.ToString() destDir = oNetDrive.LocalDrive RecursiveDirectoryCopy(sourceDir, destDir, fRecursive, overWrite, sWriter) oNetDrive.UnMapDrive() oNetDrive = Nothing Next sWriter.Close() fsStream.Close() Dts.TaskResult = Dts.Results.Success End Sub Public Function FindNextAvailableDriveLetter() As String ' build a string collection representing the alphabet Dim alphabet As New StringCollection() Dim lowerBound As Integer = Convert.ToInt16("f"c) Dim upperBound As Integer = Convert.ToInt16("z"c) For i As Integer = lowerBound To upperBound - 1 Dim driveLetter As Char = ChrW(i) alphabet.Add(driveLetter.ToString()) Next ' get all current drives Dim drives As DriveInfo() = DriveInfo.GetDrives() For Each drive As DriveInfo In drives alphabet.Remove(drive.Name.Substring(0, 1).ToLower()) Next If alphabet.Count > 0 Then Return alphabet(0) Else Throw (New ApplicationException("No drives available.")) End If End Function Private Sub RecursiveDirectoryCopy(ByVal sourceDir As String, ByVal destDir As String, ByVal fRecursive As Boolean, ByVal overWrite As Boolean, ByVal sWriter As StreamWriter) Dim sDir As String Dim dDirInfo As IO.DirectoryInfo Dim sDirInfo As IO.DirectoryInfo Dim sFile As String Dim fileCount As Int32 Dim sFileInfo As IO.FileInfo Dim dFileInfo As IO.FileInfo ' Add trailing separators to the supplied paths if they don't exist. If Not sourceDir.EndsWith(System.IO.Path.DirectorySeparatorChar.ToString()) Then sourceDir &= System.IO.Path.DirectorySeparatorChar End If If Not destDir.EndsWith(System.IO.Path.DirectorySeparatorChar.ToString()) Then destDir &= System.IO.Path.DirectorySeparatorChar End If 'If destination directory does not exist, create it. dDirInfo = New System.IO.DirectoryInfo(destDir) If dDirInfo.Exists = False Then dDirInfo.Create() dDirInfo = Nothing ' Recursive switch to continue drilling down into directory structure. If fRecursive Then ' Get a list of directories from the current parent. For Each sDir In System.IO.Directory.GetDirectories(sourceDir) sDirInfo = New System.IO.DirectoryInfo(sDir) dDirInfo = New System.IO.DirectoryInfo(destDir & sDirInfo.Name) ' Create the directory if it does not exist. If dDirInfo.Exists = False Then dDirInfo.Create() ' Since we are in recursive mode, copy the children also RecursiveDirectoryCopy(sDirInfo.FullName, dDirInfo.FullName, fRecursive, overWrite, sWriter) sDirInfo = Nothing dDirInfo = Nothing Next End If ' Get the files from the current parent. For Each sFile In System.IO.Directory.GetFiles(sourceDir) sFileInfo = New System.IO.FileInfo(sFile) dFileInfo = New System.IO.FileInfo(Replace(sFile, sourceDir, destDir)) 'If File does not exist. Copy. 'If dFileInfo.Exists = False Then sFileInfo.CopyTo(dFileInfo.FullName, overWrite) sWriter.WriteLine(sFileInfo.FullName + " Copied") totalFileCount = totalFileCount + 1 ' Else 'If file exists and is the same length (size). Skip. 'If file exists and is of different Length (size) and overwrite = True. Copy 'If sFileInfo.Length <> dFileInfo.Length AndAlso overWrite Then 'sFileInfo.CopyTo(dFileInfo.FullName, overWrite) 'sWriter.WriteLine(sFileInfo.FullName + " Copied") 'If file exists and is of different Length (size) and overwrite = False. Skip 'ElseIf sFileInfo.Length <> dFileInfo.Length AndAlso Not overWrite Then 'Debug.WriteLine(sFileInfo.FullName & " Not copied.") ' End If 'End If sFileInfo = Nothing dFileInfo = Nothing Next End Sub End Class Abhinav http://bishtabhinav.wordpress.com/

what should be "PRO GRAMMATICALLY"? Can't you use the File System Task to copy files? (optional with wildcards or a foreach loop for multiple files) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

i used file system task but it didn't worked out for me.....its giving an error when i scheduled the package using sql server agent: "The filename, directory name, or volume label syntax is incorrect. ". End Error DTExec: The package execution returned DTSER_FAILURE (1). The package execution failed. The step failed. thanks,

i used file system task but it didn't worked out for me.....its giving an error when i scheduled the package using sql server agent: "The filename, directory name, or volume label syntax is incorrect. ". End Error DTExec: The package execution returned DTSER_FAILURE (1). The package execution failed. The step failed. thanks, If it works in BIDS, but doesn't when scheduled, it's probably an authorization issue. Does the user (credentials/proxy) that runs the job has the same rights as you?Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

A Sharepoint document library is the same as a network share. So as mentioned by Joost use a File Sysytem task, try using the http address of your document or if it fails try its corresponding UNC address. The UNC address for your document library should be something like \\site\documentsubsite\....\docLibrary\File.extension.http://btsbee.wordpress.com/

hi... sql server agent service running under my credential(domain\user) and i have an access to share point(domain\user) also. no credential issues... thanks,

hi joost..... even though i have an access to sql server agent and share point. I'm getting the following error when i scheduled it in sql server agent but it works FINE in SSIS. Executed as user: domain\user. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:02:03 AM DTExec: Signature verification failed. The signature status is not present. Started: 7:02:03 AM Finished: 7:02:03 AM Elapsed: 0.078 seconds. The package could not be loaded. The step failed. please please respond to the above error.

Hi, Why don't you use the sharepoint webservice for copying ? Or this great tool from codeplex : http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 Regarding the error above : it is often caused by an error in the connection string. Try to check that part of your package. Cheers Regis

hi joost..... even though i have an access to sql server agent and share point. I'm getting the following error when i scheduled it in sql server agent but it works FINE in SSIS. Executed as user: domain\user. Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 7:02:03 AM DTExec: Signature verification failed. The signature status is not present. Started: 7:02:03 AM Finished: 7:02:03 AM Elapsed: 0.078 seconds. The package could not be loaded. The step failed. please please respond to the above error. Anything in your SSIS log? ps just to be clear...the user that starts the job in SQL Server Agent isn't the same as the user that actually runs the job. ps2: Related Post: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/784839fe-3f42-494b-b936-52779f984a7e Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

hi joost both users are same(domain\username). thanks

See: http://randypaulo.wordpress.com/2010/02/02/c-uploading-file-to-sharepoint-document-library-using-web-services-without-sharepoint-sdk/ After you generated the proxy class (Strongly-named), put it in C:\Windows\Microsoft.NET\Framework\v2.0.50727 & GAC, then reference it from your Script task. Randy Aldrich Paulo MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog BizTalk Message Archiving - SQL and File Automating/Silent Installation of BizTalk Deployment Framework using Powershell > Sending IDOCs using SSIS