Post reply

Import data from Excel and select statement and insert to another table

  • June 25, 2009 at 5:40 am

    #133384

    I am having a table "Categories" with the structure as below

    CatId INT IDENTITY,

    Descr VARCHAR(50),

    CreatedBy INT (FK Users(UserId))

    CreatedDate DATE (Default GetDate())

    I have an Excel sheet with set of Descriptions.

    I have added Excel datasource and DataConversion task to get the final Descr.

    I have added OLEDB Destination control and want to insert data from the DataConversion task(Descr) and OTHER

    COLUMNS I NEED TO GET SOME Sql statement.

    I placed Execute SQL Task and added the following sql statement...

    select (SELECT UserId

    FROM dbo.Users

    WHERE (LoginId = 'import')) as UserId, GetDate() as CreatedDate

    How can I link Execute SQL task to the OLEDB Destination and map columns from Data conversion task and sql statement

    thanks

  • June 25, 2009 at 5:51 am

    #1015228

    Here is one way.

    1) Create a package variable - UserID

    2) Modify your ExecuteSQL task so that the result of the query is mapped to UserID.

    3) Add a Derived Column transformation in your data flow - and set its value to be UserID

    Now you will have a new column in your source data that you can map to your destination.

    --Edit

    Your query is a little ungainly. Why not this?

    SELECT UserId, GetDate() CreatedDate

    FROM dbo.Users

    WHERE LoginId = 'import'

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 25, 2009 at 6:57 am

    #1015263

    Hi,

    Thanks for the reply. I created variable and mapped to the results of execute sql task.

    Execute SQL task is on Control Flow and my data retrieval from Excel is in Data Flow.

    How can I drag output of execute sql task and assign to dataflow task.

    As it is storing the results to variables, which task will execute first(Data Flow task or Execute sql task)?

    How can I use derived column and use the variable value to that column and connect to OLEDB destination.

    I am very new to SSIS. Can you guide me?

  • June 25, 2009 at 10:07 am

    #1015425

    Hi

    Thanks for the reply. I am able to get the data from execute sql task and link to Dataflow task.

    Data flow task will have Excel data source and data conversion tasks and a conditional split task.

    For testing, I added Row count task from the Conditional Split task.

    If I drag the data flow task to script task and displaying the row count variable, I am getting 8.

    I want to assign the output of conditional split i mean data flow task to a result set. so that I can link to for each loop container.

    How can I do this?

  • June 25, 2009 at 6:26 pm

    #1015707

    Your original post did not suggest any reason why you would need to use a conditional split - can you describe why you are doing this?

    You talk about 'dragging' from data flow to script - I do not know what you mean. Dragging of variables is not possible in the way you suggest.

    You data is already running through a row-by-row pipeline - why do you feel the need to process it using a Foreach container?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 26, 2009 at 2:05 am

    #1015829

    Hi,

    I have different columns in excel sheet. I want to import only the first column data.

    So, its grabing NULL values. So like to get non null values. I used conditional split for that.

    Let me explain what I have done.

    Execute SQL Task: To get the UserId and CurrentDate values and returns single row resultset.

    Storing thhose values in Package level variables.

    Data Flow Task: Excel Source -> Data Conversion Task -> Conditional Split

    Which task I can use after that to insert records to my table from the output of conditional split and variables.

    thanks for the replies.

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

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