August 24, 2011 at 5:41 pm
Hi,
I need the below things to be done in SSIS
Dynamically read the data from file (.txt or excel) and pass it to variable and from that variable to sp
SP is contains 3 param
SP_test @param1,@param2,@param3
@param1 needs to receive the data from file (.txt or excel)
@param2 variable as startdate
@param3 variable as Enddate
In (.txt or excel) it will contains data like
123
456
789
987
654
321
.............
so on it need to read the data for all the datas in the file and pass it to variable and from that to sp or directly to sp without variable.
I need to give startdate and enddate apart from the file.
Any links for this or any good suggestions for the above.
Thanks
Parthi
Thanks
Parthi
August 24, 2011 at 5:47 pm
Drop it to a recordset destination. The destination will need a global package variable of type object.
After the dataflow, initiate a for each loop, and use ado recordset as the loop. For each column in the recordset apply it to a package variable. Feed that variable into an execute SQL task inside the loop as a parameter.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 25, 2011 at 1:46 am
Hi,
Thanks for the replay
This is the file i am having
1996
1997
1998
1999
2000
2001
2002
2003
USE AdventureWorks
GO
Create Procedure Sample_ee (@hiredate Smallint,@Country Varchar(50) =null)
as
Begin
Select ee.EmployeeID,ee.NationalIDNumber,ee.BirthDate,ee.HireDate,
pa.AddressLine1,ps.Name,pc.Name from HumanResources.Employee as ee
Join HumanResources.EmployeeAddress as ea on ea.EmployeeID=ee.EmployeeID
Join Person.Address as pa on pa.AddressID=ea.AddressID
Join Person.StateProvince as ps on ps.StateProvinceID=pa.StateProvinceID
Join Person.CountryRegion as pc on pc.CountryRegionCode=ps.CountryRegionCode
Where Year(ee.HireDate)=@hiredate and pc.Name=ISNULL(@Country,pc.Name)
End
EXEC Sample_ee 1998,null
EXEC Sample_ee 1999,null
EXEC Sample_ee 2001 ,'Canada'
and the sp "Sample_ee" has 2 param one is year from txt file and another i need to give externally Output of each year needs to be stored an excel sheet with each sheet one year i.e say 1996 year in sheet1,1997 in sheet2,1998 in sheet3 and so on.. in an single sheet.
Thanks
Parthi
August 25, 2011 at 1:02 pm
parthi-1705 (8/25/2011)
and the sp "Sample_ee" has 2 param one is year from txt file and another i need to give externally Output of each year needs to be stored an excel sheet with each sheet one year i.e say 1996 year in sheet1,1997 in sheet2,1998 in sheet3 and so on.. in an single sheet.
That's a whole different issue, and I think you meant in a single workbook.
The above process is how to row-by-row call a procedure. However, what you're looking to do instead is use a dataflow with a stored procedure in the source definition using parameters instead. Same concept, just need to do more with it.
There's a way to treat Excel as a database object, where you can run commands like CREATE TABLE and the like against it to generate sheets, but I'm not sure what it is offhand. I try to avoid dynamically adjusting Excel if I can help it. Google around on keywords: excel sql odbc dml ssis. I think you'll find it with a couple of those in the combination.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply