I have a lot of packages that I run Stored Procs with. I don't write the procs just build the packages and export the data.

In my Source editor I always use this:

SET FMTONLY OFF;
SET NOCOUNT ON;
exec [Extract].[usp_HB_VEI_Outbound_Collections] ?,?,?

and in my Set Query Parameters I use the same name for the parameters as they are in the proc

@StartDate, @EndDate and then point them to variables. E.g. user::prmStartDate, user::prmEndDate

Now today I am getting the error "No Value given for one or more required parameters" on a new package. But when I went back to old packages to make sure I was building the SQL Command text and parameters correct I found all my old packages trigger this error now. 

The only thing I can think of is that I have installed SQL Server 2012 data tools on the box and that is causing it or the SQL Server has been updated with a patch or hotfix. 

Has anyone else seen this? Is there a work around?

Thanks,

Phil

Did you set any default value of SSIS variables in SSIS BIDS 2008 ?

1st of all SET FMTONLY OFF should not be used (deprecated in SQL Server 2012 even).

SET NOCOUNT ON probably has nothing to do, but belongs to inside a stored proc.

I guess you get the error in preview and thus if you remove the two lines it must work.

Removing the Set statements stop the error from appearing.

I am not sure what you mean by the default values. user:prmStartDate and the other two variables are global to the dts package and already set. The thing is I have about a dozne other packages that run fine but now when I reopen them to test this error they all cause it now. Something in the 2012 updates has corrupted the system or something. I am going to rebuild my VM and dedicate it to 2008 development and build another one for 2012.

This SQL contains really 3 statements SET FMTONLY OFF, SET NOCOUNT ON, and the stored procedure call

SET FMTONLY OFF;
SET NOCOUNT ON;
exec [Extract].[usp_HB_VEI_Outbound_Collections] ?,?,?

I think this was not possible in versions of SSIS earlier than 2012, but might be wrong. This is because the parameters are assigned to the 3rd statement and not to the 1st statement.

SSIS 2012 and Visual Studio 2012 with SQL Server Data Tools 11.1.21.208.0 does not complain about this.

I think this was not possible in versions of SSIS earlier than 2012, but might be wrong. 

I think I wasn't clear. Every package I have made has been done in Sql Server Business Intelligence Development Studio\Visual Studio Team System 2008

Only after installing SQL Server Data Tools on the same box did I start having this problem.

I use "SET FMTONLY OFF" because in most of the procedures I get handed to create exports from they use temp tables and this fixed the issue.
http://stackoverflow.com/questions/1579476/using-temp-tables-in-ssis

But even so the current query I am trying to access only has a couple parameters and no temp tables. 

Just to reiterate. When I open SSIS packages just written a few months ago on the same machine as I am using today they all have exclamation points on the OLE SRC objects and give me the error.

The bit I am not clear about is whether you are opening the packages with Sql Server Business Intelligence Development Studio\Visual Studio Team System 2008 or not.

If you are and you did not get any errors before installing SQL Server 2012, I do not understand why you get any errors now.

Yes I am using the 2008 version of BIDS. The only thing that has changed is that  the source data server was upgraded to SQL Server 2012 Enterprise. And SQL Data Tools 2012 on the local machine.

To target SSIS 2012 you may no longer use BIDS, VS 2010 or SSDT is the development environment for it.

Arthur - I do not want to correct you but this seems to me as SSIS 2008R2 targeting a SQL Server 2012 database. As far as I know this may work.

Developer_46038 - When the environment was upgraded, was the target database upgraded? You can find out by checking the compatibility level of the database.

With this setup,  I would upgrade the packages to SSIS 2012 using a package deployment model (package deployment model is roughly equivalent to what SSIS 2008R2 projects are).

If you are not doing anything too exotic, the package should upgrade painlessly and your stored procedure call should work (I have tested something similiar).

We have SQL servers at various versions. Right now we have one server that handles the scheduling and running of all our DTSX packages. And the packages point to databases on version 2005 through 2012. For this particular package I am working on it is being developed in VS 2008, will be scheduled to run from the SQL 2008 job server and the OLE source is 2012 enterprise. 

I found a workaround though. If I hard code the parameter values first, preview and get my columns. And then remove the values and put the question marks back in and click ok then the package does not throw an error. So for some reason VS2008 and Sql Server 2012 don't like talking to each other through parameters and variables.

I was thinking I should ask to see if I can use SQL Data Tools 2012 to build the package and still run it from the 2008 server. But then I remembers VS 2012 doesn't play nice with older versions of Visual Sourcesafe\Team explorer. All this trouble to get Tabular Data Modeling.

I just tried creating a package with SQL Server Data Tools 2012 with an OLE DB source pointing to a 2012 SQL server database and I get the same error. No value given for one or more required parameters. Uggg.

My command text:

EXEC extract.usp_HB_Tea_Leaves_CPT_Code ?,?

From profiler:

exec [sys].sp_describe_first_result_set N'EXEC extract.usp_HB_Tea_Leaves_CPT_Code @P1,@P2',N'@P1 datetime,@P2 datetime',1

I don't get it.

There is something odd in the stored procedure I suspect.

Can you share this insight omitting any sensitives?

CREATE PROCEDURE EXTRACT.GETSomething
(
@MyContainerId INT
)
AS
BEGIN
SELECT * FROM sys.allocation_units WHERE container_id = @MyContainerId

END
GO

In SSIS OLE DB Source Editor

SQL Command Text
EXEC EXTRACT.GETSomething ?

Variable Mappings Form
Parameter = @MyContainerId
Variable = user::intMyContainerId

The error when I click preview

To test, create a variable, vProc of type string. Go to the properties of this varibale, hit evaluate as expression to TRUE. Then in the ellipses button enter your query as follows

"
EXEC EXTRACT.GETSomething "+[User::vId]+"
"

where vId is the variable that will be passed to the query dynamically.

In ole db source, use data access mode as SQL command from varibale, and use vProc varibale from drop down.....

Let us know your test results !!!

I'll give that a shot. I was tinkering with that earlier but I was have trouble converting a Date variable to a string and then wrapping quotes around it. I was getting errorcannot convert string to date or something and assumed that was the problem.

Do you have an example of an SQL command from a variable expression that uses a Date variable? 

Thanks,

Phil

I think the . (dot) notation makes it break.

What if you create this proc names EXTRACT_GetSomething ?

Extract is the schema. And I have about 15 SSIS packages that work the same way. All use our Extract schema and most use a Start and End data parameter.

The . (dot) notation should not make it break. I have dozens of SSIS 2012 packages that use stored procedures in different schema and  never had a problem with them.

I think that Arthur is right the problem is the stored procedure itself.

There is something 'exotic'. I cannot reproduce your error with SSIS 2012.

If you try creating a package as follows:

1) Stored procedure - please note @MyContainerID must be a bigint, and int is too small

CREATE PROCEDURE EXTRACT.GETSomething
(
@MyContainerId BIGINT
)
AS
BEGIN
SELECT * FROM sys.allocation_units WHERE container_id = @MyContainerId

2) SSIS package as in screenshot below (if the screenshot below is too small, open on a new tab or save locally)

This runs without errors.

2 additional points:

a) the user namespace is actually User (note the capital 'U') unless you created a new one or change the existing one. SSIS is case sensitive with regards to variables namespaces

b) you need too map all parameters as shown in the screen above, if you are using an OLE DB data source or OLE DB command.

My VS 2008 Package fails against SQL Server 2012.

VS 2008 is installed on my VM.

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
Dec 28 2012 20:23:12 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Here is my 2012 package. Build on my local machine. Same error. I also tried changing the provider to "Native OLE DB\Microsoft OLE DB Provider for SQL Server" and also the Native 10.0 client

Is is possible something is buried in options somewhere? I had another developer try on his machine, same problem. I think I'll have to build the command text in a variable. How do I wrap dates with a single quote in the expression builder? I tried "'" and ''. But it throws an error. 

To work against SQL Server 2012 the data provider needs to be upgraded, I guess thus the error.

E.g. Native Client 11 http://microsoft-sql-server-native-client.updatestar.com/

I've done that. Same error. 

Start removing bits of the packages until you do not get the error, this way will pinpoint where the issue occurs

I'll give that a shot. I was tinkering with that earlier but I was have trouble converting a Date variable to a string and then wrapping quotes around it. I was getting errorcannot convert string to date or something and assumed that was the problem.

Do you have an example of an SQL command from a variable expression that uses a Date variable? 

Thanks,

Phil

The code I had proposed, do not have any date parameter passed. If its the pseudo code, thats fine.

Lets say, you pass date as input paratmer, then the exprression changes as

"
EXEC EXTRACT.GETSomething '"+@(DT_STR,12,1252)[User::vId]+"'
"

Please test this out !!

and, you are definitely mapping the  parameters as follows. aren't you?

Sorry for double checking, but you have not included this in your screenshot.

You must map the parameters by name.

If you have done all of this and are using the right data provider - see response from Arthur -  you have indeed hit the jackpot. The only thing to do is start stripping the package to its bare bones until you find out what causes the error.

I've tried that expression many different way. I am trying to build it in the expression builder for a variable strSQL_Query and have set EvaluateAsExpression = true.

I am going to just have to run the work around so I can get this project done since I have extracts piling up on me.

All the examples above came from brand new projects. About stripped back as far as I can go. But I found out as long as I don't hit preview it is ok. I just hit columns and I can move on to creating the next step.

We tried creating a package on a 2005 server to a 2005 database also. Same problem. I could swear something has been pushed to our servers.

You have not answered the question below. I hope that you are not overlooking it.

you are definitely mapping the  parameters as follows. aren't you?

I seem to remember that there was a version of SSIS in which you could reference the parameters of a OLE DB command by ordinal numbers - e.g., 0, 1, 2, 3 - and you did not have to include the parameter names is the SQL Statement - e.g. @MyContainerId. This could have very well been SSIS 2005.

This changed, you can no longer do this. I cannot remember if it changed with SSIS 2008R2 or SSIS 2012.

I could of course be wrong, there are too many version of SSIS out there with a number of significant minor differences.