SQL 2012 :: Getting Environment Variables For SSIS Project?
Sep 9, 2015Is it possible to export the environment variables for an SSIS 2012 project? And if so how is it achieved?
View 2 Replies
ADVERTISEMENT
SQL 2012 :: Mapping Package Variables In Project Deployment Mode To Environment
May 27, 2014I 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 RelatedSQL Server Admin 2014 :: SSIS 2012 Environment Variables Are Not On Sort Order
Feb 10, 2014I 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.
SQL 2012 :: Create And Map Server Environment For Project Deployment
Oct 27, 2015I know how to "Create and map a Server Environment" using management studio [URL] ......
Is it possible to create the environment in DTS (2012) and have that environment created on the target server?
Setting Environment Variables For SSIS Packages
Feb 29, 2008
Hi all,
We're just in the middle of performing our first release of SSIS packages through various environments.
The way we are set up currently is the developer will check the package(s) and related config files out of source control, develop on their own machine and check everything in again. Then we deploy the packages consecutively to the Dev, Tst and Prd servers.
We are going down the path of using one environment variable for every config file. some packages share config files (e.g. we've only one config file for each database or ftp connection etc.) and some config files are package specific (error log file connections and success/failure e-mail sources etc.).
What we want ideally is a script that we can check into source control that will create the environment variables on a server at deployment. The "set" command at the command line can be used to change the value of an environment variable, or to create a session-specific variable, but not to create environment variables.
So far the only method that we're using is manually typing in the environment variable names via control panel and copying and pasting the paths into the value fields. Given that we're deploying potentially hundreds of config files, it's obvious that this new-fangled GUI point-and-click and copy-and-paste method of deployment is absolutely foolproof and totaly not prone to any error whatsoever.
Please tell me there's a way to create and set environment variables without going through control panel. running a script or something to do it automatically will: Ensure that each environment is set up accurately and identically, eliminating human error. Ensure that when a developer checks out a package to their local drive, although they may have to change the variable values, he can at least create the relevant variables without having to type them in. Enable efficient migration to another new server (for example during Disaster Recovery).
Can anyone point me to some example scripts at all?
Kind Regards,
Andrew.
SQL 2012 :: Environment Variables Not Sorted In Dropdown When Configuring Parameters / Datasources
Sep 25, 2014Any 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.
Team Project In TFS - SSIS Project Sets Itself As The Startup Project
May 29, 2008
For some reason in a Team Foundation Team Project that has multiple project types (SSRS, SSIS, WebSite, C# Business DLL...), the SSIS project makes itself the startup project to the team project. If I explicitly set another project as the startup project to the team project and then select an SSIS package in the SSIS project in the team project, the SSIS project becomes the startup project automatically.
I am using Visual Studio 2005 SP1.
SQL 2012 :: XML SSIS Variables
Sep 17, 2015I 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 RelatedSQL 2012 :: Dynamically Calling Child SSIS Packages Within A Project
Mar 9, 2015I've not really used SSIS for a while, and I'm now building some in 2012 and trying to utilise some of the features in the 2012 SSIS catalogue; however I've hit a bit of a stumbling block.
What I'm trying to do is have a master/child package relationship, with several child packages and where the child packages themselves are dynamically called (i.e. the master package may call a different child package based upon some value or state of data already processed.)
When I try and create an expression for the PackageNameFromProject property... well, that property doesn't appear to set dynamically. I know how to do this for old style packages by creating expressions for the package name etc; but that way I can't use the package parameters I have from my master package.
SQL 2012 :: Using Variables In SSIS Tasks
Dec 2, 2014I 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.
SQL 2012 :: SSIS And Variables With Where Clause
Jan 15, 2015I 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
SQL 2012 :: SSIS Project Deployment Model Miscellaneous Files Not Being Deployed
Jul 9, 2015I have an existing project that I have added a simple text file. I am using the Project Deployment Model for this project. I save the project, close it and open the project and the file is there under the Miscellaneous folder. I successfully deployed the project to the server. When I retrieve the project using the Integration Services Import Project Wizard, all of my package modifications are there and the packages up to date but the txt file I added to the Miscellaneous folder is not there.
View 1 Replies View RelatedIntegration Services :: Edit Project Param Variable Value In SSIS 2012
Jul 7, 2015I have declared one variable in Project param with some value.
I want to edit that varaiable through Script task using C# / VB code.
Looking for C#/VB code which needs to be used in Script task to edit project param level variable[not for package level variable].
Integration Services :: SSIS 2012 ISPAC And Project Naming Difference
Nov 29, 2013We are using MSBuild with a custom activity to build and deploy ISPACs from SSDT BI 2012. We are building the ISPAC by calling the invoke process activity which inturn performs a build of the solution using devenv.exe.
All of this is working fine until we have solutions/ projects (dtproj) with complex names. We have a solution named Company.Project.SSIS.Package.sln when we do a build it creates an ISPAC as expected however it is named incorrectly - Company.Project.ispac.
When we subsequently deploy the ISPAC, it creates a project in the catalog called Company.Project.SSIS again this is wrong. This also happend if i perform a manual build in VS2012.
The issue is, the automated deployment of the ISPAC fails as it is looking for a file with a different name. Is there a way of changing these settings?
SQL 2012 :: Error Executing Packages In SSIS Project On Server After Editing On Client
Sep 24, 2014I am building a bunch of packages on our new server and all was going well until I edited the project using the client tools on my PC. I now receive the below error if I try to execute any of the packages on the server (all is still fine on the client). I have scoured the net but I don't seem to be able to come up with a solution. I have tried altering the folder & object permissions for my login (that created the project on the server and edited using the client) but I still get the error.
ERROR:
TITLE: Microsoft Visual Studio
------------------------------
Failed to start project
------------------------------
ADDITIONAL INFORMATION:
Exception deserializing the package "Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied.". (Microsoft.DataTransformationServices.VsIntegration)
------------------------------
Access to the path 'G:VisualStudioTestTestbinDevelopmentTest.ispac' is denied. (mscorlib)
------------------------------
BUTTONS:
OK
------------------------------
SQL 2012 :: SSIS Variables Stored On A Database
Jul 3, 2014I 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?
SQL 2012 :: How To Add Variables To SSIS Packages Per Batch
Nov 18, 2014I 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 ?
SQL 2012 :: SSIS Variables - Change Value In Hundreds Place
May 21, 2015in 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...
Migrate Project From Test To Prod Environment?
May 3, 2007So, we are about 3 weeks away from going into production, and somehow we failed to give much thought to deploying our RS project into production.
We have over 110 report models that need to be deployed into production, and until now, we just deploy into our dev and test environments using Visual Studio. But, in our production environment, our deployers will not have Visual Studio.
Is there any simply backup/restore method that can be used to move our test environment into production? Please don't suggest a copy of each file one at a time /sigh.
Appreciated,
Scott
DTS Environment Variables
Nov 21, 2005I have about 40 DTS packages that I want to run against three different databases on the same server. Can someone suggest an easy way to run these jobs and differentiate each time which SQL or INI file to use.
Thanks,
John Shaening
SQL 2012 :: Passing Record Counts From Execute Task In SSIS To Package Variables
Mar 4, 2014I'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).
Integration Services :: Assign Values To Variables Dynamically In 2012 SSIS Package
Jul 16, 2015Can 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.
Integration Services :: Pass Variables Parent To Child Packages In SSIS 2012
Aug 8, 2013How 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 RelatedUsing Environment Variables In Dtexec
Jul 6, 2007Hi all,
does anybody know if it is possible to use environment variables when calling dtexec utility?
I'd like to run packages stored on server's file system from directory that I've had specified in an environment variable called SSIS_PackagesPath.
Now, I'am trying to write dtexec command, where path to the actual SSIS package would be concatenation of environment variable (i. e. path to package directory) and name of package itself (written explicitly). Is this syntactically possible?
The reason behind is to be able to easily modify package storage directory for multiple scheduled jobs that run SSIS packages.
Any other ideas are hapilly welcomed.
Thanks,
Marek
Visual Studio 2005 Automatically Sets SSIS Project As Startup Project
Feb 23, 2007I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object.
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
Thanks.
Integration Services :: Importing Deployed Project From Catalog Into New SSIS Project
Jun 16, 2015in order to maintain a deployed project into an Integration Services Catalog I'd like to know if it is possible to import it into a new project inside SSDT.
View 5 Replies View RelatedIntegration Services :: Automate Process Of Creating A Project On SSISDB Based On SSIS Project Name?
Apr 20, 2015I've got an SSIS solution file with project deployment model in VS 2013 and would like to deploy that to SSISDB on different environments.All these days I followed the regular way to create a project in SSISDB and deploy it to that. Now want to find out if i can automate this process and so got some questions
1. Can we automate the process of creating a project on SSISDB based on our SSIS project name? This will be like when we do a deployment it should check if the project exists or not on SSISDB based on our SSIS project name, if the project exists we just deploy the packages in the project and if the project does not exists in SSISDB it will create that project and deploy the packages.
2. Can we also automate the process of creating environments? In traditional way we manually create the environment variables under environment tab of SSISDB, but can we make that also as part of deployment? Like when we are releasing to Dev server we look if that particular Dev variable exists on that server, if it exists we just update the existing stuff and if it does not exists we just create it.
Transact SQL :: Get Windows Environment Variables
Aug 31, 2015I am migrating the BE of an Access app. to SQL server 2012. I need to get the user's login name (Windows Authentication login). This can be done using xp_cmdshell, but, xp_cmdshell is considered dangerous and I wouldn't be able to run it once I deploy the app. to the company servers (currently I have SQL server on my computer and as an admin I can enable xp_cmdshell to run, but IT doesn't allow it in company servers for security reasons).
Another question, is it possible to send data from the logged in user from Access to SQL server? What I need to do is let SQL know the username of the logged in user, then, use it to filter the data on SQL. Idea is that user can only run queries for his data (he can't view other user's data unless he is a manager or an admin (currently the app. in Access logs the user in automatically if his Windows Domain username is found in the user's table, and set's his role found in the Roles table). It is this functionality that is giving me some problems to migrate to SQL.
I created a function that uses the System_User SQL built-in function, this retrieves the SQL login username, but, the app. uses 1 SQL local account to connect to the server, so in essence it doesn't work as I need the Windows Domain account username.
Raw File Destination And Environment Variables
Feb 27, 2006
when using a raw file destination it would be nice to be able to use an environment variable for the filename property.
like
%my_extract%data.txt
instead of
c:my_extractdata.txt
Setup And Upgrade :: Several Same Environment Variables In Path
May 5, 2015I noticed accidentally today on my PATH and discovered that I have several variables for different sql versions. I can understand this as I have upgraded from 2008, to 2012 then 2014. Here are the list of variables
C:Program FilesMicrosoft SQL Server100ToolsBinn;
C:Program FilesMicrosoft SQL Server110ToolsBinn;
C:Program FilesMicrosoft SQL Server120DTSBinn;
C:Program Files (x86)Microsoft SQL Server120DTSBinn;
[Code] ....
Can I delete all variables related to older versions safely without any issue.
SQL 2012 :: Shrinking Database For Dev Environment?
Nov 26, 2014I've read lots about why you shouldn't normally shrink databases in posts such as this:
[URL]
But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.
I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.
Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.
SQL 2012 :: Merge Replication Environment
May 10, 2015We are working in a Merge replication environment where we have SQL Server 2005, 11 publications and 2 subscribers.We used to get lot of incidents from the Application owner for blockings, recently we faced a situation where the lead blocker is in sleeping state and the session was used by the merge agent.Checked the query that the session was running, it was sys.sp_MSenumgenerations90;1.
View 1 Replies View RelatedSQL 2012 :: Setting Log Shipping In AlwaysOn Environment
Jan 23, 2013We have 4 Servers which have SQL SERVER 2012 and "AlwaysOn" have been enabled on all 4 servers:
Server1,Server2,Server3,Server4
Server1 is the Primary node and thr rest are secondaries. There is a Sync relation between Server1 and Server2 and also there is aSync relation between Server1 and Server3 & Server4.
Is it possible to setup log shipping from Server2 & Server3(secondaries) to two new servers?