June 9, 2011 at 11:08 am
Dear All,
I had tried to search over internet and could not find the answer. I am trying to store a value in variable in SSIS from the SQL Select Statement. I had created the ExecuteSQL Task and I am trying to use Result Set tab to return a scalar value. The ExceuteSQL Task works fine however it does not update the variable. I am sure this must be simple but I am struggling to get this working. Please can someone help on this one?
Many thanks in anticpation.
Best regards,
DV
June 9, 2011 at 12:42 pm
If your select statement retuns a single row, for example:
[font="Courier New"]select top 1 a, b ,c from myTable[/font]
where a is int, b is smallint and c is varchar.
To get these values into SSIS variables from an "Execute SQL Task".
1) Create the variables of the correct data types (Int32, Int16 and String in this example)
2) Open up the "Execute SQL Task"
3) Set the "Results" property to "Single Row". The default value if "None"
4) Open the "Results Set" panel. The widgets on this panel will be greyed out if ResultSet = None.
5) Add 3 entries.
6) 1st Row: Set result name to "0" (zero), 0-based index of the column returned by the select statement, and select the varaible to receive the Int32 value for a in the above select statement.
7) 2nd Row: Set result name to "1", 0-based index of the column returned by the select statement, and select the varaible to receive the Int16 value for b in the above select statement.
8) 3rd Row: Set result name to "2", 0-based index of the column returned by the select statement, and select the varaible to receive the String value for c in the above select statement.
Hope that this helps.
June 10, 2011 at 2:34 am
Thank you very much for replying back. I am doing exactly same and I still can't get it working for some reason. I am very new to SSIS and I dont know how to overcome this issue... it is disappointing now that I am struggling to solve this one.
June 10, 2011 at 2:48 am
Call store procedure and in store procedure define parameter as OUTPUT.
June 10, 2011 at 3:04 am
Thank you for the suggestion. I'll try the same... I wanted to understand why it can't work from a SELECT statement and I need to create a STORED PROCEDURE just for this one. I had searched for 3 hours on web and I am following the same method as others but I can't get it working. :crying::crying:
June 10, 2011 at 3:33 am
It should work with just a SELECT statement. How do you know the variable isn't being updated?
I would recommend that you use a stored procedure in any case, if this task is always going to run against the same server. This is because, if you want to tweak your query, it's much easier to alter a stored procedure than it is to edit a package. You need to use the Parameter Mapping window instead of Result Set if you decide to go down that road.
John
June 10, 2011 at 4:01 am
[font="Verdana"]Hi John,
I am creating a breakpoint after PostExecute Event. And then I am looking at the variables overiew window in BIDS and it shows the 0 value instead of the value which returns from stored procedure. I am not sure if this is right way of checking the variable. Please bare with my ignorance but can you guide me to a link to work on with SP output parameter passing variable value?
Many thanks in anticipation.
Cheers - DV[/font]
June 10, 2011 at 4:07 am
DV
Just search for execute sql task parameter mapping - you should find loads of articles. Also, try searching for your original problem - somebody might already have solved it. Try something like execute sql task result set does not update variable.
John
June 10, 2011 at 6:18 am
Post the code you're using to SELECT the variable value.
Then post the package variable(s) you're using, what their names are, and what level these package variables are created as.
June 10, 2011 at 6:59 am
Hi,
Thank you for your time. Here is what you need...
SELECT STATEMENT (Returns a date) :
SELECT Min(EffFrom) as MyDate FROM APE.dbo.CurrPeriod
I am also attaching the screenshots of Execute SQL Task window & Variable overiew window. I almost give up unless someone can help me...
June 10, 2011 at 7:00 am
Just adding to my previous post... the scope the variable is package level.
Thanks - DV
June 10, 2011 at 7:05 am
DV
Please will you post DDL and a sample of data for the CurrPeriod table.
Thanks
John
June 10, 2011 at 7:08 am
Deepak,
Go into SSMS and run your SQL Query against the proper database. Does it actually return a value?
EDIT: More to the point, does it only return 1 value?
June 10, 2011 at 7:16 am
[font="Verdana"][font="Verdana"]Yes, it returns this value...
2011-04-01 00:00:00
I had also attached the sample data for your reference...
_______________________________________________________________________________________________________________________________________
_______________________________________________________________________________________________________________________________________
CREATE TABLE [dbo].[CurrPeriod](
[Carrier1] [nvarchar](3) NULL,
[Carrier1Name] [nvarchar](39) NULL,
[FlightNo1] [nvarchar](4) NULL,
[Carrier2] [nvarchar](3) NULL,
[Carrier2Name] [nvarchar](39) NULL,
[FlightNo2] [nvarchar](4) NULL,
[CarrDom1] [nvarchar](2) NULL,
[CarrDom1Name] [nvarchar](39) NULL,
[CarrDom2] [nvarchar](2) NULL,
[CarrDom2Name] [nvarchar](39) NULL,
[DepAirport] [nvarchar](3) NULL,
[DepAirportName] [nvarchar](40) NULL,
[DepTerminal] [nvarchar](2) NULL,
[DepCity] [nvarchar](3) NULL,
[DepCityName] [nvarchar](39) NULL,
[DepState] [nvarchar](2) NULL,
[DepStateName] [nvarchar](39) NULL,
[DepIATACtry] [nvarchar](2) NULL,
[DepIATACtryName] [nvarchar](39) NULL,
[DepDOTCtry] [nvarchar](4) NULL,
[DepDOTCtryName] [nvarchar](39) NULL,
[DepReg] [nvarchar](3) NULL,
[DepRegName] [nvarchar](39) NULL,
[ArrAirport] [nvarchar](3) NULL,
[ArrAirportName] [nvarchar](40) NULL,
[ArrTerminal] [nvarchar](2) NULL,
[ArrCity] [nvarchar](3) NULL,
[ArrCityName] [nvarchar](39) NULL,
[ArrState] [nvarchar](2) NULL,
[ArrStateName] [nvarchar](39) NULL,
[ArrIATACtry] [nvarchar](2) NULL,
[ArrIATACtryName] [nvarchar](39) NULL,
[ArrDOTCtry] [nvarchar](4) NULL,
[ArrDOTCtryName] [nvarchar](39) NULL,
[ArrReg] [nvarchar](3) NULL,
[ArrRegName] [nvarchar](39) NULL,
[LocalDepTime] [nvarchar](4) NULL,
[LocalArrTime] [nvarchar](4) NULL,
[LocalArrDay] [nvarchar](1) NULL,
[LocalDaysOfOp] [nvarchar](7) NULL,
[ArrDaysOfOp] [nvarchar](7) NULL,
[Service] [nvarchar](1) NULL,
[Seats] [smallint] NULL,
[FstSeats] [smallint] NULL,
[BusSeats] [smallint] NULL,
[EcoSeats] [smallint] NULL,
[EffFrom] [smalldatetime] NULL,
[EffTo] [smalldatetime] NULL,
[LocalDaysOfOp1] [nvarchar](1) NULL,
[LocalDaysOfOp2] [nvarchar](1) NULL,
[LocalDaysOfOp3] [nvarchar](1) NULL,
[LocalDaysOfOp4] [nvarchar](1) NULL,
[LocalDaysOfOp5] [nvarchar](1) NULL,
[LocalDaysOfOp6] [nvarchar](1) NULL,
[LocalDaysOfOp7] [nvarchar](1) NULL,
[ElapsedTime] [nvarchar](5) NULL,
[FlyingTime] [nvarchar](4) NULL,
[GroundTime] [nvarchar](4) NULL,
[Stops] [nvarchar](2) NULL,
[IntAirports] [nvarchar](39) NULL,
[IntCities] [nvarchar](39) NULL,
[IntCountries] [nvarchar](26) NULL,
[AcftChange] [nvarchar](1) NULL,
[AcftChApt1] [nvarchar](3) NULL,
[AcftChApt2] [nvarchar](3) NULL,
[AcftChApt3] [nvarchar](3) NULL,
[GeneralAcft] [nvarchar](3) NULL,
[GeneralAcftName] [nvarchar](39) NULL,
[SpecificAcft] [nvarchar](3) NULL,
[SpecificAcftName] [nvarchar](39) NULL,
[SecondAcft] [nvarchar](3) NULL,
[ThirdAcft] [nvarchar](3) NULL,
[FourthAcft] [nvarchar](3) NULL,
[Freightons] [float] NULL,
[PassClass] [nvarchar](10) NULL,
[FreightClass] [nvarchar](2) NULL,
[Routing] [nvarchar](45) NULL,
[StatMiles] [smallint] NULL,
[NautMiles] [smallint] NULL,
[Km] [int] NULL,
[DistStMiles] [smallint] NULL,
[DistNtMiles] [smallint] NULL,
[DistKM] [int] NULL,
[Restrictions] [nvarchar](3) NULL,
[ShAirlDes] [nvarchar](3) NULL,
[MultCDes] [nvarchar](9) NULL,
[DupMarker] [nvarchar](1) NULL,
[DupCar1] [nvarchar](7) NULL,
[DupCar2] [nvarchar](7) NULL,
[DupCar3] [nvarchar](7) NULL,
[DupCar4] [nvarchar](7) NULL,
[DupCar5] [nvarchar](7) NULL,
[DupCar6] [nvarchar](7) NULL,
[DupCar7] [nvarchar](7) NULL,
[DupCar8] [nvarchar](7) NULL,
[OpCar] [nvarchar](1) NULL,
[Comment] [nvarchar](3) NULL,
[AcftOwnerCode] [nvarchar](3) NULL,
[AcftOwnerCodeName] [nvarchar](39) NULL,
[CockpitCrewCode] [nvarchar](3) NULL,
[CockpitCrewCodeName] [nvarchar](39) NULL,
[CabinCrewCode] [nvarchar](3) NULL,
[CabinCrewCodeName] [nvarchar](39) NULL,
[LongLeg] [nvarchar](1) NULL,
[MaxTakeOffWeight] [smallint] NULL,
[HoldVolume] [smallint] NULL,
[RangeKm] [smallint] NULL,
[RangeStatMiles] [smallint] NULL,
[RangeNautMiles] [smallint] NULL,
[CruiseSpeed] [smallint] NULL,
[Category] [nvarchar](2) NULL,
[Manufacturer] [nvarchar](3) NULL,
[Ghost] [nvarchar](1) NULL,
[SubGovnApp] [nvarchar](1) NULL,
[FltDup] [nvarchar](1) NULL,
[Frequency] [int] NULL,
[ASMs] [real] NULL,
[ASKs] [real] NULL,
[TotalSeatCapacity] [real] NULL,
[TotalTonnage] [float] NULL
) ON [PRIMARY]
GO
[/font][/font]
June 10, 2011 at 7:19 am
Eh-hem. I think you need to go back and look at your variable scope. The picture you posted does not jive with the comment "I have a package level scope" that you made right after the post.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply