Setting Variables In SSIS Packages
Feb 8, 2006
I have a bunch of variables which I need to initialize from Parameters table in SQL Server database.
I could think of the following two ways
1) Have one SQL Task for each variable and assign a query such as - Select ParamValue from Parameters where ParamChar = '<KeyName>'
2) Have a script task and write VB code to connect to the database and fire one query for every variable and set it accordingly.
I am not totally convinced with both of these approaches. Is there any better approach than these...
Thanks,
Loonysan
View 1 Replies
ADVERTISEMENT
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.
View 4 Replies
View Related
Jul 7, 2007
I have a simple task that I would like to manage through a SSIS package but do not know how to accomplish it. I need to perform the following tasks:
1. update a sql server table (ecwcust) and set the ftpstatus column to 'P' when the column's value is 'E'.
update ecwcust set ftpstatus = 'P' where ftpstatus = 'E'
2. declare a variable and set the variable to the number of rows that have a ftpstatus equal to 'P'
declare @newcustomercount int
select @newcustomercount = count(*) from ecwcust where ftpstatus = 'P'
3. if @newcustomercount > 0, copy all of the rows from ecwcust where ftpstatus = 'P' to an excel spreadsheet.
4. send an email to an email recipient with the excel spreadsheet as an attachment.
5. delete the excel spreadsheet.
I do not know how to create the variable @newcustomercount so that I can determine whether or not to create the excel spreadsheet and copy the records to it.
I have read all the online help and stuff that MS has but I can't understand the instructions. Please point me to some instructions that are understandable.
View 3 Replies
View Related
May 13, 2008
This is a fun one. I have a job that runs a SSIS package. It has some variables that I set a runtime. How can I programmatically change the values for these variables using SMO? I can get to the step in the job using VB but I can't find a way to programmatically change the value of the parameters of the job. The code loops through every property of the SSIS step. There's a command string but that's an ugly beast to code against. Any help is appreciated.
With oStep
Console.Write("Name: " + .Name + vbTab)
Console.Write("Proxy Account: " + .ProxyName + vbTab)
Console.Write("Type: " + .SubSystem.ToString + vbTab)
Console.WriteLine("Urn to point to job: " + .Urn.ToString)
iProp = .Properties.GetEnumerator
While iProp.MoveNext
Console.WriteLine("Property Name/Value: " + Convert.ToString(iProp.Current))
End While
End With
View 3 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
Mar 19, 2014
 "variables in SSIS for setting any connection string". I tried to Google it but cannot find any specific link.
View 4 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
Sep 8, 2006
Hi,
I am making use of the DtUtil tool to deploy my package to SQL Server.
Following is my configuration:
32-bit machine and 32-bit named instance of Yukon.
I have some package variables which need to be set in the code.
Previously I did it as follows:
Set the package variables in the code. For example:
pkgFile.Variables["User::DestinationServerName"].Value = <myvalue>
Deploy the package as follows:
applnObj.SaveToSqlServer(pkgFile, null,
destinationServer, null, null);
Here the package was successfully deployed and when i open those packages using BIDS, I am able to see that the variables are set to the values as doen in teh code.
Because of oen problem I am not using SaveToSQLServer method. So I switched to DTUtil tool.
Now I am doing it this way:
Set the package variables as before.
Deploy the package to SQL Server using DTUtil tool.
Now is the problem:
The package is successfully deployed. But the variables are not set to the value that I have specified in the code.
I also tried DTexec utility to set the package variable. Even that does n't work.
Can anyone help me out? Is there any alternate method to set package variables?
Thanks,
Sandhya
View 8 Replies
View Related
Jan 9, 2007
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
View 33 Replies
View Related
Dec 30, 2006
Hi, I have just started with ASP, and have a problem. I have created a stored procedure which looks to a specific tablename for information, based upon the users choice from a dropdown list.
The control works fine when executed from within visual web developer, and I manually enter the value that the variable expects. However I can not get the dropdown listbox value to be written to the SQL value. I have tried for days, traweled the net for answers, borrowed 3ft in height of SQL books! so either I am doing something fundamentally wrong, or I am missing something. My SP is:
ALTER Procedure GenericTableSelect
@tablename VarChar(20)
AS
Declare @SQL VarChar(1000)
SELECT @SQL = 'SELECT [base model] FROM '
SELECT @SQL = @SQL + @tablename
Exec ( @SQL)
and from the page the command to call it is:
SelectCommand=generictableselect></asp:SqlDataSource>
But this fails to compile and comes back with "@tablename not defined"
any pointers in the right direction would help.
The object of this is for two drop down boxes - the first is populated from one database of categories, the selection of which populates the second drop down list with items from within that category.
Cheers,
Richard
View 2 Replies
View Related
Apr 22, 2015
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
View 4 Replies
View Related
Dec 15, 2004
Hey guys,
I was wondering if there was a way to set a local variable from the results of a query or stored procedure.
For example, I have a stored procedure or a select statement that returns 1 row, with columns "a" "b" "c" "d"
I want to set my variable to be the value in column "b"
Is there a way to do that? I'm new to tsql, so any code would help.
I tried this, but I know my syntax is messed up
declare @myvar as integer
set @myvar = (execute mystoredProcedure).b
thanks
View 4 Replies
View Related
Aug 26, 2013
I have theses variables
@strStartDaysDateVARCHAR(10),
@strStartWeekDateVARCHAR(10),
@strStartMonthDateVARCHAR(10),
@strEndDateVARCHAR(10),
[code]....
So with @strStartDaysDate I'm getting number of records for the day.
With @strStartWeekDate I'm getting number of records for the week.
With @strStartMonthDate I'm getting number of records for the month.
I'm using GETDATE () function for @strEndDate because I can't count records after the current date.Of course those physical dates ('08/01/2013') are not good because I would have to have new dates each week or each month.How do I properly set my variables once so that they regardless to the month or year?
@strStartDaysDate
@strStartWeekDate
strStartMonthDate
View 8 Replies
View Related
Sep 6, 2006
I'm working on a solution in Visual Studio that has 3 SSIS packages, and now I want to add a 4th that needs access to variables defined in one of the other packages. How can I do that?
View 4 Replies
View Related
Oct 19, 2006
hi,
Is there any way to export a set of variables from each other package??
Issues comes when you have to built five packages which share the same variables. It's very tedious and hard to re-write them every time...
Thanks for your time,
View 2 Replies
View Related
Mar 12, 2008
So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?
DECLARE@Prefix VARCHAR(10),
@First_Name VARCHAR(50),
@Middle_Name VARCHAR(50),
@Last_Name VARCHAR(50),
@Suffix VARCHAR (10),
@Title VARCHAR (50);
SET@Prefix = ind_prf_code;
SET@First_Name = ind_first_name;
SET@Middle_Name = ind_mid_name;
SET@Last_Name = ind_last_name;
SET@Suffix = ind_sfx_code;
SET@Title = cst_title_dn;
select coalesce((@Prefix + ' '),'') +
ltrim(rtrim(@First_Name)) +
case when ltrim(rtrim(@Middle_Name)) = '' then ' ' else (' ' +
ltrim(rtrim(@Middle_Name)) + ' ') end +
ltrim(rtrim(@Last_Name)) +
coalesce(' ' + @Suffix, '') +
char(13) +
ltrim(rtrim(@Title)) +
char(13) as label
from co_individual (nolock)
join dbo.co_customer (nolock)
on cst_key = ind_cst_key
Thanks!
View 1 Replies
View Related
Jul 23, 2015
How do I set a variable to represent all of the data. For example using SELECT * will pull all of the data. Is there any symbol or way to declare and set a variable to do the same exact concept. In my query I have set many different variables which are used later on in my where clause but depending on what information I'm pulling from the data I don't wan the variable to have a specific value and instead pull all the data.
View 2 Replies
View Related
Aug 31, 2007
Hello everyone,
Can someone recommend me the most efficient protection level for SSIS
packages for my environment? I've been doing some reading on this lately, but can't really decide which one to use. The Rely on server storage for encryption (ServerStorage) seems interesting, but I'm unsure if that would work on my environment (Windows Authentication).
Currently I'm saving my packages to my dev server under "Encrypt sensitive data with user key" Protection level, but seems like the passwords for the FTP Connection for instance doesn't get saved when I deploy my package to the server.
Our environment consists of 2 dev servers + 2 prod servers with windows authentication, our SQL Agent Account is a domain account with local admin rights and sysadmin rights on the SQL Server and we try to use proxy accounts to access network resources instead of giving the agent access to everything.
This is what I found on MS's web site.
http://technet.microsoft.com/en-us/library/ms141747.aspx
http://technet.microsoft.com/en-us/library/ms141053.aspx
View 1 Replies
View Related
Oct 12, 2007
When executing a package from Business Intelligence Studio variables are well passed between packages but when same is done using dtexec utility variables don't get passed between packages. Does anyone have any idea why this could be happening and what would be possible solutions? Thanks in advance!
View 3 Replies
View Related
Feb 26, 2008
I am trying to understand the relationship of setting package configurations and setting variable values during job scheduling. I understand that I can select variables that I want to manipulate at run time using package configurations. I understand that the configuration file is an xml file that the job can be told to access at run time. Here are my questions:
1. Once I create a configuration file, do I physically modify the file to change the variable that is input at runtime?
2. Do I have to select the config file and then change the value using the Set values tab?
3. What is the relationship between the config file and the set values tab?
4. When creating a package configuration, when would you use the options other than XML configuration file?
View 8 Replies
View Related
Jan 3, 2007
I'm playing (and trying to learn)...
I have an FTP task in a for each containter and am setting the RemotePath using an expression (works great). Thought I could use this to start learning some of the scripting funtionality in SSIS (in a script task) so found some code in this forum (thanks Original Posters!) and tried my hand at some coding... Intent was to create a variable and then dynamically overwrite the Expression in the FTP Task from the script (I know I don't need to do this, I just wanted to use it for learning purposes)....
I have a variable named varFTPDestPathFileName (string) and want to set it to the value of varFTPDestPath (string) + varFTPFileName (string). Note: all variables are scoped at the package level (could this be the problem?). I did not assign any of the variables to ReadOnly or ReadWrite on the Script Task Editor page (seems to me that doing this in the code is a whole lot cleaner [and self documenting] than on the Task Editor page)...
I keep getting the following error:
"The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there."
Here is the script:
Public Sub Main()
Dim vars As Variables
' Lock for Read/Write the variables we are going to use
Dts.VariableDispenser.LockForRead("User::varFTPDestPath")
Dts.VariableDispenser.LockForRead("User::varFTPFileName")
Dts.VariableDispenser.LockForWrite("User::varSourcePathFileName")
Dts.VariableDispenser.GetVariables(vars)
' Set Value of varSourcePathFileName <<--- ERROR OCCURS HERE
vars("User::varSourcePathFileName").Value = _
Dts.Variables("User::varFTPDestPath").Value.ToString + _
Dts.Variables("User::varFTPFileName").Value.ToString
vars.Unlock()
Dts.TaskResult = Dts.Results.Success
End Sub
I would also like to be able to loop through the Dts.VariableDispensor to see the contents of the variables and their values.
Somthing like
For each ??? in vars
msgbox(???.Value)
Next
One other question... Do we always have to preface the variable with "User::" or "System::", if so can you explain why?
Any help would be much appreciated....
View 17 Replies
View Related
Oct 19, 2006
Hi,
I am trying to execute a DTS on sql server 2000 using the "Execute DTS 2000 Package Task".
I can see the inner variables and when I set the values everything works fine.
Now I want to pass the values dynamically. How can I achieve this?
Many thanks
View 9 Replies
View Related
Feb 1, 2007
I am trying to set the connection string in a connection manager at runtime. Here is what I have done:
1. Created a gv_DataSource, gv_Username and gv_Password
2. Created a ForEach Loop that reads DataSource, Username and password values from a variable (it is an For Each ADO loop Enumerator). The ADO recordset is read into by an Execute SQL task before the loop.
3. Mapped values from the recordset to variable in the ForEach loop's "Variable Mappings" page.
4. Used the variables in my Sybase OLEDB Connection Manager's "Expression" property, setting the "ConnectionString" property to:
"Data Source=" + @[User::gv_DataSource] + ";User ID=" + @[User::gv_Username] + " ;Password=" +
@[User::gv_Password] + ";Initial Catalog=blue;Provider=Sybase.ASEOLEDBProvider.2;Persist Security Info=True;"
5. I set the values in my database table for the connection-I set 2 connections for which I have Sybase OLEDB datasources setup.
When I run the package, I just get the first server's data twice, it doesn't set the second server's data during the second loop. I made sure the first one was working (i.e. the ConnectionString's property was being set by the data from the current variables) by setting the variables incorrectly in the variable properties page, and then running the package. So the first row of connection information is working, but the second loop around it doesn't seem to be working. I used a msgbox in a script task to show that the variables are mapping correctly in the loop, so it seems the second time around the connection information isn't taking from the variables.
What am I doing wrong here?
Thanks,
Kayda
View 1 Replies
View Related
Mar 26, 2007
It is pretty easy to create the package configuration to the shared, never-changing path to XML file. But to get this to work, I have to add a bunch of package variables which are driven from the configuration. I then use expressions to compute actual paths to source files and packages.
This business of copying a block of variables from one package to another is extremely tedious, and at least to me, something of a maintenance issue. I have found if I get ahead of myself and forget to create the variables first, then Visual Studio is NOT very forgiving, and will complain repeatedly just prior to crashing without the offer to save anything. If I add a new variable, it is like an easter egg hunt. This does NOT feel very single point of maintenance when variables must be used in this way to really effectively apply configuration, and it is so tedious and error prone, and unforgiving. I seem to spend a great percentage of time copying the names and datatypes and values of variables ONE AT A TIME. What is that all about?
I hear people telling the praises of configurations, but I must be missing something.
With DTS I used a system where global scope settings were "injected" into packages as they ran, and I never had to worry about anything, and it was damn hard to goof it.
Please someone, evangelize me!! Are people bypassing VS and using NOTEPAD on the packages or something like that to save time?
View 2 Replies
View Related
Feb 13, 2007
Hi,
Let's say I have a package taking as parameter "InvoiceID". I want to execute this package as a child in another package. The parent package gets the list of invoices to produce and calls the child package for each entry of the list.
How do I pass the InvoiceID to the child? I know I can use the parent's variables from the child but I don't want the child package to be dependant on the parent package. For example, I might want to execute the "child" package as a stand-alone in development (providing it with a predefined InvoiceID). I might also want to call the same child package from another parent package with other variable names.
What I would like to do is "push" the value instead of "pulling" it. I know it's possible using the command line and the /SET option (ex.: /SET Package.Variables[InvoiceID].Value;' 184084)... Is it possible using the Execute Package Task?
Thanks
View 4 Replies
View Related
Mar 1, 2007
We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.
What we have now is this:
string cmdLine = job.JobSteps[0].Command;
cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::GroupId].Value;1";
cmdLine += " /SET \Package.Variables[User::period].Value;"" + periodEndDate + """;
job.JobSteps[0].Command = cmdLine;
job.Start();
It appears that when the job is run, the modified command line is not used.
What is needed to supply runtime parameters to a job step when starting the job via SMO?
Thanks,
View 3 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
Apr 24, 2006
Hi,
Is there a way to reuse existing Configuration Files in SSIS Packages?
I have two packages with the same connection managers, properties and variables.
I want to reuse the Config file of the 1st package in the second one. While building the packages it gives error like
“Config file already exists cannot recreate”.
View 1 Replies
View Related
Jun 8, 2007
Hi All,
I have created SSIS (.dtsx) files and have stored in different servers.
Now my query is I want to move all dtsx files from filesystem to Sqlserver2005 database how should i do it.
Please help
Regards
Hassan
View 2 Replies
View Related
Oct 16, 2007
I need to create the ssis package in business intelligence developement studio i am need to sqlserver 2005.When i opened the BID studio i am not able to see the integration services packages type..
Please help the steps to design the package.
I have experience of using the 2000 in dts designer mode.
Thnks for your help in advance.
View 1 Replies
View Related
Mar 14, 2008
Hi,
iam New to Packages. So Please give me the guidelines to Learn Packages.
Thanks for Advance
View 3 Replies
View Related
Mar 12, 2007
Hi,
I upgraded to Microsoft SQL Server 2005 Service Pack 2 and now when I run the master SSIS package( that has several packages in it), all the packages run twice.
After removing SP2, they work fine. Any ideas how to make this work with SP2?
View 2 Replies
View Related
