SQL 2012 :: SSIS Variables - Change Value In Hundreds Place
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
ADVERTISEMENT
Jul 20, 2005
How can i delete all user stored procedures and all table triggers very fastina single database?Thank you
View 17 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
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
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
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
Mar 24, 2014
why when I make a change to a foreach loop container in a SSIS package it does not save?
I am trying to change the base file name and if I change it through either the edit or properties windows, the changes does not save. Fore example i change the Files: or File Spec: from oldfile*.txt to newfilename*.txt and when I save and og bak into the edit area to confirm the change, the Files: section shows as "oldfile*.txt.
View 4 Replies
View Related
Feb 16, 2014
I have a table called Table1 where I have five fields like Tableid, Processigndate, Amount, remainingCollectonCount and Frequency. All total I have more than 5Lacs records.
Now I need to fill up another table Called FutuecashFlow taking the records from Table1. There will be also five Columns like FutureCashflowid, Table1id, Processigndate, Amount.
Now the condition is that if the remainingCollectonCount =6 and the frequency is 12 then there will be the 6 entries in the futurecasflow table where the prcessign datae wille be addeed by 1 month.
For example Table1
Tableid, Processigndate Amount remainingCollectonCount Frequency
1 2014-02-15 48 8 12
the future cash flow table the prcessing date column will be shown in the following way
Processigndate
2014-03-15
2014-04-15
2014-05-15
2014-06-15
2014-07-15
2014-09-15
2014-10-15
I do not to want to use cursor....
View 4 Replies
View Related
Jul 2, 2015
When I add three columns to a table, they always go to the end of it.
I want to add a column as the third of the table, the second column that I want to add needs to be at the fifth place.
How can I do this using t-sql?
Is this possible?
View 7 Replies
View Related
Aug 4, 2015
I have a new cluster (2 sync, 2 async) with about 50 databases going from 1 to 200gb ( all of the objects are compressed).That at sql server 2012, sp1 CU7.I have several drives for logs with 200gb of space in there...I am having issues at rebuilding indexes on this env, ie, I have a table with the clustered index heavily fragmented (~80%), and the table has about 60gb of data, uncompressed that should be about 160gb.
The index rebuild is creating a log file big enough as to consume all the space that I have for logs, and that is only 1 table, so for sure my old process to maintain indexes (ola.hallengren code) won't work on this scenario.
View 8 Replies
View Related
Jul 20, 2007
Is there a place where i can find events that takes place in the sql server? Like adding data to a database or something like that....
Regards
Karen
View 4 Replies
View Related
May 12, 2015
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies
View Related
Aug 24, 2006
I've got a container with two variables: xxxNextFile and xxxLastFile, both with EvaluateAsExpression = False.
My container has a dataflow followed by a script task followed by another dataflow. The first dataflow starts with ae existing raw file source from variable xxxLastFile and wants to write output to a new raw file from variable xxxNextFile. The script component then sets the value for xxxLastFile equal to the value of xxxNextFile and generates a new value for xxxNextFile. The final dataflow component does pretty much the same as the first one, i.e. it uses xxxLastFile for the source and xxxNextFile for the destination.
I've got two package scoped variables pkgFolderTemp and pkgCurrentTime that I use to come up with a unique filename, due to the fact that pkgCurrentTime evaluates at runtime. The script below should be pretty self explanatory.
The problem is that I expect the variable values to have changed after the script task runs but they don't. I have checked to see if I possibly had the xxxLastFile and xxxNextFile variables scoped at the script task level or even at either of the dataflow levels but they are only scoped for the container. BTW, I have xxxLastFile and xxxNextFile declared as readwrite variables and pkgFolderTemp and pkgCurrentTime delared as read only.
For anyone wondering why I have the script component jammed between the two dataflows rather than in a PostExecute event for the container, it's because I couldn't get it to run at all when I tried that. One thing at a time :)
Here's my script:
Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimePublic Class ScriptMain Public Sub Main() Dim varLastFile As Variable Dim varNextFile As Variable Dim pkgFolderTemp As Variable Dim pkgCurrentTime As Variable Dim folder As String Dim time As String varLastFile = Dts.Variables.Item("User::xxxLastFile") varNextFile = Dts.Variables.Item("User::xxxNextFile") pkgFolderTemp = Dts.Variables.Item("User::pkgFolderTemp") pkgCurrentTime = Dts.Variables.Item("User::pkgCurrentTime") varLastFile.Value = varNextFile.Value folder = pkgFolderTemp.Value.ToString() time = pkgCurrentTime.Value.ToString() varNextFile.Value = folder + " emp_" + time MsgBox("User::xxxLastFile=" + varLastFile.Value.ToString() + " User::xxxNextFile=" + varNextFile.Value.ToString()) Dts.TaskResult = Dts.Results.Success End SubEnd Class
View 5 Replies
View Related
Feb 2, 2006
What are the downsides to have many multiple views on a SQL Server Database?
It was somebody's design decision to create 10 views for each vendor in our database- that could range from 20 to 500 vendors ... which means we could potentially have 5000+ views in our database.
What is the downside/problems with doing this?
View 17 Replies
View Related
Feb 2, 2008
Hello All,
We are running a hosted application where we currently have over 700 databases and expect to go to over 2000 within a year. This is all running currently on a 2 node fail-over cluster connected to a SAN.
I'm basically looking for advice on growth startegy from anyone that may have a similar setup.
Should I buy the biggest boxes I can afford and keep piling the databases on the same server? Should I add clusters and split the databases in bunches of, say, 500?
Performance is not bad right now but I'm not sure how long that will continue and I would rather plan than fight fires.
TIA
Andrew
View 1 Replies
View Related
Jul 20, 2015
I've deployed my ssis pkg to the server and created a sql job to run this pkg. So far, everything is fine. Today, I got a request to change some variables inside the package which is part of the .dtsconfig.  I want to edit the deployed .dtsConfig but it won't allow me and always complained  that this file has been opened by another program. I am sure i've closed my ssis designer and other notpad, why can't I edit and save .dtsconfig file?
View 4 Replies
View Related
Feb 5, 2004
Hi,
I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?
If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.
Thanks.
View 14 Replies
View Related
Jun 3, 2014
i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length...Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again
SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS
Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below
(which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS
View 1 Replies
View Related
Sep 10, 2006
Hello
I'm looking for a way to store a large chunck of text (200 lines) in a variable which can be called in different objects within the database.
For example: I have several stored procedures that create the same temp table which exists of 200 column names over and over again.
It would have to look like this
Import myScript (I keep thinking of Import like used in .NET)
and myScript could exist of:
CREATE #MyTable(
...)
or even some simple text or a part of a sql statement.
I'm familiar with synonyms but you can't use that in this scenario.
Then I thought of functions. The scalar function returns a result, not what I want here. Table valued function return tables, not what I want.
Many thanks in advance!
View 3 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
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
