December 13, 2010 at 6:31 am
Ok I stumped!
I have an SSIS OLE DB Source that uses a T-SQL Statement. I've tried and tested the T-SQL in SSMS and also in the package both with success,unitl I try to add parameters..... :crazy: My package provides a start and end date, that I want to use to filter the source record set.
Using dates (as I will highlight below) works fine. When I try and substitute these dates with a question mark (?) and try to assign a paramter, I then get the following error:
TITLE: Microsoft Visual Studio
------------------------------
Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.
------------------------------
ADDITIONAL INFORMATION:
Syntax error, permission violation, or other nonspecific error (Microsoft SQL Native Client)
I cant see what is wrong with what I am trying to do - I've done it several times with other packages without issue.
Heres my code and where it's faulting:
/*
Project/DB:swisslog_dw
Author:David Lumley
Purpose:pairs up damage status change records
DateChanges / Amendments Made
--------------------------------------------------------------------------------------
10-12-10Created v1
*/
SELECT CASE
WHENDATEPART (HOUR,s1.create_dt) < '06' THEN CAST(LEFT(DATEADD(DAY,-1,s1.create_dt),12)as datetime)
ELSECAST(LEFT(s1.create_dt,12)as datetime)
END as[damage_return_dt]
,s2.from_licas[lic_no]
,s2.from_prod_keyas[prod_key]
,CASE
WHEN s2.reason_code IS NULL THEN '**'
ELSE s2.reason_code
ENDas[adjust_reason_code]
,CASE
WHEN s1.from_attr20 IS NULL THEN '0'
ELSE s1.from_attr20
ENDas[damage_type]
,s2.[no]as[tx_no_to_damage]-- Tx no for the pack put to damage status
,s1.[no]as[tx_no_from_damage]-- Tx no for the pack returned from a damage status
,s1.[from_status]as[from_status_code]
,s1.[to_status]as[to_status_code] -- Not required as all packs being returned are to a status code of 0
--,s2.create_dtas[damage_dt]-- Date the pack become damaged / unvailable
--,s1.create_dtas[repaired_dt]-- Date the pack returned to an available status
,s2.[user_name]as[put_to_damage_by]
,s1.[user_name]as[put_to_available_by]
,CASE
WHEN s2.lic_weight IS NULL THEN 0
ELSE s2.lic_weight
ENDas[pack_weight_orig]
,CASE
WHEN s1.lic_weight IS NULL THEN 0
ELSE s1.lic_weight
ENDas[pack_weight_new]
,1as[no_of_packs]
,DATEDIFF(HOUR,s2.create_dt,s1.create_dt) as[time_damaged_hrs]
FROMextract_saves_trans_002s1 -- Record of packs returned to available status
INNER JOIN extract_saves_trans_002S2 -- record of the pack being moved to a no available status
ONs1.from_lic=s2.from_lic
ANDs1.[no]>s2.[no]
this is where my error is being generated from. As mentioned - if I use the date it's fine, trying to change it to a parmeter throws and error.
WHEREs1.create_dt>=? --'2010-12-01 06:00:00' --?
ANDs1.create_dt <'2010-12-01 06:00:00' --?
ANDs1.from_category<>'NON PRODUCT'
AND
(
-- Identify pack the movements from damage to available
(
(S1.[from_status]='1' -- Damaged
ors1.[from_status]='3' -- Damage & Reject
)
AND(s1.[to_status]='0')
)
-- Identify pack the movements from available to damage
AND(s2.[from_status]='0'
AND(s2.[to_status]='1' -- Damaged
ORs2.[to_status]='3' -- Damage & Reject
)
)
-- Find the last transaction that put the pack to a damage status
-- that was prior to the transaction returning the pack to an available status
ANDs2.[no] =(select MAX(s3.[no])as [no]
FROMextract_saves_trans_002s3
wheres3.from_lic=s1.from_lic
AND(s3.[from_status]='0'
AND(s3.[to_status]='1' -- Damaged
ors3.[to_status]='3' -- Damage & Reject
)
)
ANDs3.[no]<s1.[no]
)
)
Would really appreciate any possible answers as to why this occuring.
thanks all
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 13, 2010 at 7:01 am
Update:
For those interested I resolved the issue, bit it's a little bit of a mystry as to why it was an issue.
I decided to try and strip out all the comments, clutching at straws I know, but it worked!
for some reason that I cant comprehend, the comments on the lines about the s1.from_status appeared to trigger the error. Remove these comments I could then assign parameter values.
,s2.[no]as[tx_no_to_damage]-- Tx no for the pack put to damage status
,s1.[no]as[tx_no_from_damage]-- Tx no for the pack returned from a damage status
,s1.[from_status]as[from_status_code]
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 13, 2010 at 7:12 am
Dave,
Great catch! I was just composing a sarcastic response about the fact that the oledb connection does not allow comments when I switched back and saw your response.
I don't know what your header or any other comment line for that matter, disables the oledb provider from properly parameterizing your query, but it does.
December 13, 2010 at 7:57 am
Puzzling.
But I thought you could use comments in OLE DB source components as it would basically execute as a T-SQL command?!
I still have comments in my statement (see below) - the only subtle difference being I guess that the commented lines are commented before any T-SQL might have to be parsed.
SELECT CASE
WHENDATEPART (HOUR,s1.create_dt) < '06' THEN CAST(LEFT(DATEADD(DAY,-1,s1.create_dt),12)as datetime)
ELSECAST(LEFT(s1.create_dt,12)as datetime)
END as[damage_return_dt]
,s2.from_licas[lic_no]
,s2.from_prod_keyas[prod_key]
,CASE
WHEN s2.reason_code IS NULL THEN '**'
ELSE s2.reason_code
ENDas[adjust_reason_code]
,CASE
WHEN s1.from_attr20 IS NULL THEN '0'
ELSE s1.from_attr20
ENDas[damage_type]
,s2.[no]as[tx_no_to_damage]
,s1.[no]as[tx_no_from_damage]
,s1.[from_status]as[from_status_code]
,s1.[to_status]as[to_status_code] -- Not required as all packs being returned are to a status code of 0
--,s2.create_dtas[damage_dt]-- Date the pack become damaged / unvailable
--,s1.create_dtas[repaired_dt]-- Date the pack returned to an available status
,s2.[user_name]as[put_to_damage_by]
,s1.[user_name]as[put_to_available_by]
,CASE
WHEN s2.lic_weight IS NULL THEN 0
ELSE s2.lic_weight
ENDas[pack_weight_orig]
,CASE
WHEN s1.lic_weight IS NULL THEN 0
ELSE s1.lic_weight
ENDas[pack_weight_new]
,1as[no_of_packs]
,DATEDIFF(HOUR,s2.create_dt,s1.create_dt) as[time_damaged_hrs]
-- Record of packs returned to available status
FROMextract_saves_trans_002s1
-- record of the pack being moved to a no available status
INNER JOIN extract_saves_trans_002S2
ONs1.from_lic=s2.from_lic
ANDs1.[no]>s2.[no]
WHEREs1.create_dt>=?
ANDs1.create_dt <?
ANDs1.from_category<>'NON PRODUCT'
AND
(
-- Identify pack the movements from damage to available
(
(S1.[from_status]='1' -- Damaged
ors1.[from_status]='3' -- Damage & Reject
)
AND(s1.[to_status]='0')
)
-- Identify pack the movements from available to damage
AND(s2.[from_status]='0'
AND(s2.[to_status]='1' -- Damaged
ORs2.[to_status]='3' -- Damage & Reject
)
)
-- Find the last transaction that put the pack to a damage status
-- that was prior to the transaction returning the pack to an available status
ANDs2.[no] = (select MAX(s3.[no])as [no]
FROMextract_saves_trans_002s3
wheres3.from_lic=s1.from_lic
AND(s3.[from_status]='0'
AND(s3.[to_status]='1' -- Damaged
ors3.[to_status]='3' -- Damage & Reject
)
)
ANDs3.[no]<s1.[no]
)
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
December 13, 2010 at 8:32 am
Dave,
Sorry I mispoke, I was referring to /*comment*/ or -- comment as "any comment line" and should have clarified. Anything before the SELECT statement will disable the SQL from being parameterized and cause the error.
December 13, 2010 at 9:45 am
ah ok.
Well learnt something new today.:-)
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 20, 2011 at 10:08 am
Paul
sorry to bother you again - this is a performance question - but related to my original post.
When creating a stored procedure, an the execution plans is created for sp and reused every time the sp is called, unless recompiled is recompiled at some point.
when SSIS executes a SQL command (as per my example) is does this need to create an execution plan ( i guessing the answer is yes)?
What in your opion is the better method to use - an exec stored procedure in the OLE DB source or a T-SQL command?
I'd be interest to know - thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
January 20, 2011 at 1:44 pm
(…eye twitching…)
You struck a nerve…
http://msdn.microsoft.com/en-us/library/aa175244(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/ms181055.aspx
The two links provided are similar; ones for SQL Server 2000 while the other is for SQL 2008 R2.
Bad code is bad code. If you’re stuffing junk code in a stored procedure or a SSIS task/Data Flow component, it doesn’t matter, existing execution plans will NOT be reused if the code is not fully qualified or if the plan has been “aged” out of the procedure cache.
Now, if you’re sending a 65 Mbyte of T-SQL code, from a task or component in your packages on your SSIS Server, to your databases on your SQL Server, halfway around the world, over a 9600 baud modem, you should be fired, but aside from the obvious environmental issues, it should be a matter of choice.
Factors you need to consider:
• Statement Construction; is it fully qualified/parameterized
• Statement Complexity/Size
• Statement Use; how often
If you’re not fully qualifying your statements as specified in both links, the existing execution plans are not likely to be reused. See the paragraph above the Ageing Execution Plans, in the first link, and Removing Execution Plans from the Procedure Cache, in the second. In the first link they include the database name as part of the “fully qualified” statement where in the second it’s just the schema and table name.
When you have a stored procedure, you have an execution plan for the procedure call as well as for each select statement that it may contain. The stored procedure is not “compiled” as I’ve heard many people ignorantly say. In SQL 6.5 and earlier, there was a significantly different execution strategy, of which I am NOT educated on.
So, you’ve constructed wonderful T-SQL, fully qualified and parameterized. If it’s a Select * From small benign table, it’s unlikely an execution plan will get built, let alone reused before it’s kicked out of the cache club. Finally, if you’re T-SQL, whether in a stored procedure or coming from your task/component is used once a month on a highly transacted server, it’s likely the execution plans have aged and been removed from the procedure cache as well.
Personally, I like using my SSIS as a “business layer” a one stop shop for all of my T-SQL. I don’t ever know if my sources will allow me to create stored procedures etc, in their databases, so as a standard practice, I expect to put my T-SQL in the tasks and component of my SSIS packages.
Sadly, I have seen many great ideas go overlooked or “pooh-poohed” because the technical lead wanted to use only stored procedures because they were compiled or for security reasons when the application used a proxy to the database. You need to weigh your options which it sounds like you do/are.
Good Luck,
Paul
January 21, 2011 at 2:11 am
well,
to a large extent, I've got the choice, with few little restrictions on what I choose to do - the only limiting factors really are potentially data related.
I'm moving a data warehouse from SQL2000 DTS to SQL 2005 and SSIS. On SQL2000, DTS has been predominately used as the 'control flow' method with all the work logic in stored procedures in the database.
On SQL2005, I'm starting from scratch to a degree, to
a) make better use of the newer features available
b) to really go over what was done and really scrutinize where I can make improvements.
so - as you say - I can chose which to use.
thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
July 2, 2014 at 8:40 pm
Encountered the same issue and spotted this post so thanks for pointing me in the right direction.
For me removing the word "for" from the comment text fixed it, looks like this may the case here too.
Apparently FOR is the new super keyword.
December 19, 2014 at 5:01 am
hey guys,
i can't do what are you talking about!
i have this part of a ququerynd i can't pass the parameter through it (it's a date).
"select coalesce(SUM(case when Dat_Echeance=? then 0 else Taux * Qte_Devise end ) ,0) AS Qte_Ecrs_Tx FROM Wrk_Agreg_Faible
WHERE Id_Tranche_Rec_Dur_Init IS NOT NULL
AND Cod_Famille_Taux <> 'S' -- hors produits structurés
GROUP BY Dat_Mvt_Ecrs
,Cod_Type_Tcn_Transcod
,Cod_Devise
,Cod_Famille_Taux
,Id_Fam_Tranche_Rec
,Id_Tranche_Rec_Dur_Init
,Cod_Emet
,Lib_Zone_Geo_Mec
,Id_Categorie_Not;
"
the error is:
"It is not possible to extract parameters from the SQL command.
The provider can prevent analyze the parameter information from the command.
In this case, use the access mode "SQL command from variable" in which the
entire SQL command is stored in a variable.
violation of authorization or other non-specific error (Microsoft SQL
Server Native Client 10.0)"
thank you for rsponding me.
December 19, 2014 at 6:53 am
hi everyone,
Even when i remove the comments it deosn't work.
here is my code:
select coalesce(SUM(case when Dat_Echeance=? then 0 else Taux * Qte_Devise end ) ,0) AS Qte_Ecrs_Tx
FROM Wrk_Agreg_Faible
WHERE Id_Tranche_Rec_Dur_Init IS NOT NULL
AND Cod_Famille_Taux <> 'S' -- hors produits structurés
GROUP BY Dat_Mvt_Ecrs
,Cod_Type_Tcn_Transcod
,Cod_Devise
,Cod_Famille_Taux
,Id_Fam_Tranche_Rec
,Id_Tranche_Rec_Dur_Init
,Cod_Emet
,Lib_Zone_Geo_Mec
,Id_Categorie_Not;
I have this error when i execute the old db source:
"It is not possible to extract parameters from the SQL command.
The provider can prevent analyze the parameter information from the command.
In this case, use the access mode "SQL command from variable" in which the
entire SQL command is stored in a variable.
violation of authorization or other non-specific error (Microsoft SQL
Server Native Client 10.0)"
Thank you for your response
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply