Mapping UDF Parameters To Variables
Feb 3, 2006
As mentioned in a previous posting, I have an in-line table valued UDF with three input parameters. I can set this up as an OLEDB Datasource SQL Command Text with parameter markers (i.e. "?") and test it successfully in the Generic Query Builder. The parameter markers are correctly associated with the input parameters of the UDF and the parameters can be entered at execution time into a parameters table.
So near and yet so far. When I attempt to map the parameter markers with Package Variables there is an error message saying the the parameter details cannot be retrieved from the function. If the function was in a foriegn (e.g. Oracle) database I might accept this as just one of those things but this is a SQL 2005 database and compatability should be complete. Add to this that the Generic Query Builder has no problem with the same UDF and nor does Reporting Services and I have to assume that this is a bug plain and simple.
The only solution that I have seen suggested is to embed the SQL Command text in a Package Variable and change it at execution time but I regard this as a second rate solution.
View 2 Replies
ADVERTISEMENT
May 25, 2005
Is there a problem mapping variables to resultsets with a bigint as datatype?
View 6 Replies
View Related
Oct 3, 2006
I have an Execute SQL Task1 that executes an extraction stored proc (say spe). spe returns a rowset that has 25 columns. For each row in the rowset, a load stored proc (say spl) has to be executed (spl is executed using Execute SQL Task2). spl has 25 input parameters that match the 25 columns returned by spe (the column names returned by spe and input parameter names of spl are exactly same). To achieve this, in Execute SQL Task1, I had to specify a variable in the Result Set (say User::resultset). After declaring 25 variables, in the foreach loop editor, I had to specify the Variable Mappings of these 25 variables to the column indices of the rowset returned by spe. After this, in Execute SQL Task 2 I had to specify in the Parameter Mapping the mapping between the 25 variable names and 25 parameter names of spl. You can understand that it is cumbersome to define all these mappings manually, especially when there are a lot of variables involved.
Is there some way of telling SSIS that it has to automatically map the columns returned by spe and the input parameters of spl (given that the column names and parameter names match exactly)? Or is there a totally different and simple way of achieving the above scenario?
One more problem that I am facing :-
In this package we are having a dataflow control which returns a recordset destination which has a column named ID(in the Input/output tab it is showing its datatype as DT_I8).This recordset is passed to a foreach container control through User::ID variable which is defined as Int64.
While running the package we are getting an error like this:
Error: ForEach Variable Mapping number 4 to variable "User::ID" cannot be applied.
Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Actually both are related to mapping. I tried the second issue with just 1 variable as well to make sure there is no mismatch. Still, i face the same.
Can anyone please provide a solution to this ?
View 6 Replies
View Related
Nov 2, 2006
Learning how to use SSIS...
I have a data flow that uses an OLEDB Source Component to read data from a table. The data access mode is SQL Command. The SQL Command is:
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare)
I wanted to add additional clauses to the where clause.
The problem is that I want to add to this SQL Command the ability to have it use a package variable that at the time of the package execution uses the variable value.
The package variable is called [User::Date_BeginningYesterday]
select lpartid, iCallNum, sql_uid_stamp
from call where sql_uid_stamp not in (select sql_uid_stamp from import_callcompare) and record_modified < [User::Date_BeginningYesterday]
I have looked at various forum message and been through the BOL but seem to missing something to make this work properly.
http://msdn2.microsoft.com/en-us/library/ms139904.aspx
The article, is the closest I have (what I belive) come to finding a solution. I am sure the solution is so easy that it is staring me in the face and I just don't see it. Thank you for your assistance.
...cordell...
View 4 Replies
View Related
May 27, 2014
I have deployed a project with multiple packages to SSIS 2012 db. I am able to configure the project parameters fine. But, I am not able to replace the package variable values with the 'Environment' variables.
View 3 Replies
View Related
Dec 31, 2007
Good day everyone,
I have a question concerning the Execute SQL task.
Problem Statement:
I have a table containing the following sample data.
Catalog_ID, Product_ID, IsBlocked
56789, 1, 0
56789, 3, 1
23567, 1, 0
23567, 2, 1
The data types of the fields are:
Catalog_ID: bigint
Product_ID: bigint
IsBlocked: bit
I have two variables called "old_catalog_id" and "new_catalog_id" with the following values:
old_catalog_id: 56789
new_catalog_id: 11111
Now, I would like to select all the recods, whose Catalog_ID fields equals the value in the variable "old_catalog_id" and insert identical recods with the "new_catalog_id" value.
The result of that operation on my sample records is:
Catalog_ID, Product_ID, IsBlocked
56789, 1, 0
56789, 3, 1
23567, 1, 0
23567, 2, 1
11111, 1, 0
11111, 3, 1
blue: selected fields
green: inserted fields
My Solution:
In order to realize this solution, I have created the following tasks on the Control Flow.
1. Create an Execute SQL Task for the selection. (Name: Selection Task)
2. Create a For-Each-Loop for iterating on the result set. (Name: Loop on results Container)
3. Create an Execute SQL Task inside the For-Each-Loop container for inserting the new records. (Name: Insertion Task)
Configurations and Problems:
1. Selection Task:
General Tab:
Result Set: Full Result Set
SQL Statement:
select Product_ID, IsBlocked
from MyTable
where Catalog_ID = ?
Parameter Mapping Tab:
Variable Name -- Direction -- Data Type -- Parameter Name
User:: old_catalog_version -- Input -- Large_Integer -- 0
Result Set Tab:
Result Name -- Varibale Name
0 -- User::FullResultSet (which has the Data Type: Object)
When I execute this task, I get no records.
Thus, I have hard-coded the value of Catalog_ID in the Sql Statement parameter. Now, I get the correct 2 records in the result set.
Question 1: What am I doing wrong in the Parameter Mapping?
2. Loop on results Container:
Collection Tab:
Enumerator: Foreach ADO Enumerator
ADO object source variable: User::FullResultSet
Enumeration mode: Rows in the first table
Variable Mappings Tab:
Variable -- Index
User:: old_prod_id -- 0 (Data Type of "old_prod_id" is Int64)
User:: old_isBlocked -- 1 (Data Type of "old_isBlocked" is Boolean)
When I execute the package, it fails with the following error message:
Error: 0xC001F009 at SQL Tasks: The type of the value being assigned to variable "User:: prod_id" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Thus, I have changed the data type of the variable "old_prod_id" to Object. Now, the package runs successfully.
Question 2: Why isn't the package accepting "Int64" as the data type of my variable, although the corresponding DB field has the type "bigint"?
3. Insertion Task:
General Tab:
Result Set: None
SQL Statement:
insert into MyTable
(Catalog_ID, Product_ID, IsBlocked)
values (?, ?, ?)
Parameter Mapping Tab:
Variable Name -- Direction -- Data Type -- Parameter Name
User::new_catalog_version -- Input -- Large_Integer -- 0
User:: old_prod_id -- Input -- Large_Integer -- 1
User:: old_isBlocked -- Input -- Variant_Bool -- 2
When I execute this task, it fails with the following error message:
Error: 0x0 at Insertion Task: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Catalog2Context_CATALOGS". The conflict occurred in database "XS_EC_CCH_PEGXSAP2", table "eSearch4.CATALOGS", column 'ID'.
Well, the message implicitly states that the value of the variable "new_catalog_version" has violated the mentioned FOREIGN KEY constraint. But I have set a breakpoint and I saw that the value was set correctly. So, it seems that the Execute SQL Task is not able to read the value of the variable correctly.
Question 3: What am I doing wrong in the Parameter Mapping, which causes the task not being able to read the value of the variables correctly?
Thanks in advance for your help.
Regards,
Samar
View 3 Replies
View Related
Feb 27, 2008
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]
/*
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
Example:
exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0
*/
@ProcessName varchar(50)
, @FileCreateDate datetime
, @IsNewFile bit OUTPUT
AS
SET NOCOUNT ON
--DECLARE @IsNewFile bit
DECLARE @CreateDateInTable datetime
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty.
Expressions is empty.
When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
Thanks for your help.
View 5 Replies
View Related
Jan 8, 2008
Hi all,
I'm trying to make a stored procedure which runs a different stored procedure.
exec sp_addsubscription @publication = N'update setup tables', @subscriber = 'SERVER', @destination_db = N'DB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
this is the stored procedure I want to run, but I wanted to use a parameter for 'SERVER'.
if my parameter is called @serverName, how would you use it for subscriber?
exec sp_addsubscription @publication = N'update setup tables', @subscriber = @serverName, @destination_db = N'DB', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
kind of like that??
thank you
View 1 Replies
View Related
Aug 26, 2013
I don't know the difference between parameters and variables in a stored procedure. How can I tell?
ALTER PROCEDURE [dbo].[spEdgeSalesDashBoad]
@x_strSessionGUIDVARCHAR(50),
@x_strStartofWeekVARCHAR(10),
@x_StartofMonthVARCHAR(10),
@x_strEndDateVARCHAR(10)
[Code] ......
View 2 Replies
View Related
Mar 19, 2008
Hi Friends,
I have a small problem in parameter mapping for Execute SQL Task.
I am using a delete statement with 2 conditions.
Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider.
I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for
c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR.
i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype.
This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also,
I set the property RetainSameConnection = TRUE for oracle connection manager.
I am not able to trace this logical error.
The same is working fine in my local machine.
But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping?
What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and
inside the parameter mapping.
Any thoughts!
View 5 Replies
View Related
Feb 29, 2008
I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_diplomaViewQualifications"
UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"
DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"
SelectCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="quantity"
/>
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="createdBy" DefaultValue="TEST"/>
</DeleteParameters>
<SelectParameters>
<asp:Parameter Name="masterKey"
/>
</SelectParameters>
</asp:SqlDataSource>
My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.
This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)
How do I pass this into the update and/or delete part
of the command field on the ASP page?
View 3 Replies
View Related
Jun 3, 2006
When creating a Reporting Services report and declaring local variables as part of your query in a dataset there is sometimes a problem. When you hit run in the Data section and the €œDefine Query Parameters€? box pops up, all the variables are not there. Sometimes when you go to properties (€¦) of that dataset the parameters are gone. Is this a bug? This is happening both in RS2000 and 2005.
Thanks
View 7 Replies
View Related
Feb 23, 2006
Hi,
I need to run an SQL command, in which one of the fields is based on the current time, represented as a string in a specific format.
I used a script task to create the string from the current time, and store it in a package variable named "newDate". Later, in the execute sql task, I have the following update statement: "update table_1 set field_1 = ?" (OLE DB connection is used)
and in the parameters mapping I set parameter 0 to the string package variable "newDate".
The problem is that on runtime, the sql executes with the default value of the variable - i.e. it doesn't take the new value.
I checked it and placed another script task after the first one, that simply tries to read the variable "newDate" and print it to screen, but even here the default value is used.
how can I use the new value of the variable?
thanks.
View 7 Replies
View Related
Apr 4, 2006
Hi Guys,
(I have searched this forum extensively, but still can't find the solution to this problem)
Here it is:
I have step in my ETL process that gets facts from another database. Here is how I set it up:
1) I have to package variables called User::startDate and User::endDate of data type datetime
2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine)
3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate)
When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping."
It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?"
To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well.
Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message.
This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...
View 23 Replies
View Related
Jun 20, 2007
Hello all,
I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:
C++ Extended Stored Procedure:
(Basically all this code is doing is retrieving the parameters and printing them back out)
srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);
param1 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, param1, &bNull);
param1[uLen] = ' ';
srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, NULL, &bNull);
param2 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 2, &bType, &uMaxLen, &uLen, param2, &bNull);
param2[uLen] = ' ';
srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, NULL, &bNull);
param3 = new BYTE[uLen + 1];
srv_paraminfo(srvproc, 3, &bType, &uMaxLen, &uLen, param3, &bNull);
param3[uLen] = ' ';
sprintf(msgText, "Params received by xp: %s, %s, %s", param1, param2, param3);
srv_sendmsg( srvproc, SRV_MSG_ERROR, 0,(DBTINYINT)0, (DBTINYINT)0,NULL,0,0,msgText,SRV_NULLTERM);
srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);
Calling the XP with literal values:
EXEC xp_mytest 'one','two','three'
Output:
Params received by xp: one, two, three
Calling XP via a stored procedure:
create procedure sp_mytest
(
@myvar1 nvarchar(200),
@myvar2 nvarchar(50),
@myvar3 nvarchar(50)
)
as BEGIN
PRINT @myvar1
PRINT @myvar2
PRINT @myvar3
EXEC xp_mytest @myvar1, @myvar2, @myvar3
END
EXEC sp_mytest 'one','two','three'
Output:
one
two
three
Params received by xp: o,t,t
Any insight or assistance is greatly appreciated!!!
View 1 Replies
View Related
Sep 25, 2014
Any fix for the seemingly random sort order of the variables in the dropdown list when configuring parameters and connection managers in the SSISDB catalog?
I imported all of our connection strings into an environment (about 200 of them). They were inserted in alpha order and the ID values within the internal.environment_variables table shows them in order as well, by ID and by name.
When I run profiler and capture the command that retrieves them and run it in ssms they are in order but in the dropdown they seem random.
There are no values within any of the tables that accounts for the order they are in.
If a package has 5 connections you need to go through the unsorted list 5 times to find them.
Sometimes you get lucky and they are in the first 20 or so.
I know I can write a script, just wondering if there is a fix for the sorting.
View 2 Replies
View Related
Sep 4, 2006
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
View 8 Replies
View Related
Jan 24, 2006
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
View 2 Replies
View Related
Feb 18, 2007
Hi;
Can anyone please tell me how to do re-mapping? or give some resources address that I can learn easily?
Thanks.
View 5 Replies
View Related
Sep 5, 2006
hi! i have two different databases (SQL 2005 and Oracle) and i need to map their tables with one another. how will i do this?
thanks
View 6 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Jul 20, 2007
I'm using NHibernate with SQL Server Express Edition and I've been problems with the data type to be defined in the "type" attribute on the "property" tag in the XML file mapping.Has someone worked with that?I need to match the appropriate NHibernate type to the following SQL Server Types: uniqueidentifier;bit;ntext;I have been getting "type mismatch" error when the code is trying access the data. Thanks a lot!
View 2 Replies
View Related
Sep 24, 2007
Hi all, I have to provide user mapping between two databases on the same server so that the tables and content of both the databases can be accessed by each other. I know how to do it using Enterprise Manager, but I have to do it via script as in my asp.net application the databases are created dynamically. If I am not wrong, then "sa" login will be required to execute this script, but no problems as we will be having the "sa" password also. The server is sql server 2005.
Can anybody please let me know how to do it exactly. Its very urgent. Thanx a lot in advance.
View 2 Replies
View Related
Dec 26, 2007
hi friends..
i want to insert data in sql server2005 table through asp.net
column names of this table comes from diffrent tables.....
like Rollno--- in my new table....but in old table1 this is like RNO & from old table2 this is R_no
so i want insert this two old diffrent table data into my new table...but i have problem coz of diffrent column names of old table...
can you help me 4 that...
View 3 Replies
View Related
Aug 25, 2006
Hello,
I have a requirement to link two databases on the same server. Besides using the microsoft link option is there any other method by which we can link these two databases. Responses will be highly appreciated.
Thanks
View 3 Replies
View Related
Aug 6, 2007
I'm trying to find out the best O/R mapping tool for .NET. A perfect tool would begin with the database model and would be capable of generating 2 layers: the Domain layer (normal classes) and the Persistence layer (methods for storing and retrieving the domain layer objects from the database.
This tool would also be capable of generating the corresponding Visual Studio solution and projects.
I've already tried NetTiers, but it generates so many layers that the code is very dificult to manage and understand.
So any of you know a tool similar to the described above?
View 1 Replies
View Related
Aug 23, 2014
We have a Item table and a Price table. Structure is mentioned below. An Item need to be matched with the Price table (or vice versa is also fine) and get the Cost from the Price table. The challenge (which I feel :) ) is, mapping or Columns to be looked up between these two tables are not fixed.For an Item, Country, City and Number of Days columns can have a value or can be null. For ex, Paper, US, New York <10 days is one combination which should be matched to the Item table. For Paper, UK, NULL, <5 days is another combination which need to be checked. it is kind of dynamic look up of columns between the two tables.
Price Table
Item Country City Number of Days Cost
Paper US New York <10 100
Paper UK <5 150
Paper Chicago >10 200
Pen China <10 250
Item Table
Item Country City Number of Days
Paper US New York 5
Paper UK London 3
Paper US Chicago 15
Pen China Shangai 5
Paper China Beijing 15
Paper US Chicago 5
View 2 Replies
View Related
Apr 29, 2008
I need to get a list of users and databases that a sql login is mapped to. Is there a view or a sp that can get me this information?
View 18 Replies
View Related
May 5, 2008
In an event of a system restore from development to production, what would cause the mappings for a database user to be whiped out?
Thank you in advanced for the clarifications.
Al
View 9 Replies
View Related
Apr 1, 2008
Hi Guys
I have a table with a column and value like below.
A1JAN
A1FEB
A1MAR
A1APR
A1MAY
A1JUN
A1JLY
A1AUG
A1SEP
A1OCT
A1NOV
A1DEC
A2JAN
A2FEB
A2MAR
A2APR
A2MAY
A2JUN
A2JLY
I want to map all these values to one value like AL AVG
How do i implement the solution so that all those different values map directly to one value.
Thanks.
View 5 Replies
View Related
Mar 21, 2007
I need to generate a document with the fields mapping from a DataFlow Task. Anybody knows how to do this?
Know I'm using screenshots from the destination object mapping.
Thanks
View 5 Replies
View Related
Jun 20, 2007
I read in my record. As we proceed down the pipeline, I want to put kind of record (with length 742) into one path, and a different kind of record (with length 242) into a different path. My problem is that I have (obviously) different fixed width column mappings for the two kinds of records. How can I change the column mapping mid-stream? Can I? Do I need to just write out to flat files and go from there?
Thanks!
Jim Work
View 3 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
