Post reply

Creating SSIS Package

  • March 12, 2010 at 2:35 pm

    #143522

    Hi Experts,

    I am using Business Intelligence Development Studio, my purpose is to take the data from the AdventureWorks , table HumanResourse.EmployeePayHistory.

    Then divide the data depends upon the year and send it to the excel sheet to my system, to the specify folder.

    For example, all the data in the year 1996 will go the excel sheet in the path c:/data/1996/1996data.xlsx

    and the data of 1997 will go to the path c:/data/1997/1996data.xlsx

    and so forth...

    As I am not aware about the year, like it may be start from 1990 to 2009.. Folder should be created dynamically based on the data.

    Please help me to proceed further. Thanks in advance.

    Kindly note, I am a newbie in SSIS.

  • March 12, 2010 at 2:47 pm

    #1133022

    Here is what I would do. Create a variable and store the minnimum value in that variable by doing a select Min(distinct(year)... something like that and store the result in a variable. Then setup a loop and run through the query using your year variable and output to your excel spreadsheet. In the excel spreadsheet use the expresion property and enter a string using the variable. So each time it looped through it will use the variable to open the correct file and run the appropriate query for that file.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • March 13, 2010 at 12:03 am

    #1133148

    Hi,

    Thanks for your useful suggestion, can you please guide me the step by which I can set the folder path property to be dynamic, if any tutorial available.

    Like I am able to filter the data according to the variable now I want to create a folder with that year name.

    Thanks again for your valuable suggestion.

  • March 14, 2010 at 9:26 am

    #1133394

    Please reply so that I can proceed further.

    Thanks

  • March 15, 2010 at 7:10 am

    #1133675

    In an execute SQL task do something like Select 'C:\Myfolder\Myfile'+ year(Getdate()) +'.txt'

    asign this result set to a variable.

    select the file in the connection manager. Just a single click. then look in the properties pane. If you do not have it open right click on the file and select properties. you will see a line in the properties pane for Expressions. If you look to the far right of that side you will see the elipse "..."

    If you click that an expression wido will open. Choose the property of Connection string and then asgin the expression of your variable with the file name in it.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • March 15, 2010 at 10:14 am

    #1133859

    Thanks for replying.This is what I was looking for.

    Please let me know how to mark your post as answer as I am not able to find any button for that.

  • March 15, 2010 at 1:51 pm

    #1134042

    I agree with Dan. There are multiple ways of doing that.. the other way would be to write a distinct(year) in Execute SQL task in a way that it returns all the distinct years and then using full result set functionality pass the values in the the Full result set variable to For each loop (ADO enumerator). Inside For Each you can have an execute SQL task to create the excel file and Data Flow to load appropriate data into excel

    You can use other variables to hold SQL to create a Excel Path and File

    For ex: @year = 1996

    @folerPath = "c://data//"+@year+"//"+@year+"data.xlsx" (C:/data/1996/1996data.xlsx)

    @createExcelFile = "Create Table"+@year+

    (

    ColumnNames

    )

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

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