locked
ssis. A new Folder in a project RRS feed

  • Question

  • Hello friends,

    I am creating a ssis package that downloads file from a remote ftp server into a local folder. A script component will read these files and tranform the data before being fowarded to the Database. 

    But I need  to create this folder inside my project, such that, when transferring the project to another computer or deploying the project on to SQL Server, I don't need to create this folder manually again.

    How do I do this?. I don't seem to find this option in Visual Studio Integration Services.

    Thanks.

     


    Thursday, August 11, 2011 2:55 PM

Answers

  • Why not to download into the same place where the package is. So simply indicate no path. The reading script will also operate on the same local dir.

    It is not the best practice though. A much better approach is probably to use the %temp% or %TMP% directory instead, it is always defined and the best place for interim, so to speak, files.

    As a side note: make sure you deploy your package using the proper package protection and ensure the account running it has all the permissions to access the file system.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by HarriUG Saturday, August 13, 2011 11:09 AM
    Thursday, August 11, 2011 6:13 PM

All replies

  • There is no option to do this in Business Inteligence Development Studio (BIDS) for SSIS packages.

    You could choose to do this one of many ways probably, but here are two that I can think of. 

    A) Write a batch file or PowerShell script that checks for the existance of the folder and creates it if it does not exist.  Run the script to deploy the folder.

    B) In the SSIS package, write a Scipt Task that checks for the existance of the folder and creates it if it does not exist.  Run the package and it will create the folder.

    The advantage of A is that you're seperating the deployment step from the SSIS package.  The advantage of B is that the deployment step is contained within the SSIS package.  The disadvantage of B is that the SSIS package will not fail if the folder turns up missing.

    Thanks,

    Jason

     


    Jason
    Thursday, August 11, 2011 3:45 PM
  • Why not to download into the same place where the package is. So simply indicate no path. The reading script will also operate on the same local dir.

    It is not the best practice though. A much better approach is probably to use the %temp% or %TMP% directory instead, it is always defined and the best place for interim, so to speak, files.

    As a side note: make sure you deploy your package using the proper package protection and ensure the account running it has all the permissions to access the file system.


    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by HarriUG Saturday, August 13, 2011 11:09 AM
    Thursday, August 11, 2011 6:13 PM
  • I am agree with IronCladLou,

    you can check the existence of the folder and create it if folder doesn't exists with script task,

    this code will do this in script task :

    public void Main()
        {
          if (!System.IO.Directory.Exists(@"C:\destinaiton directory"))
            System.IO.Directory.CreateDirectory(@"C:\destinaiton directory");
          
          Dts.TaskResult = (int)ScriptResults.Success;
        }
    



    http://www.rad.pasfu.com
    My Submitted sessions at sqlbits.com
    Thursday, August 11, 2011 10:04 PM
  • This "is" possible using the existing SSIS tasks in the toolbox. Drag and drop a "FileSystem" task on to your SSIS package designer surface. Double click on it and set the "Operation" property to 'Create Directory'. When you configure the taskk first time, it'll ask for a path. You can type the path/folder name in your Dev environment when you are developing the package. Also, on the same properties window, set the "UseDirectoryIfExists" property to "True". That way, when you port the package to any other server/environment, it'll create the directory only once, when the package executes the very first time.

     

    Finally, to avoid hard-wiring the Dev folder path property to what you provided during development, you can keep it in the package Configuration file and then read the path from the config file at run time for FileSystem task to use. So every time you port the packlage, the DBA will only have to change the path string in the config file and you'll be good to go.

     

    (By the way, to test that this technique works, delete the folder that gets created in your Dev environment at design time when you configure the FileSystem task and run the package. it'll create the folder without any errors/issues.)

    Hope this helps!

     

    Cheers!

    Muqadder. 

    Thursday, August 11, 2011 10:33 PM