SQL 2012 :: SSIS And Variables With Where Clause
Jan 15, 2015
I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.
I used a select from a central table to get the connection strings, which is put into [User::Server2]
1) execute SQL task to collect the port
(DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
)
Which is then put into a variable @Portnumber
2) I then need to insert this into a certain server with a where clause including another variable
so something like
"INSERT INTO DBO.InstanceConfig VALUES ('"+@[User::Portnumber]+"') where Serverinstance = ('"+@[User::Server2]+"')
but it doesnt work like that.. is there an easier way
View 1 Replies
ADVERTISEMENT
Oct 11, 2014
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar
View 4 Replies
View Related
Sep 17, 2015
I have a process which connects to a SQL server remotely, runs some code, creates a temporary table with an output. I want to be able to select the results of this table into XML (not a problem), put the results into a variable in SSIS, and put the variable results into a SQL table a different SQL Instance. Doing it this way removes the footprint of needing a normal table on the source SQL Server.
View 2 Replies
View Related
Dec 2, 2014
I have this procedure to remove certain characters from file names.
The SQL Task has this: exec dbo.spCleanseFileName @strFileName = ?, @strFileNameCleansed = ?
The stored procedure:
CREATE PROCEDURE [dbo].[spCleanseFileName](@strFileName varchar(40),@strFileNameCleansed varchar(40) output)
I have it in an SSIS package and my problem is that, after that SQL Task completes, the value for the
),@strFileNameCleansed variable is blank. I HAVE confirmed that the procedure DOES set the correct value inside the SP.
View 2 Replies
View Related
Jul 3, 2014
I have finally been able to get access to SQL Server 2012, so I am just now getting to play with some of the features. My new company has multiple ways of calling an SSIS package, and some (if not all) use Package Parameters. I just did a little research on these, and I in general get how they work. Here is why I ask...
I liked how my last company had dynamic variables setup. They had all of the required variables stored on a single database table on a single server. This kept all of the connections strings, file paths, file names, etc. that a package would use. My computer, the UAT server, and the Prod server all had the same file in the same location with 1 character difference to note which environment to run in. This way I knew I would always use only the Dev server when I ran a package during development. I would also only touch the test folders with my test data. Once we moved it to UAT or Prod, that single character difference would point to a different set of variables for the same PackageID, and we didn't have to worry about forgetting to change hard coded variable values. The only 2 hard coded variable values were the PackageID and the variable holder database. I have heard of similar ways of doing this via an XML file, but I have not had the (ahem) pleasure of working with that yet.
Jump to my current job and why so far I have not been impressed with the Package Parameters. We have 3 different jobs that all call the same SSIS package. We feed in a different FacilityID, and using values stored in tables we know to look for different file names, etc. To me this seems to be a similar set up to how my last company had set the system. The big difference is my old company used a Script Task to verify which environment we were running in (I can see setting this for different FacilityID's, but that would be cumbersome), and my new company uses multiple tables across multiple databases (I know, this is not the best set up in the world, and we are fixing it soon) to decide what we will use for some of the variable values.
What are your thoughts on storing variables outside a package? Do you have any blog posts or articles you can link to show current best practices?do the Package Parameters only work via the Integrated Services Catalog, or can they be accessed for the File System packages too?
View 2 Replies
View Related
Nov 18, 2014
I am using Variables to configure a lot in an SSIS Packages.
Over the years you add new variables that are usefull to your default-package.
However to use those in "older" packages you have to open add them manually.
Any way (e.g. skript) to add a set of variables to every SSIS-package in a folder ?
View 3 Replies
View Related
Sep 9, 2015
Is it possible to export the environment variables for an SSIS 2012 project? And if so how is it achieved?
View 2 Replies
View Related
May 21, 2015
in SSIS
( Left(@[User::YourFileName],3) == "AP_" ? 101 : ( Left(@[User::YourFileName],3) == "VD_" ? 102 : ( Left(@[User::YourFileName],3) == "BK_ " ? 103 : 000)))
In the above Variable i want to change the value in Hundreds place i.e from 101,102,103 to 201 ,202, 203 depending upon some Project param
lets say if project param is 1, the values assigned should be as 101,102,103
if project param is 2, the values assigned should be as 201,202,203
if project param is 3, the values assigned should be as 301,302,303 etc...
View 0 Replies
View Related
Feb 10, 2014
I have SSIS 2012 Enterprise, using catalog deployment and have more that 50 environment variables for connection to databases across my enterprise.
The problem when i go to configure the packages after deployment and pick the proper env variables, that are not sorted, so i have to browse all entries in order to find the proper entry in environment variables.
View 1 Replies
View Related
Mar 4, 2014
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).
View 2 Replies
View Related
Jul 16, 2015
Can I assign values to variables in 2012 using below command? I have used the same command in 2008 and it works fine.
DTEXEC
/SERVER"XXXXXXXXSQLSERVER2012"/SQL"Mypackage.dtsx"/SETPackage.Variables[FilePath].Value;"C:Test estvariable.csv"
Wondering is there a different way in 2012 to pass values to variables dynamically.
View 2 Replies
View Related
Aug 8, 2013
How to pass variable from Parent to child and child to Parent Packages  is this possible in SQL SSIS 2012. I need this only in SSIS 2012 ...
View 6 Replies
View Related
Aug 20, 2013
SQL logic, and I've been working (and researching) this all day with zero success..My goal is to try an pass a variable from an ASP page to a stored procedure, which is utilizing the variable as criteria for a column_name in the where clause.So for example (a simplified version of my query):
@strDept nvarchar(10), @strUser nvarchar(30)
-- the asp page will pass f19 to @strDept
-- the asp page will pass the logged in username to @strUser
select x, y, z from table1 where @strDept in (@strUser)
The stored procedure does execute, but it returns no values. Is that because the where variable has no data at compile time? If not, I can't think of any reason why this is not working. (I have tried a case statement, before the select to set the variable too and that returned the same result.)
View 19 Replies
View Related
Jul 20, 2005
I have a problem with executing following T-SQL select query.My select statement looks like thisSELECT * FROM (@TableName_FirstPart + @TableName_secondPart)**@TableName_FirstPart & @TableName_SecondPart are Local variables**Im getting Incorrect syntax error for this statement.Following is the error message:"Server: Msg 170, Level 15, State 1, Line 55Line 55: Incorrect syntax near '+'."Is it possible to construct table names in the FROM clause dynamically ?Thanks in advance
View 2 Replies
View Related
Mar 24, 2004
Hi,
I need to include two input variables
in my Order By Clause in a stored procedure like ORDER BY @column @Dirction. But MS SQL does not allow me
to do so and gives an Error 1008.
How can i solve this problem?
Thanks for your help!!
View 6 Replies
View Related
Jul 23, 2015
All of a sudden my user variables are not displayed.
I know that they exist because the package run is successful.
For better, quicker answers on T-SQL questions, click on the following...
[URL]
For better answers on performance questions, click on the following...
[URL]
View 4 Replies
View Related
Mar 19, 2015
I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
compare xmls by their "semantic value" ?
View 8 Replies
View Related
Sep 17, 2015
I have three variables
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
They are sent to the sproc as null. Since they could be null I need to exclude them from posting to a temp table
Example
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
SET @CostID = NEWID()
SELECT @QuantityID as ID UNION ALL
SELECT @LengthID UNION ALL
SELECT @CostID
Two values are null. I want those excluded from this table
Here is the example of what I am trying to do:
DECLARE @QuantityID uniqueidentifier,
@LengthID uniqueidentifier,
@CostID uniqueidentifier
DECLARE @Temp as Table (id uniqueidentifier NOT NULL Primary key)
SET @CostID = NEWID()
INSERT INTO @Temp
SELECT @QuantityID as ID UNION ALL
SELECT @LengthID UNION ALL
SELECT @CostID
How do I insert into @Temp only non null values?
View 5 Replies
View Related
Oct 20, 2014
I've got a number of stored procedures that I have for reporting
All are of a similar starting format
For easier maintenance and to take away the need to change all of them if the methodology changes I want to split out shared code.
What I want to do is to take out the part that populates the @ID1 table into a separate stored proc which will be called from the report procs. The values from the shared proc will then be parsed back to the reporting proc.
I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.
I also don't want to make the code too complex so that it is relatively easy to read
CREATE PROC dbo.ReportM1 @ID INT AS
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
IF @ID = 0
INSERT INTO @ID1
[Code] ....
The first question I have is: can i do it with a table variable when going between procs or do i need to build a real table if i want it to maintain the logic in 1 place.
May be worth bearing in mind that the end user who will be executing the proc will only have read + execute stored proc access permissions so dropping, updating or creating real tables is not an option. #Temp tables are possible but since am using table variables throughout would prefer to stick with them.
View 2 Replies
View Related
Sep 17, 2015
I am trying to use variables to generate the command:
USE DATABASE
GO
Code below:
DECLARE @DBName_Schema varchar(500)
SET @DBName = 'Test'
EXEC ('USE ' + @DBName )
GO
It does not seem to be working.
View 5 Replies
View Related
May 16, 2006
Hi there,
This is what I am trying to do :
1) Create a global variable.
2) assign a value to the variable using an SQL Task. (For this all I want to do is assign a maximum value from a column in my table)
3) use the variable in a Data flow task (using SQL to get my source data)
I know this must be very simple but I am new to SSIS and I am struggling to achieve this task.
Thanks for your help in advance.
View 11 Replies
View Related
Aug 2, 2007
Hi,
I'm trying to use a variable in a OLEDB Command, but i can't do it.
What i want to do is:
Step 1: Count the number of rows of a table (using count rows transofrmation) and save it to a variable 'var1'.
Step2: Insert some rows on this table.
Step 3: Count again the number of rows and save it on another variable 'var2'.
Step 4: Ina OLEDB Command , get the inserted records in step 2 to manipulate them on other tables...
Something like that: select top (@var2 - @var1) * from table order by desc
¿Can i use local or global variables from an OLEDB Command transformation object?
View 3 Replies
View Related
Jul 2, 2014
We're getting
Msg 1013, Level 16, State 1, Line 1
The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries
CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST
[code]....
View 2 Replies
View Related
Sep 26, 2014
I need to update the account_status and show the audit in the ssrs report.
I wrote the following query in the ssrs report but when I click on refresh fields it is throwing error incorrect syntax near output
Declare @username nvarchar(50)
Declare @UserStatus Table
(id bigint,
Username nvarchar(50),
Oldaccount_status varchar(255),
Newaccount_status varchar(255))
[Code] .....
View 2 Replies
View Related
Oct 9, 2014
Looking to coalesce a WHERE clause with XML data. I cannot supply test data.
If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.
set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src
where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')
View 1 Replies
View Related
Oct 29, 2015
I have a temporary table #Temp with, among others, a column CountryCode and a column Lastname.I would like to change the ü that appears in some names to u (u umlaut to a plain u), but only for those that have the nationality 'Ned'
My code so far:
Update #Temp
set LastName = replace(Lastname, 'ü', 'ue') WHERE CountryCode = 'Ned'
This code deletes all entries in the column Lastname
I think it must be easy, but I keep staring at the code.
What to do?
View 5 Replies
View Related
Oct 24, 2014
I have a table in the report. One of the columns is the result of a variable. It is the maximum value of a field that is grouped.I need to chart the maximum values and use a different group for the values.
For example:
In the data set is the following information:
Furnace # Heat # Frame Avg Window Avg
then the table has the following:
Heat # Max Frame Avg Max Window Avg
I need to graph the Max Frame Avg and the Max Window Avg on separate graphs.
View 0 Replies
View Related
Jan 28, 2015
how can i put multiple values in the variables.
for eg:
Declare @w_man as varchar
set @w_man = ('julial','BEVERLEYB', 'Lucy') and few more names.
I am getting syntax error(,)
View 5 Replies
View Related
Aug 10, 2015
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
View 9 Replies
View Related
Oct 22, 2015
I am writing a custom query to determine if a legacy table exists or not. From My CMS Server I already have all the instances I have to query and I store the name of the instance in the @Instance variable. I cannot get those stubborn ticks to work right in my query. Below I am using the IF EXISTS statement to search the metadata for the legacy table.
DECLARE @Found tinyint
DECLARE @Instance varchar(100)
set @Instance = 'The Instance'
IF (EXISTS (SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=' + @Instance + ';UID=DBAReader;PWD=DBAReader;','SELECT * FROM [DBA].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''TheTable''') AS a))
SET @Found = 1
ELSE
SET @Found = 0
PRINT @Found
View 2 Replies
View Related
Jul 10, 2007
Before in DTS, I used to using ActiveX scripts to setup simple Global Variables that would later be used by the package, let's say a give state or a date.
I'm completely lost how to do the same in SSIS, can someone give me a hand?
I'm not sure what component to use and how to read this variable.
View 2 Replies
View Related
Apr 27, 2007
Hello,
I am wondering what is the difference between assigning variables in the following two ways.1.
Dim myVar11 As Variables
Dts.VariableDispenser.LockOneForWrite("LoopCount", myVar11)
myVar11("LoopCount").Value = 32.
Dts.Variables("LoopCount").Value = 3I received this error when processing SSIS Package.
Error: A deadlock was detected while trying to lock variables
"User::LoopCount" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.
Your help is appreciated,
-Lawrence
View 7 Replies
View Related
Aug 15, 2006
I have a SSIS package that was migrated from DTS 2000. It had a variable that I passed to the DTS package when executing. Now when I try executing the SSIS package with the variable, I get the following error: The package path referenced an object that cannot be found: "package.variables[user::SnapShotDate].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
From everything that I can find, I am passing the variable correctly. If I look at the package within BIDS, my variable is listed under the variable window & it is scoped for the package. Any ideas on what I might be missing here? This is my first SSIS package that contains a variable.
Let me know if you need more info.
Thanks!
John
View 3 Replies
View Related