I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.
I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?
I have created a SSIS package with a Foreach Loop including a Data Flow Task, which in turn include a Row Count component which pass the row count value to variable with package scope. The variable is used in an Execute SQL Task following the Data Flow Task.
The package executes successfully when executed on its own, but when executed as a child from a parent package (which only include an Execute Package Task) the variable from the Foreach Loop becomes NULL.
There are a lot of other variables in the package receiving values dynamically without any problem, the row count variable however is the only variable in the package that receives a value as part of a Data Flow (and used in following tasks within the Foreach Loop).
Why does the variable become Null? For your information, I am using a variable with package scope and no variables from the parent package are used or passed from the child package to the parent package.
(For your information, we are running the 64 bit version)
We have one main package from which 7 other child packages are called. We are using ParentPackage variables to assign values for variables and database connections.
While the values from ParentPackage variable get assigned to some of the packages properly, to others it doesn€™t assign the value.
For example: Except for one of the packages the database connection string gets assigned properly to all other packages.
Similarly, in another package one of the variables doesn€™t get assigned. In other packages it is assigned properly.
We have checked all the other property values and they are exactly the same.
We cannot make any head or tail of this erratic behavior.
I have packages stored in SQL store. I was letting users run the packages from a .net app that I made with
Microsoft.SqlServer.Dts.Runtime
Now I have noticed this causes the packages to run on the client pc cpu, as well as the network traffic is done via the client pc, in my particular case this is slow.
From the doc and in this forum I have found that you can run a package on the Server cpu through sql agent, let packages be run in a sql job. after that you can start a package from an application with the SQL sp_start_job .
But How do you set a user::varibale in a package if you have to start the package from a sql agent job ?
sub packge = ArchiveTicketLog. when i try to execute the sub pacakge it thorughs up error saying the connection string in not set. i am setting in the parent connection string of the main package. when i execute through the pacakge itself it works fine but when i execute through the Sql command line it fails.
is there some thing to to "/Set [package name]Package.Connections[ConnectStringArchiveTicket].Properties[Value];
Hi, I have created a DTS Package which copies data from a Access Database table to a SQL Server table. The DTS Package is scheduled to execute every 15 minutes. Everyday the source table name will change. Is there a way to reset the source table name everyday without changing it through DTS wizard. Thanks, Vivek Gupta
I have a SQL 7 package which uses a select statement. Within the the select statement is a where clause. I would like to define a string variable for the object of the where clause. I would like to have this string variable passed into the package just prior to execution of the package.
I have a package with variable "A" defined at the package level, type "string".
I subsequently reference variable "A" in a script task, as a ReadWrite variable. In the script, I simply assign the value of another string variable to variable "A".
However, when I run the package, I *intermittently* receive the error below. Not every time though, sometimes it runs fine without the error. What's going on here?
Thanks
Error msg:
The type of the value being assigned to variable differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
I am attempting to export data on a daily basis via DTS to an XLspreadsheet. I would either like to: a) have a separate worksheet inthe spreadsheet for each export or b) a completely differentspreadsheet for each export. Whenever I attempt to use a variable asthe name of my table in the DTS package, it will not let me.Constructing a string and attempting to execute it also fails,although both of these work in query analyzer. Any advice?Peter
I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable.
I am trying to execute a ssis package, where a user will be able to excute the package from an ASP page, the user will select a file, this will be the source file for the SSIS package, this will change when ever the package is executed, I was thinking of just using a sqlClient.sqlCommand and editing the command text and excuting it that way, or through a SP, would this be the best solution? Is there a better solution? and if not what would be the SQL command to execute the package, along with editing the variable?
I want to use a variable in a number of SQL statements as paramenter. But I am not sure how to assign the value I want to the variable. The value I want is the maximum week number from one of my tables. I know how to put this value into a new table (which is one field and One row), but I cannot see how to pass this value to my variable. Using the table in my SQL is not straight forward unfortunately, at least not for me.
I have issues with the Connection Manager in the SSIS package when using package configs thru environment variable.
Here goes..
SSIS package1:
Connections used: devcon1, devcon2 - Dev Env and testcon1,testcon2 - Test Env. Now using all four. Ideally either devcons or testcons should reside at a time.
Environment variable:
Pckg_config = <location of config file which has testcon1 and testcon2>
I need to use only devcon1 and devcon2 in Dev env. In test i need to use only testcon1 and testcon2 Hence i set the values of devcons in devEnv.dtsconfig and testcons in testEnv.dtsconfig
Now i remove both testcons from ssis package. If i try to run the Test Env and my testcons which are marked in testenv.dtsconfig are not found as connections in ssis package then the ssis gives error wanting for those connections.
SSIS maintains the connections in the Connection Manager per package. Although internally it is a pool of connections.
Ideally i should be able to play around with the connection at run time. My package now works, if it is deployed with all the devcons and testcons together. However, ideally it should be either devcons or testcons. I am trying to be more explicable to reach to the masses here.
Am i doing something wrong? All your efforts in solving this puzzle will be greatly appreciated. Please participate.
I'm in the process of creating a series of packages to do the ETL for a datawarehouse. As such, I've got quite a few DataFlow tasks scattered through them.
The problem I'm coming across is where the disk that the temp files (created during the processing) are put on is short of space (when compared to some of the data sets I'm copying around and noting that it's the System disk, not the "Data Drive").
To get around this, I found number of article scattered around that suggested using the BufferTempStoragePath property of the DataFlow tasks to redirect the temp files to somewhere else. This works fine for the tasks that I hard-code the new directory.
Noting the number of these tasks that I want to redirect, and also that the Dev environment will be different to the Production environment (and I have no control over the drive letters, paths, etc, in Prod), it was suggested that a package variable be created, assigned to the property and then said variable could be exposed via the Package Configuration file.
That's great as far as it goes, but I just cannot, for some reason, make it work.
The property in the DataFlow task is then set to: @BufferTempStoragePathValue.
When the package is executed, it logs the error message "The buffer manager cannot create a temporary storage file on any path in the BufferTempStoragePath property. There is an incorrect file name or no permission."
My question for the group is two-fold: 1) what am I doing wrong in the setup of the property/variable, and 2) what are the security permissions that are required for the (new) folder.
It's got to be something obvious, I just can't see what!
We are using Package configuration with environment variables. The problem we are having that if we try to open project from other PC (PC 2) it gives the error:
Error 1 Error loading F0005.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. z:visual studio 2005projectssales data martextract to staging areaF0005.dtsx 1 1
We are using environment variable named DWConfig and have configured correct path in each PC. If we edit package configuration in PC 2 and go thru the same procedures without any amendments the errors is removed for that PC and if, again we OPEN that project in PC 1 it gives same error and if we go thru package configuration wizard again error is removed.
Can any one tell me is there any solution of that problem?
Note: Our project is saved on server (neither PC 1 nor PC 2)
I have a package that uses an environment variable package configuration of value X for a connection string. I close BIDS. I change the value of the environment variable to value Y. I open BIDS and the package, and the value of my connection string is Y. I save my package with the new configuration. if I look at the dtsx file, I see connection string with value Y. All as expected.
I move the package to my server (I've tried Import package from SSMS, using the deployment manifest, and save copy as). On the server, the environment variable is set to value Y. If I run the package or export it; however, the value of my connection string is X!
Does anyone have any suggestions of things to try or some reason that this is not working?
I have a master package which calls two package one after an other.
1) I will be passing parameters to master package using dtexec with /set property to set the variables in the master package. I would like to know how can child package access these variables.
Description: The package path referenced an object that cannot be found: "Package.Variables[User::<variable_name>].Properties[Value]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. End Warning Could not load package "<package_name>" because of error 0xC0010014.
Basically, I create a package variable under my User Namespace and this variable will tell what server the SSIS is running at. We first create a system variable locally and the SQL Server will have a variable with exactly the same name so that the server name will be evaluated through the package variable/package configurations when the SSIS is executing from a SQL Server job.
This way we do not hard code the server name... We always succeeded on doing that with DTS as well as SSIS packages but just now my package is running into this issue...
Since I did not change ANYTHING in the package, I am guessing this is not programming related and that something was changed in the server. However, the DBA was helpless over here and I have no clue of what this error means.
I have declared a variable XYZ in Parent package Similarly I have declared ABC in Child package and have done the configuration. I have assigned some value to XYZ How to get the value in Child Package.
We're attempting set the ConnectionString for our configuration database connection manager from an environment variable, but SSIS seems to ignore the environment variable value. Deployment process:
Create the Connection Manager
Create an Environment Variable type configuration with the Target Property: Package.Connections[acConfigDBManager].Properties[ConnectionString]
Build the package
Copy the package and the manifest from the project's inDeployment folder to a folder on the server
From the SSIS server's console, Import the package from the File System.
Run the package, after first inspecting the ConnectionString in the Connection Managers collection In all cases, the ConnectionString variable for our configuration database is the value in the package at build time.
So far we've tried the following variations:
confirmed that the spelling and case of the environment variable is identical on the XP development computer and the Win2003 server.
restarted SQL Server and SSIS
rebooted the Win2003 server.
built the package with a blank ConnectionString value in the connection manager
re-imported the package, overwriting the old one
deleted the old package before reimporting
renamed the package and imported
run imports from SQL Management Studio from the server console
assign the connection string from an arbitrary system environment variable
assign the ServerName and use an expression to build ConnectionString What now? Has anyone been able to set a Connection Manager property from an environment variable?
I have a package variable that is a datetime... how am I able to set the time of that variable? In visual studio on the variables screen if I click on value it brings up a calendar control - I can't seem to edit the time portion of the variable.
When I first click open the variables window it will say 9/1/2006 12:00:00 AM - but as soon as I click on the value box the 12:00:00 AM part will disappear and I can't edit it. I've tried on someone elses PC as well to make sure there isn't something wrong with my visual studio
Hello, I have a package variable called LatestTableFileDate that is typed to DateTime. I have an Execute SQL Task that executes the following SQL statement against an MS Access 2000 database table through the OLEDB Jet provider;
SELECT MAX(FileDate) AS MaxFileDate FROM CollectionData_Access
The statement parses fine. At the moment, the returned value is NULL. I have 'Result Set' set to 'Single Row'. The FileDate column in the Access table is 'Date/Time', and the Format is 'Short Date'.
In the result set properties, I attempt to set the result to the LatestTableFileDate user variable. When I run the task, the task fails, and the following appears on the 'Progress' tab;
[Execute SQL Task] Error: An error occurred while assigning a value to variable "LatestTableFileDate": "Unsupported data type on result set binding 0.".
I searched the forum for this problem, and didn't find anything. Do I need to convert the date in MS Access to a string and set the package variable to a string type, or is there some other way to handle this?
I have a parent package (i.e. P) and a child package (C). I was able to easily pass a variable value from P to C. And C did computations and get a result (i.e. integer) and stored this integer to a variable (x).
Now I want the value of "x" to be passed back to P and store in P's variable (say Px). Anyone knows how to ?
Am I correct in thinking that I need to place a dummy script component which does nothing in order to route logic depending on the value of a variable?
ie. I want to output to a file if a Parent variable is a certain value, or i want to load a table if its a different value.
How can I specify the target object when trying to set a parent variable package configuration programatically?
I am trying to set the Target Object to be "ParentLogID" and the Target Property to be it's "Value". I think this is how I'd add the configuration and set the source.
Is there any way to change variable scope while using package templates?
I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.
