.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS - Passing a date parameter to a SQL Query question again....

Posted By:      Posted Date: October 19, 2010    Points: 0   Category :Sql Server

I need to pass a date string to my sqlcommand inside of my "datareader source".  I read some of the questions about setting expression variables but I can't seem to figure out how to do it.  I pasted the query below.  The table name contains a date in it so I need to pass that date as a string into my sqlcommand at runtime.  Do I create a variable with the entire SQL command in it or can I just create a single variable that holds the date and just reference that in my datasource reader sqlcommand property?  Can someone assist me with the syntax for creating the expression?

     pages_20101006.`date` AS full_date,
     pages_20101006.`cat` AS category_no,

Thanks very much,



View Complete Post

More Related Resource Links

Executing an SSIS package from an stored procedure and passing a parameter to it:

Hello all, I'm trying to execute an SSIS package from an stored procedure, using xp_cmdshell. When I execute the package, hard coding the value on the variable, I get rows inserted to my destination tables, which indicates my package pulled the correct data based on the TransactionID parameter. When I execute the package from the stored procedure, the package executes without issues, but I get no rows inserted into my destination tables, which leads me to think it is a data type issue, between the package and the variables I'm using within the stored procedure. The variable TransactionID on the SSIS package, is a string data type, therefore I'm passing it as a varchar from the stored procedure. Am I doing this correctly? Maybe I'm missing something, can you guys see anything wrong? Also, I tried login into the server and executing these 2 commands on the cmd prompt: One with "" quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheck_RenewalOrder.dtsx" /CONFIGFILE "D:\SSISPackages\MyOrderCheckProcess\MyOrderCheckProcess_Config.dtsConfig" /CHECKPOINTING OFF  /REPORTING EWCDI  /SET \Package.Variables[User::TransactionID].Properties[Value];"470219"   And also one without quotes between the TransactionID value: dtexec /FILE "D:\SSISPackages\MyOrderCheckProcess\MyOrde

syntax problem passing parameter into Indexing Service Query

Hi everyone, I have the following query which works fine: select OriginalFileName from Document_Entries where EntryType like 'File%' and substring(entry,charindex('file_',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS('' "green" '') ') )  It finds all documents in the document system which contain the word "green" using the index catalog.  My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others. I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'".  The @keywords parameter will contain any key words the user enters.   declare @keywords nvarchar(500) set @keywords='green'   Declare @query nvarchar(max) set @query = ' select OriginalFileName from Document_Entries where EntryType like ''File%'' and substring(entry,charindex(''file_'',entry),LEN(entry)) in (  SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE    CONTAINS(''' + @keywords + ''') '')     )  )' exec(@query)   Any ideas? thanks Gus

remote execution of ssis package using wcf doesn't like date parameter


I have a ssis package remotely executed by a C# windows application that runs ok in context of SSIS installed on the desktop; I created a wcf windows service that the C# application executes, creates a copy of the package with the date in the variable, but errors on the first SQL task component that requires the date. The copy executes using the SSIS utility. What is possibly the issue with wcf and the date type in the SSIS package? Here is the error:

OnError,SRVR2BLADEA3,MIC\1347259,Clear CII ImportDate,{AB3CF5BA-C114-40F6-9ECB-4C7620D97829},{8B1C10CB-580F-4BFE-BC7D-0B97C181E307},9/27/2010 3:29:56 PM,9/27/2010 3:29:56 PM,-1073548784,0x,Executing the query "" failed with the following error: "The type initializer for '<Module>' threw an exception.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is the code:

case @"E:\SSIS Packages\CIISCImport.dtsx":

//string dtValue = (prms[iPrms - 1].Split('=').GetValue(1).ToString()) + " 12:00";

vars["User::ImportDate"].Value = DateTim

calling a SSIS package by parameter passing using AcvtiveX script (VB SCRIPT)


please find the following code, if i comment the parameter passing line, the code is executing fine, but at the paramter passing line giving all the trouble...any suggestions....

Dim objDTSPackage
Dim sTableName

Set DTSPk = CreateObject("MSDTS.Application")

Set objDTSPackage = DTSPk.LoadPackage("c:\SSIS\SplittingATabe.dtsx",  true, nothing)

sTableName = ltrim(rtrim(INPUTBO"Please enter the Table Data to split into files.")))
objDTSPackage.Variables["sTableName"].value = sTableName     



Passing parameter when running SSIS



I have  question and not sure ssis can do that

I have one store procedure that has one date as a parameter like 

exec Employeehiredate '10/10/1998'

now i want in "Execute Sql task"  such that

 when i run this package from server  after deployment  i can set date value and it will run package by taking that value.

Please let me know if this is achievable  or not  or how to achieve this.

Passing parameter to Subreport dataset query returns no results



I'm using the subreport as a workaround to not being able to join two disparate datasets. The "tickler" file  of keys comes from an Access DB defined as a dataset in my main report. The subreport has a dataset query using parameters from the main report. 

When subreport returns no results, I want to display an error message in the main report and continue to the next row key processing. How do I accomplish that?


SSRS 2008 - Passing parameter into textbox - can't seem to apply formatting to it (date/currency)


I have a report that is displaying a parameter in a textbox inside the report.

Now I right clicked on the textbox, went to properties, and set number formatting to: Date - 01/31/2000

However, when I render the report it does not display the date in that format. It displays it in the way the sproc returns it which is: 5/13/2011 3:08:30 PM

I tried changing the parameter type to TEXT instead of datetime, made no difference.

I am having the same problem with currency. I will return 12345 and it will just display that way in the textbox instead of $12,345.00.

Any suggestions?

MDX Query parameter from SSRS


I've a MDX Query that has where clause as shown below.
I'm designing report using SSRS 2008. How can i pass date as parameter ? I tried to setup @from and @to as parameter but not working ?
any ideas....

WHERE ( {[Date Central].[Calendar Date].[2010-04-01 00:00:00]:[Date Central].[Calendar Date].[2010-08-30 00:00:00]} )

need it to work as
WHERE ( {[Date Central].[Calendar Date].[@From]:[Date Central].[Calendar Date].[@To]} )



Get ServerReport selected Parameter value to use in vb query


I'm using ReportViewer in Asp.net 2.0 to view a SSRS report. I need the value of the selected parameter to use in a vb query. The parameters are populated on the server and its a single selection. Doing searches I've come across  ReportViewer1.ServerReport.GetParameters() but I can't figure out if I can use this to determine which value the user has selected in the parameter dropdownlist. Any help would be appreciated.

How To Remove a Query Parameter from a URL


for a string such as this:


how would I remove the parameter id 12?


adding parameter to query string


hi all,

how do i add a new parameter to an existing query string?


now i need to add a new parameter say, showsearch.

Passing Id in parameter


Hi, I am using url in my mvc application like /product/edit/5, should change it because users can

change id parameter and access different product as this Id directly refer to Id in product table, I have validation there for user to access product, what you suggest, what best practice can be for it ?

should I continue with it or should I change it as application has many users

Thanks you


output cache to be used only when parameter has changed + old question of user control not answered



i have 2 questions about output cache:

1.is it just me , or does it seems that it is used only after the second time that the user reaches the webpage? if so, why, and how can i tweak it to my needs? my guess is that it wouldn't be logical to cache every time a user reach a webpage, but only when it happens enough times.

2.i know that i can use "varybyparam" for using the output cache when the parameter doesn't change , but it seems that it would use the output cache even if there is no parameter , or if the specified paramter is not there. is there a way to overcome this?

3.an old question that somehow marked as "answered" , yet i didn't solve : suppose i created a user control and i gave it values through the CTOR via the aspx file . is it possible to show the values from within the split/design mode? remember that i've talking about ASP.NET and not simply C# winforms . i write this because this question was on :


and the answer that i got was for C# and winforms . i've tried to do the same for ASP.NET , but i've failed.

can anyone please help me?

ascx control with passing parameter

i want to create a ascx control with passing parameter .i got followong code from stackoverflow<% Html.RenderPartial("~/Views/Shared/MyControl.ascx", {data model object}) %>i am fine above line but how can i design html control for this.sample code example would be great.

SSIS OLEDB DataSource - Parameterized Query

Hi Team,We use SSI 2005 for one of our projects. We need to connect to the Oracle database and extract data. When we extract data from the source, we use the OLEDB Source and pass the SQL statement through an expression. We have a limitation of 4000 character when we use these expressions. To avoid this, we tried using parameterized queries as mentioned below:SELECT col1, col2, col3 ... col n FROM tab1 WHERE col1 > :PARAM1;When we try to assign the parameters, we end up getting an error as below."Parameters cannot be extracted from 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 commands is stored in a variable"However, if use the SQL Server as a data source, I can assign the parameters. Could anybody let me know if this is a limitation from SSIS orif I am doing something wrong.Please suggest.ThanksAtukuri

Performance problem when passing a "nullable parameter"

Say @startDate and @endDate are my datetime parameters. If users does not want to limit the @startDate for example, he/she sends a null value to @startDate. Same goes for @endDate (and any other parameter). What I like doing: Select * From MyTable Where MyTable.Date >= IsNull(@startDate, MyTable.Date) And MyTable.Date <= IsNull(@endDate, MyTable.Date) The above looks nice as far as coding but when I look at the execution plan I'm surprised to see the my clustered index (that has it's first column as "MyTable.Date") is not used. instead I get an index scan. More frustrating is that the following works with an index seek and is much faster: Select * From MyTable Where MyTable.Date >= @startDate And MyTable.Date <= @endDate So what should I do? am I doing something wrong? I don't want to use dynamic sql because it's generally slower and prone to sql injection (is dynamic sql my only choise)? I don't want to use "if statements" because then i'd have to rewrite my code several times (in this case three combinations but some of my procedures have up to 8 "nullable" parameters). Or maybe there is another way of implementing what I call here "nullable parameters". obviously I preformance is crucial. thanks, Dror

Need help in Date Query

HI Guys I have date like 2008-10-31 16:36:567 I want to convert like 20081031 00:00:000 for this I'm using convert(datetime,(convert(varchar(8),replace('2008-10-31 16:36:567','-','')))) is there any option to do this?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend