I am looking for SSIS solution , and i am new with excel transformations..on SSIS. Like shown Below, i need load data from current column. I appreciate your help . Investment Jan 2011 Feb 2011 March 2011 Apr 2011 Investment A 2000 10000 200000 10000 Investment B 10000 200000 30000 200000 Above data is in the spread sheet in xls format i get everymonth, need to load the data into sql server 2005 . i am using this code SELECT * from [Sheet3$A3:C11], to collect the data from spread sheet for now! but everymonth , i need to change.. I am looking for is there way i can only capture current month culumn, sometimes the current month might not be April. It might be March if we dont get Feed. Thanks Jay

What is your requirement? It is always good to provide some sample input and expected output to understand the requirement clearly.Nitesh Rai- Please mark the post as answered if it answers your question

Above data is in the spread sheet in xls format i get everymonth, need to load the data into sql server 2005 . i am using this code SELECT * from [Sheet3$A3:C11], to collect the data from spread sheet for now! but everymonth , i need to change.. I am looking for is there way i can only capture current month culumn, sometimes the current month might not be April. It might be March if we dont get Feed. Thank youJay

One option is : Load the data into a table , pivot the data and then load into another table with some ID autogenerated (identity)and select the row of max(id) in a DFT most probably and load the data into the required table. A check to be made on the pivot. When pivot , it should be either of top 1 * or the max(id).Request to please mark my post as an answer if I helped you to resolve the issue or vote up if I helped you.Thanks. Regards and good Wishes, Deepak.

if the number of the column are fixed you can do something about it and it can be like 1- yes you are using the right thing by using "SELECT * from [Sheet3$A3:C11], " 2- read the excel sheet as text (use IMEX = 1 in your connection string) 3- check OFF the option "FirstRowColumnname" = FALSE -- up to now you are reading the file and the column as a record 4- now you can bring in the data into a staging table or still use SSIS 5- you must find the row that is not numeric , thats how you find the header 6- read that record and fing the date and month 7- with a adhoc query make a INSERT INTO statment and insert into the destination table Note : you may need to PIVOT the data good luckSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

You can use a variable for current month and write a dynamic sql. Define a package scoped variable varCurrMnth (with some proper default value) Define a package scoped variable varExcelSQL with its expression as : "Select " + @[User::varCurrMnth ]+ " as CurrentMonth from [Sheet1$]" Have a data flow task and use excel source with data access mode as sql command from variable. Select the variable varExcelSQL Now you can pass the value to the variable and data will be fetched accordingly. If you want to have some mechanism which will populate the varCurrMonth variable with the current month and year, you can have an execute sql task to generate a string like Jan2011 or Feb2011 using Getdate() and substring funciton and assign the same to varCurrMonth. Ex: select SUBSTRING(CONVERT(varchar(20),getdate(),106),3,9) Nitesh Rai- Please mark the post as answered if it answers your question

Thank you all! How to find a Current month on Excel sheet Dynamically, that is my problem, sorry i am new bee.! :-(Jay

You can query the Excel sheet using Jet OLEDB: Excel 2003 or older: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel And excel 2007 or newer: SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=File_Path\Your_Excel_Workbook.xlsx', [Sheet1$])Arthur My Blog