Is there any way to configure a SSIS package variable using a SQL Statement?
The "Package Configurations" does not accept SQL statement.
I have about 5 package variables that need to be initialized using queries.
I don't want to end up writing 5 execute sql tasks to initialize them.
In DTS we could do that at one place using Dynamic Properties Task.
Any 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.
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
What am I doing wrong in the following snippet of code?-----------------------------------declare @FullPath varchar(255)set @FullPath = 'C:MyDirMyFile.txt'bulk insert Northwind.dbo.Sometablefrom @FullPathwith(FIELDTERMINATOR = ',')-----------------------------------This works when I write the path out instead of using the @FullPathvariable. I have a cursor that will Bulk Insert all files names from atable. The path will not change but the file name will. I guess I could usea system call to rename the file in use to a hardcoded name and then renameback to original name but I would like find a way to make the above conceptwork if it is possible.TIA,-p
View 2 Replies View RelatedHi,
I try to run sql statement by placing them in a variable as given below but receive the following error message
set @sql_string= 'BACKUP DATABASE '+ @temp_db+' TO '+ @bk_device+' WITH FORMAT,INIT, NAME = '''+ @bk_name+''''
EXECUTE IMMEDIATE @sql_string
Error Message
Server: Msg 2812, Level 16, State 62, Line 10
Could not find stored procedure 'IMMEDIATE'.
Server: Msg 2812, Level 16, State 62, Line 18
Could not find stored procedure 'BACKUP DATABASE DTSTEST TO bk_dev1 WITH FORMAT,INIT, NAME = 'Full backup''.
Thanks
John Jayaseelan
Hi
I am trying to include a string variable in a Select Statement.
My problem is that when I code with the user name hard coated in the SQL Statement it works fine (see below:)
cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = 'jdoe' " But when I try to use the String variable I get an error (See below):
cmd.CommandText = "SELECT UserPswd, StudioID, StudioCode FROM Users WHERE UserName = " & StrUserName
I know there must be something wrong with my syntax ??
Thanks
Jackson
HiI'm using the SQL 2000 table variable to hold 2 different fact sets.I'm declaring the variable @CurrentTable and inserting into it using aSELECT statement with no problems.I'm leaving certain of the columns null in order to later update themwith the PK.Problem is in the UPDATE syntax I'm usingUPDATE @CurrentTableSET ManagerTitle = (select mgrs.pos_title from mgrs) wheremgrs.pos_num = @CurrentTable.MgrPosNumIt is insisting I declare the @CurrentTable variable when I try to useit in the where clause.Is it simply out-of-scope or am I really doing something foolish?Andrew
View 2 Replies View RelatedHi,
Is it possible in any ways to Configure a SSIS package in such a way that based on the User Input the package runs. For e.g if there is a table which has say 10 distinct groups. Normal SSIS package would ideally pick all the data from the source to the Destination
I want to know how to configure in Such a way that I should be able to say Group X as the input and data related to GroupX alone should be copied.
Select * from SomeTable where GroupName = @CongigVar.
how to achieve something like this?
thanks
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
Hello All,
I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.
i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?
Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string
Thanks for your time.
I want to loop through a recordset of email addresses, and for each value in the recordset, I want to perform a set of task which includes creating an Excel spreadsheet and emailing it through to those email addresses.
I have 2 DTS package.
Package 1 - This contains an ActiveXScript task. The purpose of this package is to do the looping through of the recordset, and passing the email address variable to package 2.
Package 2 - Receives the email address from package 1. Package 2 contains the creating Excel spreadsheet and emailing it through to the recipients function.
I am having difficulty passing the variable from package 1 to package 2.
The code I have to date for the ActiveXScript task in package 1 is:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim oPackage
Dim oStep
Dim oTask
Dim oCustTask
Dim oGlobal
'Initialise package
Set oPackage = CreateObject("DTS.Package2")
Set oStep = oPackage.Steps.New
oStep.Name = "TestStep"
Set oTask = oPackage.Tasks.New ("DTSExecutePackageTask")
Set oCustTask = oTask.CustomTask
oCustTask.Name = "TestCustTask"
oStep.TaskName = oCustTask.Name
oPackage.Steps.Add oStep
oCustTask.FileName = "C:TempVeraTest1.dts"
oPackage.Tasks.Add oTask
set oGlobal = oPackage.GlobalVariables.New("CurrentAirlineCode")
oGlobal.value = "SIA"
oPackage.GlobalVariables.Add oGlobal
'Execute package
oPackage.Execute
Main = DTSTaskExecResult_Success
End Function
This does not work. Can anyone please help?
Thanks very much in advance.
How to add SQl statements to the package created by Export Data wizard? I need to add SQL coming from file, the flow would be like this:
1) SQL would drop all objects: (views, tables, stored procedures) etc
2) SQL would create all objects: (views, tables, stored procedures) etc
3) Export Wizard SSIS package would execute
Also is there a dynamic way to add more tables do the package once created in SQL database? I would like to put the whole task on a scheduler too...
I know there is a global variable feature in DTS that allows you to pass data or object references between several different Activex scripts in a single package.
What I'm looking for is a method of using a Transact-SQL variable (like @whatever) that I could use across several "Execute SQL" tasks within the same DTS package.
Does anyone know if this functionality exists (directly or indirectly)?
Thanks,
Joe T.
Couldn't quite find the answer I was looking for via a forum search.
I have 9 packages that are currently called by a master package - all child packages take a datetime variable from the master package which is subsequently used in the child processes.
The question I have is that if I run the master package manually from Visual Studio I can set the master package variable manually to a date of my choosing and run it, which works fine. However we will be wishing to automate the package execution at some point and want to know the best way to run the package(s) on an automated basis and have the data variable supplied to the master package.
What would be the best way to do this ?
Presently we have a variable called MasterPackage_vLoaddate which is a DateTime data type.
Any help appreciated.
How to get the value of the variables when i debugging a package?
Thanks!
HI, we have something like 120 packages that need to be "upgraded" to a newer version of a template. Basically, we need to add a bunch of variables of various types (12-15 variables). Is there a way to open the package in a script task and add those variables programmatically? Or is there another way to do it (e.g. modify the dtsx file)?
Thank you,
Ccote
I was able to write successfully a Script Task to set the values of several package variables. But when the execution completes, the variables still contain values which were specified by default.
What I want is a method of persisting the values assigned to a variable through a script. I believe this was possible in DTS
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
I have 10 or so packages with package scope variables. I would like to create a package that not only runs all the packages but also sets the variables within the packages being executed.
A) Whats the best way to run all the packages from one (package??)?
B) Whats the best way to set the variables in the child packages, without having to change them manually everytime I run it.
Thanks,
Mardo
Hi,
I am running my package in this way:
exec xp_cmdshell 'dtexec /SQL "SBLoadExcelDBLog" /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];"aaab"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareaaab.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp" '
I got errors:
Started: 3:49:51 PM
Progress: 2006-01-30 15:49:52.34
Source: Extract AdHoc Data from Excel
Validating: 0% complete
End Progress
Error: 2006-01-30 15:49:52.46
Code: 0xC0202009
Source: Extract AdHoc Data from Excel Excel Source [649]
Description: An OLE DB error has occurred. Error code: 0x80040E37.
End Error
Error: 2006-01-30 15:49:52.46
Code: 0xC02020E8
Source: Extract AdHoc Data from Excel Excel Source [649]
Description: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.
End Error
Error: 2006-01-30 15:49:52.51
Code: 0xC004706B
Source: Extract AdHoc Data from Excel DTS.Pipeline
Description: "component "Excel Source" (649)" failed validation and returned validation status "VS_ISBROKEN".
End Error
Progress: 2006-01-30 15:49:52.51
Source: Extract AdHoc Data from Excel
Validating: 25% complete
End Progress
Error: 2006-01-30 15:49:52.51
Code: 0xC004700C
Source: Extract AdHoc Data from Excel DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2006-01-30 15:49:52.51
Code: 0xC0024107
Source: Extract AdHoc Data from Excel
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 3:49:51 PM
Finished: 3:49:52 PM
Elapsed: 0.703 seconds
Hi,
We can set the connection parameters using Variables as well as thru Package Configuration. My question is, if we use both to pass value for a connection string, which one will take precedence or in other words, which value will be used?
Advance thanks for your help.
Hello,
I have three package variables that I need to have access to at runtime. All three variables have package scope. The first two, 'StartDate' and 'EndDate' are DateTime variables. The third is called FilePath and is a String variable.
I have taken the following steps;
1. Enabled package configurations
2. Set up all three variables as Parent Package Variables, and have targeted the 'Value' property for each.
3. In the properties of the solution, I have set AllowConfigurationChanges to True.
4. After the package was built, I ran the Package Installation Wizard from the Manifest.
I have done both File System and SQL Server installations. When I go to run the package, none of the three package variables are available for modification.
What am I doing wrong?
Thank you for your help!
cdun2
Hi,
I am executing one of my package through cmd line using DTEXEC and also passing parameter.
The CMD line is :
"E:Program FilesMicrosoft SQL Server (x86)90DTSBinndtexec"
/FILE "C:UnitTestingLGASProd-InFeed.dtsx"
/CONFIGFILE "C:UnitTestingLGASDataFeedConfig.xml"
/MAXCONCURRENT " -1 "
/CHECKPOINTING OFF
/SET package.variables[_VarProdValue].value;0
/REPORTING E
when i try to run the job i am getting the error i putted below...............
Started: 9:37:21 PM
DTExec: Could not set package.variables[_VarProdValue].value value to 0.
Started: 9:37:21 PM
Finished: 9:37:22 PM
Elapsed: 1.188 seconds
Please tell me any one what is wrong in that CMD line.
Thanks
Thiru Senthil
I have a For Loop container where each iteration a Master record is inserted. I also have a number of package variables capturing rowcounts from the dataflow task. I later update the master record with the rowcounts. How do I initialize the package variables containing the rowcounts because currently, some of the rowcounts are being populated with rowcounts from another file.
thanks
I have defined an active X task (converting from SQL 2000)
Function Main()
DTSGLOBALVARIABLES("TEST").Value= InputBox("TEST : ","Message Box")
Main = DTSTaskExecResult_Success
End Function
The problem is that this variable is not recognized by the subsequent tasks although I have defined a variable "TEST" in the variable lists.
Is there something else to do to get it recognized ?
Thanks for helping,
Pierre
Hello,I'm using BIDS to create a package, and I need to pass in a variable value; how does this new DTS process work, in deploying the package and passing in a custom value at runtime?Thanks.
View 2 Replies View RelatedHi,
what I basically want to do is to delete all records from all tables where the field loadnumber equals a certain value.
To do this I've defined the global variable 'DSLoadNumber' (integer) in a DTS package, and I want to use the stored procedure sp_msforeachtable in an Execute SQL Task.
This is the code in the Execute SQL Task:
sp_msforeachtable
@replacechar = '~',
@command1 = "delete from ~ where loadnumber = ?",
@whereand = " and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')"
This code can be parsed without any problem, but the ? is not seen as a variable.
When I get the ? out of the quotes the code cannot be parsed any longer.
Finally I've written a cursor to the job which looks like this:
DECLARE @tablename varchar(35),
@loadnumber int
SELECT @loadnumber = ?
DECLARE delete_cursor CURSOR FOR
select name
from sysobjects
where type = 'U'
and name not in ('dtproperties', 'dsloadlog', 'FotoMediaProduct', 'ProductFF', 'ComStat', 'ProdStat')
OPEN delete_cursor
FETCH delete_cursor INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
exec('delete from ' + @tablename + ' where loadnumber = ' + @loadnumber)
FETCH NEXT FROM delete_cursor INTO @tablename
END
CLOSE delete_cursor
DEALLOCATE delete_cursor
But this code doesn't compile either...
Any suggestions on how to get the code working within DTS?
Stef
I'm developing an SSIS package that will theoretically be run in two different ways. One is as a nightly job, called by a SQL job. The second is from a web application. When run as a job, it will be looking for a flat file in a known location. Run from the web, the file name and location are determined at runtime. I've tried to handle this by using a variable to hold the flatfile connection string, and setting the variable value to the known location. Then, when called to the web, I determine the file location/name at runtime, and pass it to the package. I'd assumed that if I pass the value into the package, it would override the variable's value that I'd set, but this doesn't seem to be happening. I realize this could very well be due to a coding error, but I thought I'd check to see if this is even a viable approach to be taking. If a variable's value is set in the package itself, will it be overwritten by a value passed to the package (assuming no stupid mistakes)? Or is this entirely the wrong approach to be taking for what I'm trying to do?
View 6 Replies View RelatedHi,
I am sending variables from package to package by configuration parent packages in each package...
I usually set logging to text files in a certain path that should be received from the parent variable... the strange thing is that if i let the variable in the child package with an incorrect path, my package just through an error even if i am passing the variable from the parent with a correct value...
So... my question is... does my package starts execution without any synch over the parent packages? When are they set? How can i be sure to start my logging with the right parent variable settings?
You can try for example to create a package... add a new variable like "A" and "B" set its value to a default string like "I DONT WANT THIS", add an Execute SQL Task that will record the values of "A" variable to a temporary table...
Then use dtexecui.exe to execute the package and set variable "A" to "THIS IS WHAT I NEED" you will see that in the table will appear one record with the "I DONT WANT THIS" value...
I can't understand this...
Also... if i have 100 variables configured to get values from the parent... is the execution synch or asynch?
Best Regards,
I have read a few different threads on this and I am still not having any luck. I have 4 dtsx files in my project. One represents the parent package. I have set up 3 Parent Package Variables and have assigned each to the value property of 3 variables within the parent package.
Not sure if I did this correct because when I execute the parent package, it in turn executes the first child package, but the child fails because the variables needed did not set the correct value for a connection string in the child's data connection.
I just need to know how to use these parent variables from Package A within the child (Package B) when setting up my expression for a connection- example: "@[User::varParentFilePath] " - This is the local variable name in the child. This is the variable I am assuming that I am supposed to be able to set the value property of from the parent variable?
I feel that I am close, but may need a little more direction. Thanks
I would like to pass variables to the package before it is executed (e.g. I am calling the bcp utility and I need to pass a password to the command line), so that at runtime a variable is set and then used.
Does anyone has some hints for good approaches? Every idea is welcome.
FYI: I do not use the bulk insert task as I need an errorlog file and the command is buggy with the errorfile option, therefore I chose the bcp approach. My connections are dynamic as far as servername and userid is concerned, but I did not find a solution for the password issue.
Hello,
I am looking into a new project and I assume that I can end-up in a situation where the same package could be called at the same time (or close enough) by different jobs passing different values to the package's variables.
Is it going to work? Will each instance of the package run in a separate thread?
I would think so because I already have a package similar to this.
The different is that the new package will run for a long time as opposed to the older one which runs for a second or so only.
Just wanted to make sure and gather your feedback.
Thanks