Post reply

SSIS Challenge I am Facing

  • November 15, 2006 at 3:41 pm

    #115084

    Hi

     

    I need to write a SSIS package with the following features:

     

    Source: There are many .xls which are placed randomly in the network share. However, we will have a “tracker table” in target database where all the .xls path are mapped.

     

    Destination: Local SQL Database [One table for one .xls and data must be appended]

     

    I believe that the SSIS will actually have the below control flow design:

     

    1. It will connect to Local tracker table table and read the rows for .xls path
    2. For each path, it will create data source of Flat File
    3. It will then create data mapping dynamically with the SQL Database
    4. Then it will do the datapump

     

    Could you please help it how to author this package? Also if these features are possible to be included in SSIS [Particularly point 2 and 3]

     

    Thanks in advance.

     

    Regards,

    Utsab Chattopadhyay


    CDBA

  • November 16, 2006 at 7:24 am

    #672273

    go to the store and buy the Wrox SSIS book. It is the red book with the pictures of all the geeks on it.

    there is a walk through and at the end they use the ForEachLoop to read multiple files in a folder

    i would start testing by creating the flow for one file and then the foreachloop task at the end

  • November 16, 2006 at 10:43 am

    #672378

    The Wrox book is awesome.   I'm not sure this task is well handled in it, though.  I have had to do similar things...here is how I would approach this task (though I haven't done this specific task before):

    1. Create a variable in your package with a data type "Object", and another variable to hold your xls path.
    2. Add an Execute SQL Task objects that selects from your table...in the "Result Set" panel, enter "0" under result name, and select your object variable from the dropdown under variable name.
    3. Create a Foreach loop container.  In the "Collection" panel, select "Foreach ADO Enumerator", select your object variable in the "ADO object source variable", select "Rows in the first table".  Under varible mappings, select your xls path variable, and map to index 0 (assuming your select statement returned only one column).
    4. Create an xls connection manager...add an expression for the connection string property and set it equal to the xls map variable.
    5. Put a data flow task pulling your xls file inside your Foreach loop.

    You may have to play with this a little, but that should be the gist if it.  Happy hunting!

  • November 20, 2006 at 1:47 pm

    #673064

    Thanks a lot Dan for the help. But I am still struggling with the step 4 and 5. Could you kindly help me on how to implement suggestions of Step4 and Step5 as suggested above?

    Thanks in advance for the helps.

     

    Regards

    Utsab Chattopadhyay

     


    CDBA

  • November 20, 2006 at 3:23 pm

    #673093

    I think so...It might be easier to work this out outside of the foreach loop, then drag it to the foreach loop container.

    1. Add a data flow task to your package.
    2. In the toolbox, add an "Excel Source" data flow source onto your page.  Use it to add a new excel data source to your packages. Set it up by directing it to a sample Excel file.  Choose the worksheet and make sure  the columns are propagated. 
    3. Add the data source for your SQL destination, and map the excel file to it.
    4. Right click on your "Excel Connection Manager", and choose properties.
    5. In the properties menu, find "Expressions" and click on the ellipsis to access the expression builder.
    6. In the expression builder, select "ExcelFilePath" from the drop down, and add the variable name you created in step 1 of my original instructions.

    I tested this method, and it seems to work.  Once you get this working without the for each loop, add the foreach loop to your package, and drag the data flow task you created to your foreach loop.  Follow the instructions in step 3 of my original post to map values from your table to the variable used in the excel connection.  

    Hope this helps...you will run into trouble, of course, if the structure of your excel files are different, or if the data in the excel files doesn't fit your destination.  Other than that, I think this should work.

  • November 21, 2006 at 7:34 am

    #673201

    Hi Dan,

     

    Thanks for the advise. But still I am struggling L

    When I am changing the XLS connection manager property à Expression à XLS File Path == @[User:ath] and try to execute, the following error is thrown:

     

    Package Validation Error

    Error at dataflow task [Excel Source[1]]

    ... etc

    It looks like a OLEDB Error [Source MS Jet Database Engine HResult 0x80004005 Description "Invalid Argument"

     

     

    Could you kindly advise me how to proceed? Many thanks in advance.

     

    Regards

    Utsab Chattopadhyay


    CDBA

  • November 21, 2006 at 10:31 am

    #673265

    Just to make sure, the only thing that should be appearing in the expression box is:

    @[User::File_Path]

    Where "File_Path" is the name of your variable.  You are adding this value to the Property "ExcelFilePath".  It is unclear from your post how you tried to do this (did you type "XLS File Path == @[Userath]" somewhere?).

    Were you able to get a simple Excel import to a SQL table working?  I would do that first.

    Barring that...if you are comfortable, post an e-mail, and I will e-mail you a sample dtsx file.  I got this to work before I described the steps.

  • November 21, 2006 at 11:49 am

    #673278

    Many thanks in advance for continuously helping me out. J I sent you a mail requesting you the file. 

     

    Regards

    Utsab Chattopadhyay


    CDBA

Viewing 8 posts - 1 through 7 (of 7 total)

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