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]
I have a table named item that looks like this:
PartNumberDescriptionI.DateCreated
1A1XXX ALPHA1 1/1/2014
2A2XXX ALPHA2 1/5/2014
3A3XXX ALPHA3 1/12/2014
1B1XXX BRAVO1 1/22/2014
2B2XXX BRAVO2 1/28/2014
3B3XXX BRAVO3 2/2/2014
And a second table named orders that looks like this:
OrderNoPartNumberQtyO.DateCreated
1CHXX1 1A1XXX 2 1/8/2014
1CHXX2 2A2XXX 3 1/8/2014
1CHXX3 2A2XXX 1 1/20/2014
2CHXX1 1B1XXX 4 2/10/2014
2CHXX2 2B2XXX 2 2/10/2014
2CHXX3 2B2XXX 1 2/22/2014
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated
FROM item I
CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated
FROM Orders O
WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O
PartNumberOrderNoO.DateCreated
1A1XXX 1CHXX1 1/8/2014
2A2XXX 1CHXX3 1/20/2014
3A3XXX NULL NULL
1B1XXX 2CHXX1 2/10/2014
2B2XXX 2CHXX3 2/22/2014
3B3XXX NULL NULL
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 RelatedI 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.
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
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" ?
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?
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?
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.
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 ?
Is it possible to export the environment variables for an SSIS 2012 project? And if so how is it achieved?
View 2 Replies View RelatedI 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.
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.
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(,)
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...
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 RelatedI 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
know a way to find all stored procedures that use declared or temp tables, i.e
Declare @temptable TABLE as....
Create table #temptable
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 RelatedAny 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.
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
I am getting error [[Msg 16924, Level 16, State 1, Line 13
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.
Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)
DECLARE Dropuser_Cursor CURSOR FOR
[Code] ....
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
[Code] ....
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:SQLServerResults
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.
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).
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.
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 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
Hi,
I have just installed MS SQL in my computer and I am trying to run MS
SQL and Visual studio for the first time. However I have run into a
problem that I can't solve. Basically the problem is that the data I
fetch from the database is not being displayed/bound to the ddl I have
on the page.
I have used VS to configure the sqlDataAdapter, DataSet, sqlConnection,
and the sqlCommands. Basically the data is being fetched from the
Northwind sample database. From the preview option on the
sqlDataAdapter I am able to preview the information from the database.
So it seems to work. I have also configured the ddl settings so that
the Categories table is the datasource. I have also set the ddl.DataTextField = "CategoryName"; ddl
.DataValueField = "CategoryID";
When I compiled and run the page, no error messages are generated, but
the ddl is still completely blank and I can't understand why.
Does anybody know how I can solve this?? :s
When using sp_who and sp_who2 on SQL Server 6.5 I seem to get inaccurate results .
Each spid seems to be using the same host name, and the host name used is one that hasn't been connected to SQL server for several months.
Is there a problem with the connection management ? If so how do I find this out ? and how do I fix it ?
We have a SQL Server 2005 database which holds all the companies scanned documents (in image fields). I have developed a report which allows the user to search for a contract and all relevant scanned documents are returned.
When I preview the report with Visual Studio Report Designer...SOME of the images are not displayed, I get the little red cross icon in the top left corner.
When viewed using Report Manager with a browser... The report displays ALL images... However, when I click on the print button and then the print preview button in Report Manager, some of the images do not appear (the same images that don't appear in the Report Designer preview.
Can anyone tell why I can see all the images through Report Manager and my Browser (IE7) but only some of the images when in Report Designer preview or Print Preview?
Any help would be appreciated.
Hi,
I have setup Merge replication with 2 publishers and one subscriber. On the subscription machine I can see publisher 1 listed under local subscriptions but not publisher 2. I have created the second subscription on publisher 2 and also tried to create the subscription again on the subscription machine but i get an error saying the 'Subscription already exists'.
Neither do I see the second subscription nor does it work as if it is subscribed to Publisher 2. What could be going wrong.
I would appreciate any help.
Thanks
tribal