We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'
/SET Package.Variables[MyDate].Value;"NULL"
What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...
I'm developing an SSIS package that will theoretically be run in two different ways. One is as a nightly job, called by a SQL job. The second is from a web application. When run as a job, it will be looking for a flat file in a known location. Run from the web, the file name and location are determined at runtime. I've tried to handle this by using a variable to hold the flatfile connection string, and setting the variable value to the known location. Then, when called to the web, I determine the file location/name at runtime, and pass it to the package. I'd assumed that if I pass the value into the package, it would override the variable's value that I'd set, but this doesn't seem to be happening. I realize this could very well be due to a coding error, but I thought I'd check to see if this is even a viable approach to be taking. If a variable's value is set in the package itself, will it be overwritten by a value passed to the package (assuming no stupid mistakes)? Or is this entirely the wrong approach to be taking for what I'm trying to do?
View 6 Replies View RelatedI would like to pass variables to the package before it is executed (e.g. I am calling the bcp utility and I need to pass a password to the command line), so that at runtime a variable is set and then used.
Does anyone has some hints for good approaches? Every idea is welcome.
FYI: I do not use the bulk insert task as I need an errorlog file and the command is buggy with the errorfile option, therefore I chose the bcp approach. My connections are dynamic as far as servername and userid is concerned, but I did not find a solution for the password issue.
I am having a problem with passing variables into my SSIS package from C#. The variable names match ("Variable1, ...), however they do not seem to be assigned the proper values once the package is executed. The package does run and returns a FALURE notice saying there is a problem with my expressions.
When I added a new data flow, derived all the variables into columns and wrote their values to a flat file I noticed that the values still contain my default values from the SSIS package itself as though nothing was passed in from C#. I am hoping that it is a simple configuration/user error.
Any ideas?
---- C# ----
Reference to Microsoft.SQLServer.ManagedDTS
using Microsoft.SqlServer.Dts.Runtime;
Application DTSApp = new Application();
Package DTSPack = DTSApp.LoadPackage("d:\SSISPackages\Package.dtsx", null);
DTSPack.Variables.Add("Variable1", true, "", var1.ToString());
DTSPack.Variables.Add("Variable2", true, "", var2);
DTSPack.Variables.Add("Variable3", true, "", 100);
DTSPack.Variables.Add("Variable4", true, "", var4.ToString());
DTSExecResult pkgResult = DTSPack.Execute(null, DTSPack.Variables, null, null, null);
I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?
View 6 Replies View RelatedI've got an SSIS package that works fine. It does extracts from a foreign ODBC source and moves it to SQL Server. It has 3 variables, a customer ID, a fromDate and a toDate. Those variables are used to complete a SQL statement expression. So far so good.
I now want to provide a web interface in a web page that will enable the user to provide values for those variables. How can I execute my package passing the user's input?
Thanks for any advice.
Lou
I want to loop through a recordset of email addresses, and for each value in the recordset, I want to perform a set of task which includes creating an Excel spreadsheet and emailing it through to those email addresses.
I have 2 DTS package.
Package 1 - This contains an ActiveXScript task. The purpose of this package is to do the looping through of the recordset, and passing the email address variable to package 2.
Package 2 - Receives the email address from package 1. Package 2 contains the creating Excel spreadsheet and emailing it through to the recipients function.
I am having difficulty passing the variable from package 1 to package 2.
The code I have to date for the ActiveXScript task in package 1 is:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim oPackage
Dim oStep
Dim oTask
Dim oCustTask
Dim oGlobal
'Initialise package
Set oPackage = CreateObject("DTS.Package2")
Set oStep = oPackage.Steps.New
oStep.Name = "TestStep"
Set oTask = oPackage.Tasks.New ("DTSExecutePackageTask")
Set oCustTask = oTask.CustomTask
oCustTask.Name = "TestCustTask"
oStep.TaskName = oCustTask.Name
oPackage.Steps.Add oStep
oCustTask.FileName = "C:TempVeraTest1.dts"
oPackage.Tasks.Add oTask
set oGlobal = oPackage.GlobalVariables.New("CurrentAirlineCode")
oGlobal.value = "SIA"
oPackage.GlobalVariables.Add oGlobal
'Execute package
oPackage.Execute
Main = DTSTaskExecResult_Success
End Function
This does not work. Can anyone please help?
Thanks very much in advance.
I've got a package in SSIS 2012 that has an Execute SQL task in the control flow level.
The SQL in question does an Upsert via the SQL merge statement. What I want to do, is return the count of records inserted and records updated (No deletes going on here to worry about). I'm using the output option to output the changed recs to a table variable.
I've tried returning the values as:
Select Count(*) as UpdateCount from @mergeOutput where Action = 'Update'
and
Select Count(*) as InsertCount from @mergeOutput where Action = 'Insert'
I've tried setting the resultset to both Single rowset and Full rowset, but i'm not seeing anything returned to the package variables I've set for them (intInsertcount and intUpdatecount).
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
I am pretty new to this and hope someone can help and I hope I can explain my problem.
I have a php form
<form action="cpesearch.php">
State: <select name="stname">
<option value ="ALL">all states</option>
<option value ="vic">VIC</option>
<option value ="sa">SA</option>etc etc etc
I am then connecting to my datatbase, assign the variables and doing the SQL query.
if ($dbc = mysql_connect ('localhost','root',''))
{
if (@mysql_select_db('database'))
{
$sname = $_GET['stname'];
$pname = $_GET['prname'];
$sql = 'SELECT * FROM `cpe` WHERE eventdate >= CURDATE() AND state = ''.$sname.'' AND presenter = ''.$pname.'' ORDER BY eventdate ASC';
$r = mysql_query($sql) or die("Query failed".mysql_error($dbc));
while ($row = mysql_fetch_array($r))
{
print "<div id="mainContent"> <p>".$row['state']."</p>".
"<p>".$row['eventdate']."</p>".
"<p>".$row['eventtitle']."</p>".
etc etc etc
So my problem is... I have an events table with presenters and state (and other info). I want to be able to select each presenter and state or all presenters and/or states. Not sure then how to pass the variable into my sql statement if they want to select ALL.
Hi,
I have a DTS (DTS1) that call another DTS(DTS2). DTS2 has a global variable defined, I need to pass a value to that variable from DTS1.
Thanks,
Arturo
Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VB I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.
I am executing:
--------------------------------
DECLARE @x AS int
SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
PRINT @x
--------------------------------
@x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:
SET @lcQuery = 'DECLARE @x AS int ' +
'UPDATE ... WHERE .... ' +
'SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
How can I pass the variable @x to the progam from within EXEC (@lcQuery)?
How does EXEC (@lcQuery) execute? In a different space?
Thank you.
Does any one know how to pass variables to a stroed procedure within a trigger and does anyone know how to capture a value
from a stored procedure and store that value in a variable all within a trigger.
If anybody could help me with this i would love to hear from you, at the moment this is driving me crazy?
now i have a stored procedure in SQL server 2000, which i need to pass some variables into (pretty straight forward so far!!) I am copying tables from one database to another and i what to be able to choose the spefic database i'm copying to, this is the basic's of my query!
CREATE PROC sp_Copy_Facility
@FacilityID int,
/* Select and append Data tables */
-SELECT testDB.dbo.dt_test.* INTO
-******.dbo.dt_sample
-FROM testDB.dbo.dt_test
-WHERE testDB.dbo.dt_test.Facility_ID = @FacilityID
-option (keep plan)
i have also tried this:
CREATE PROC sp_Copy_Facility
@FacilityID int,
@DBIN nvarchar(40),
@DBOUT nvarchar(40)
AS
DECLARE @SQLString varchar(1000)
DECLARE @S2 nvarchar(1000)
/* Select and append Data tables */
SET @SQLString = 'SELECT ' + @DBIN +'.dbo.dt_test.* INTO '
SET @SQLString = @SQLString + @DBOUT + '.dbo.dt_test
SET @SQLString = @SQLString + 'FROM ' + @DBIN + ' .dbo.dt_test WHERE ' + @DBIN + '.dbo.dt_test.Facility_ID = @FacilityID option (keep plan)'
please help i need to get this done and i posted this on a number of different web sites with as yet no joy!!
Anyone,Is this possible?I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuerystatement. I need to pass a list of ever-changing deal numbers Mylist of numbers are stored as a table on MS SQL.So what I want is thisSelect * from OpenQuery(TeraSrvr, "Select Col1, Col2, Col3[color=blue]>From Teradata_Table_1[/color]Where Deal_no in (Select Deal_no from SQLTable)")Now I know that wont work, but How can I pass 184 Deal Numbers from mySQL server to this query before it is sent to the Teradata server to bedone? Do I have to keep re-doing an in statement each month?Anyone can help?Doug
View 3 Replies View RelatedI have an SSIS package. In my control flow I have an Execute SQL Task and a data flow task. In my Execute SQL Task in the SQL Statement I have (select dbo.to_date(getdate()) as process_date) its a direct input with a result set. It gets Getdate as (processed_date) is declared. I have set no parameters, but I have set a Result Set. Also, I have set a global variable in the scope where I passes the date (processed_date)
In my Data Flow, I have an OLE DB data source with the following SQL statement in my SQL Command.
I am trying to pass down the variable as the ? .
It works when I pass it only to: lr.processed_date = ?
But I get an error when I pass it down to the : and ? between cav.begin_date and cav.end_date
SELECT distinct
acc.account_num,
FROM
cust_version_slow cvs
inner join cust_acco_version cav
on cus.cust_id = cav.cust_id
and ? between cav.begin_date and cav.end_date
inner join bia.dbo.acct acc
on cav.acco_id = acc.acco_id
inner join bia.dbo.account_version_slow avs
on acc.account_id = avs.account_id
and ? between avs.begin_date and avs.end_date
inner join bia_org ('02','2,3,4,5,6,7,9,10') boh
on cvs.branch_code = boh.branch_code
inner join accou_version_profit accM4
on acc.acco_id = accM4.acco_id
and ? between accM4.begin_date and accM4.end_date
where
lr.processed_date = ?
I need to pass down the same variable (processed_date) which is ? in all the ? in red
Thanks,
Delovan
In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc.
We have a report in which there are 2 parameters (Drop Downs).
Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine.
Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.)
Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?
Hello.
I need to store 2 values that i retrive from a databse into variables so i can check them towards what is typed into the textboxes for my login script iv built. How can i do this?1 string myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
2
3 SqlConnection myConnection = new SqlConnection(myConnectionString);
4 SqlCommand cmd = new SqlCommand("SELECT [username], [password] FROM [company] WHERE (([username] = @username) AND ([password] = @password))");
5 cmd.Parameters.AddWithValue("username", TextBoxUsername.Text);
6 cmd.Parameters.AddWithValue("password", TextBoxPassword.Text);
7
8 myConnection.Open();
9
10 //how do i pass the values retrived into variables, like string usrname and string password?
11
12 myConnection.Close();
Similar to a previous thread, but not exactly. I have a batch file that takes
two date parameters. I have a SQL script that gets the values I want and assigns
them to variables, but I don`t know how to pass these to the batch.
I`m thinking of something like:
declare @myvar1 datetime, @myvar2 datetime
select @myvar1 = <some date value>
select @myvar2 = <some date value>
exec xp_cmdshell "c:mssqlscriptsMYBATCH.BAT" @myvar1 @myvar2
This just errors out when it reaches the variables in the last line. If I move
the variables inside the quotes, it passes them as literal text strings, not as
the assigned datetime values.
Any ideas? Is this possible?
Thanks,
Robert
Hi!!
If I have declared a variable, such as char variable_name[x], and have assigned it a value, how do I go about passing this value to an SQL query such as:
SELECT * FROM variable_name
where my value replaces variable_name.
Cheers, Marc
Any one have any ideas or links to point me to ???
View 2 Replies View RelatedHi everyone,
passing variables (values) from a form (MS Access Project) to a stored procedures in order to select records shouldn't be all that difficult. However after searching for hours and hours in various forums and discussions i'm still nowhere. I really hope someone can give me the missing hint.
OK All I whant to do is select data from the sql server via an access interface. the value entered into the access form by the user (for example the client name) should then be transferred to the stored procedure which will then return all the records of the chosen client.
The simple task of transferring this form value to the stored procedure is driving me crazy. Does anyone have an idea.
Thanx in advance
Guido
how can you pass variables from one 'Execute SQL Task' to another?
I am familure with having a report run based on users inputting variables via DDL or multiselect using SRS. But now want to access my reports a little differently. I have an ASPX page and I want a user to click a button and to have the report use the Session Variable that is stored in ASP. As a result the report will be prefiltered on data the specific user can see.
Any ideas on ow to do this?
When executing a package from Business Intelligence Studio variables are well passed between packages but when same is done using dtexec utility variables don't get passed between packages. Does anyone have any idea why this could be happening and what would be possible solutions? Thanks in advance!
View 3 Replies View Related
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?
Hi.
I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?
Any ideas would be appreciated. Thanks!
Hi,
I have created lastUpdatedDate variable on package level. I have run a sql task and store a date in that variable.
now i am trying to pass that variable as parameter to oledb source connection (using command). it seems that we cant pass parameter in any sub query or derived table in query. its only working in outer query as soon as we place ? in WHERE clause of inner query it start throwing an 'Syntax Error' error saying that connection provider might not support that.
any idea ?????
I dont want to use command variables as my query is going to be quite big.
Note : I have tried Sql Server Native and OLEDB provider for sql server and this behaviour is seems to be constant in both.
Thanks,
Furrukh baig
Hi friends,
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
Thanks
Subhash Subramanyam
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
Hi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil