SSIS question

  • Ive been given the task of automating the execution of an existing SSIS package. Im going to schedule a job on sql server to run the package once a month. Im pretty new to SSIS and have a few questions.

    Some of the steps inside the package invloves the transfer of data from supplied .mdb files. These files have different names depending on the month they are for

    eg 080209.mdb, 08032009.mdb

    This name format should be consistant, but the dates will be different depending on which month im processing eg 08032009.mdb will be the next months file

    At the moment the SSIS package is run manually and the contents changed to reflect the file being processed. In order to automate this, id need to use a variable which id determine from the system date (I think !)

    so If was was processing the file in February, id need some way to 'create' the name of the file inside the package, i could then use that to load up the appropriate .mdb file

    something like

    ProcessFileName = FormatForfileName(date.now) + '.mdb'

    this should give me a result of '08022009.mdb' (or whatever day/month/year was the current date)

    Id then use that name for the name of the .mdb file in the the OLE DB Source object.

    There are several files involved in this process, but they all have a consistent naming convention that includes the date.

    So when I look at the details of an OLE DB Source object, it specifies the OLE DB Connection manager (which is the name of the .mdb file id be dynamically creating)

    It will always be a table and the table name will always be the same for that particular OLE DB Source object.

    As I said, Im a complete newbie to SSIS, so any help/pointers/resources would be greatly appreciated.

  • Hi there,

    Add a Foreach loop container to your package and place the Data Flow task inside it.

    Configure the Foreach loop:

    - Collection Enumerator: Foreach File Enumerator

    - Specify the location of the folder that contains the .mdb files

    - Variable Mappings: Create a new variable to store the .mdb filename

    In the Data Flow task:

    - Select the connection manager that you use for the .mdb files, go to it properties and look for Expressions

    - Expand the Expressions and click [...]. Set the ConnectionString Property to use the Variable you created in the earlier step.

    This should give you the freedom you are after.

  • How would i create this variable ? it would have to be based on the current month and year (possibly a day as well, this will be confirmed)

    Is it possible to code it based on my previous pseudo code ?

  • You can set the path of the file from an expression - which can include a variable...

    I've not got an mdb connection setup, but principals should be the same as a flat file connection.

    If you click on the connection, use the Properties window. You'll see the ConnectionString. Further down you'll see Expressions with a little cross. Expand and use the elipses button (...)

    There you can set properties dynamically. Drop down the proeprty you're insterested in, then hit the elipses button for the expression you're going to use.

    Use evaluate expression to check it works ok.

    I've found Jamie's old blog useful. This may help http://consultingblogs.emc.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx...

    Also I've found this site extremely helpful http://www.sqlshare.com/Channel.aspx?cat=c871236d-8554-42e3-8683-4d422356c0bd...

    I am new to this myself!

  • Is it also possible to do the following with SSIS ?

    use FTP to download a zip file to a specified location. Once this file has been successfully downloaded, unzip it, and once successfully unzipped, rename specified files to a common format (eg 'Financial090203.mdb' to 'Financial.mdb') and finally process the data utilizing the original stages in the SSIS package. In here Ill hard code the required .mdb file to 'Financial.mdb'

  • Ok bad answer, more than likely... not done myself.

    All activity would likely stay on the control flow area. There's an FTP task, a File System Task to move, copy, create, delete files and folders. Though can't say about the zipping, perhaps the Execute Process Task? You'd need, if using WinZip, the command line utility.. I am guessing here though.

  • Ive googled this and found these steps

    Steps:

    1. Drag and drop execute Process task

    2. Edit the component.

    3.In the Process Menu,

    'Executable' =C:\Program Files\WinZip\WINZIP32.EXE

    'Arguments'= -e -o "fileNameinZipformat.zip "

    Working Dir='C:\TestFolder '

    4. Arguments tips: -e extract -o OverWrite exist

    Im about to try it alongside the FTP task (If I can get the use of an ftp server somewhere)

    will keep you posted

  • If you need a zip/unzip utility, check out this custom task:

    http://cozyroc.com/ssis/zip-task

  • There are a few ZIP tasks out there - some free. Check out the SSIS Community Tasks and Components[/url] directory, I list them there.

    Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
    Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
    Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.

  • There is also the TaskUnZip component available on CodePlex: http://taskunzip.codeplex.com/

  • Im following this example here

    http://sqlblogcasts.com/blogs/jorg/archive/2009/08/27/ssis-unpack-a-zip-file-with-the-script-task.aspx

    but converting it to c#.

    The process seems very 'flaky' to me. For example, If I give the Foreach process a name and description, then come out of the editor, save and go back in, the name and description revert back to its original !

    If I move to the collection area, i initially have no enumerator configuration option.

    If I create variables, the enumerator configuration options magically appear. I entered a value into the configuration area, saved it, came out, went back in, modified it, came out of the editor, saved it, went back in and it kept the original values !! Also If i create any variables and give them values, i cant then go back into the variable editor to change the values ! If I delete the variables, they don't actually get deleted, so I have no way of removing them and staring again. I would think that if I create anything in this process that I should subsequently be able to go back and edit or remove them, but it doesn't seem to be the case.

    Am I missing something fundamental here ?? Is it not possible to change anything thats created in the foreach loop process once its been created ?

  • Ok,

    1. Renaming a task: You cannot use any special characters. keep it to simple alphanumerics and you'll be fine.

    2. Enumerator Config: I can tell you are using SS 2008 (BIDS). Open the Foreach container task, then click the collections drop down and select the Foreach file enumerator option. This will give you the config screen.

    3. Original Value: have no idea why this is happening for you. Anything I am attempting to update, remains updated.

    4. Updating Variable Values: Manage your variables in the Variables window. (right-click the design area and choose 'Variables'). to update the value of a variable, locate the variable in the variables window, scroll across until you find the 'Value' column, enter the desired value and hit enter.

    5. Deleting Variables: you can delete a variable by highlighting the variable (in the variables window) and clicking the icon with a tiny red cross, or hitting delete.

  • Also with Variables watch out for their scope...

    They can exist globally in the package.. or within a data task. If you've declared a variable in a one data task and try to use it in another, you won't be able to.

  • You can also change the name and description of the object without going into the editor..

    Select the object, then use the properties window. Under Identification you can set the names.

    F4 is the shortcut to get to properties if the windows not open.

  • I think im slowly getting the hang of SSIS, its not particularly intuitive though, I was expecting to edit the variables in the edit window of the task, but had to open another window separately. I did, however, manage to achieve the functionality i wanted and now have the zip files being unzipped into my specified directory.

    now on to the ftp task (see my other post for my problems with that)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply