Scanning Many SSIS Packages For A Particular Property?
Jan 14, 2015
I have a pile of around 50 packages (*.dtsx files) or so, and we have found that not all of these packages are set to have the critical bits fail the package on failure of the critical bits. This gets to be slightly annoying when there is a job of some 20 or 30 packages running, and one of these fails and the rest of the job runs along as if nothing (bad) happened. This leads to having to hunt down what failed, diagnosing why it failed, and then re-running the parts that failed in different and inventive ways after the unhandled exception.
So far, I have broken open a couple of the offending SSIS packages in notepad, and the string "FailPackageOnFailure" appears in a number of places. Unfortunately, due to the fact that some of the packages have Sequence Containers that hold some of the SQLTasks (all of the "critical bits" are SQL Tasks, I believe), and this changes the XML path to the parts that I want to scan. This leaves me with the prospect of opening each package, wait for the package to be validated, going to the SQL Task in the package, right clicking for the properties of said SQL Task, and finding the Fail Package on Failure bit (OK, more likely I will delegate that task to some poor schmuck, but I promise I will feel some regret over it).
View 3 Replies
ADVERTISEMENT
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
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
Aug 1, 2007
Hi guys,
We have a new project that involves Finger Scanning. We have the device for the scanning but we need to develop our own interface and database using MS SQL Server.
What kind of data type are we going to use in order to save the Finger-scanned-image ?
How will I search the input finger-scanned ?
Thank you very much.
View 4 Replies
View Related
Aug 11, 2006
Hi all. Have a query and i'm really not sure how to approah it.
OK, I have two tables. In the first table (ABSENCE) i have three fields FROMDATE, UNTILDATE and TOTAL HOURS.
These field state the start and end date of an absence.
I have a second table (WORKPATTERN) in this table are stored etails of an individual workpattern.
It contains two fields DATE and HOURS. It stores each individual date in a workpattern and how many hours are worked on that day.
Obviously there are days missed in the work pattern (I.E. saturday, sunday and bank holidays etc).
What i need to do is work out the TOTALHOURS for a particlar absence. This means i need scan the range of FROMDATE to UNTILDATE.
If there is a matching date in the WORKPATTNT table i need to add the HOURS for that record to running total.
Not sure if ive explained it all that well. But any advice would be smashing. Thanks people.
View 3 Replies
View Related
Mar 6, 2007
Hi all. I have a problem on my query. let me explain first the scenario.
I have a windows application project that views a report through report viewer(Report of all employees that have a religion of adventist, born again, catholic, baptist, etc....). In my windows application, I have checkboxes for a specific religion. So, if I checked baptist and catholic, report generated is all employee that has religion of baptist and catholic.
My concern is, How could i make a query of this?
sample tables.
table - employee
dcno name religion
1 jon baptist
2 rose catholic
3 joy baptist
4 mike adventist
5 soy born again
6 rich protestant
you can supply additional table if needed.
thanks
-Ron-
View 7 Replies
View Related
Mar 12, 2006
Hi,When we make a search for a table in SQL Server 2000sysindexes table is searched and indid field is checked,If it's 0 we go for full table scan (No Indexes Present)If it's 1 it means clustured index column is used for search.2-254 means non-clustred indexes used.Thank you very much for taking time and replying.Thank you,Miks
View 1 Replies
View Related
Sep 5, 2006
To make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with CheckpointRestart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.
Is there a way to control the defaults that the SSIS IDE uses?
I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.
View 5 Replies
View Related
May 1, 2006
Everytime when I set this value to true; it saves but when I close the package and reopen it. It returns to false, I am using OLE DB connection... Help Please
View 1 Replies
View Related
Oct 4, 2007
Hello All,
I am working for a company with approx. 5000 computers(Mix of Win2000+WinXP-PRO). I am asked to write some script or find some kind of tool or mehanism to scan the network to see how many PCs are running SQLServer ( Any version -- may be Desktop/Standard Edition). Is there any such way/script/tool anyone knows here . Please share your experience.
Thanks A lot.
R
View 10 Replies
View Related
Mar 24, 2007
Can the WaitForMe Task property be used for building eventing based tasks (that is, "tasks with no defined execution lifecycle"), as Haselden's IS book terms it?
The SSIS run-time would appear to never interrogate this property.
I tried setting a breakpoint in a custom task on the WaitForMe
property. The breakpoint was never hit at run-time. Design-time only. It seems that the run-time ignores this property.
The documentation states that "On completion of any task, the runtime examines tasks that are still running in
the container, and if any of these tasks have WaitForMe set to
false, those tasks will be canceled." Well, the tasks with WaitForMe set to false are in fact not cancelled (e.g. System::CancelEvent is not signalled).
The intention of the WaitForMe Task property is to allow for event based tasks to not prevent a package (or other container) from finishing if the event never happens.
Tasks like the WMI Event Watcher, Message QueueTask set to receive, and third party tasks like file watchers, or service broker queue receivers would seem to fall in this category.
View 8 Replies
View Related
Jul 23, 2015
Where do you set an SSIS EvaluateAsExpression property of the variable to TRUE?
[URL]
View 3 Replies
View Related
Oct 26, 2006
SSIS data flow transformation - Lookup task - best practice concerning Cachetype
I would like to know if there's any best practice concerning the CacheType property for the the Lookup task. Default value is "Full", but if the SSIS package is working with at lot of data, i.e. +10 mill. records from the OLE DB source to be handled through a variated numbers of data flow transformation tasks, it must have an impact memory usage if the lookup table also is a large table, i.e. +8 mill. records? When should I consider turning the property value to "none"
View 1 Replies
View Related
May 29, 2007
I see various references to the options for a package's protection level (including http://msdn2.microsoft.com/en-us/library/ms141747.aspx) but I can't seem to find anything telling me how to actually look at and/or change the protection level of a package - thus my question is "How do I change the protection level of an SSIS package?"
Thanks!
- Lance
View 3 Replies
View Related
Feb 12, 2008
For those who want to know the root of my question without any of the detail:
I want to know if there is any recommended way to detect and remove potential malware from files uploaded to SQL 2005 Reporting Services via the Report Manager.
Here's a general description of our setup and challenge:
We have a Windows 2003/SQL 2005 Enterprise Server as the reporting database server and another server running Windows 2003/IIS 6.0/ColdFusion MX7/SQL 2005 Reporting Services as the web server. The Report Server is only used internally, but has a potential user community of approximately 150,000. The actual community of users who would have access to the Report Manager would be much smaller.
In our environment, we have no way of ensuring that virus scans are being performed at all of the clients, so we need a way to make sure that files uploaded through Report Manager are all being checked as they are coming in to the Report Server.
Here's what we've tried so far:
We downloaded the Eicar A/V test file and verified that Report Manager has no problem accepting it as an uploaded files by default. The servers are running Norton AntiVirus Corporate 10.2 with the latest definitions and it did not detect the virus as it was being loaded in to the database.
Next, I setup Microsoft ISA 2006 in reverse proxy mode with Kaspersky's Antivirus for ISA. Kaspersky does not prevent a client from uploading the test file to Report Manager, but it does prevent the user from downloading it, so it seems that it only works with HTTP get operations and ignores HTTP put operations.
I have had Watchguard come in with a Firebox (an appliance that they claimed could A/V scan files), but it provided similar results. Files uploaded to Report Manager were ignored, but infected files downloaded were blocked.
I have BlueCoat, TrendMicro, and CheckPoint all on tap to come in and see if they can fare any better, but I am not holding much hope for this.
I know that SharePoint has an API that virus scanners can use (VSAPI) for scanning binaries stored within its SQL database. Is there anything similar for SQL Reporting Services? Is there any other way of preventing these files from entering the application?
Thanks for reading and any responses. I am happy to provide more details if it would be helpful.
View 1 Replies
View Related
Jun 16, 2006
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
Can someone please help me resolve this.
Thanks much.
View 5 Replies
View Related
May 19, 2015
I have a main package calling another package through the Execute Package task.
The main package is passing the Job Instance id as a parameter to the other package.
When i execute the Execute Package task the concerned package is not showing any execution progress.However when i set the Delay validation Property to True , I saw that the package executed instantly and the desired result was obtained.
I am not sure how the Delay Validation property worked for the cause , as in my package I had no scenario of a temp table being called or any other temporary variables being used which needed a Delay Validation.
View 7 Replies
View Related
Oct 2, 2015
I am working with SQL Server 2012. I have deployed a SSIS project that has 2 packages in it. The package connection manager (Test) uses an expression to evaluate one of the Project parameter value (TestConnectionString) to set its ConnectionString property.
This works fine in a Dev environment. However when deployed to UAT, it keeps failing with the error:Â
PackageExport:Error: The result of the expression
"@[$Project::TestConnectionString]" on property
"Package.Connections[Test].Properties
[ConnectionString]" cannot be written to the property. The expression was evaluated, but cannot be set on the
property.
I can not seem to find what the issue could be. I have come across [URL] .... where it says: "If the package contains project parameters, the package execution may fail." but offers no solution.Â
View 4 Replies
View Related
Apr 17, 2008
Hi,
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Thanks for any hints.
View 3 Replies
View Related
Aug 26, 2015
I can set the propperty of the checkpoint file to a local drive, but not to a UNC path mapping, mapping to my host server. (loop back)
Example: "I:FILEFILE1$InputArchiveOntwikkel " is possible as checkpoint file property.
S11487O$InputArchiveOntwikkel is not possible, though this is the same folder on the local host.
For data source both unc path and drive mapping are allowed. Why this difference?
View 5 Replies
View Related
Jan 22, 2008
Hello,
I am having a hard time setting the executable path for an Execute Process Task in SSIS. I have a variable that is initialized at package statup which holds the path to an executable in Windows. When I set the property "Executable" Path in an expression, I get a warning that the path for the executable is not set. One workaround was to try and initialize the variable with a bogus path with the hopes that the "correct" value will be written on run-time. NO LUCK. I still get the error and I cannot run the package until I put a static path.
Does anyone have a clue as to what is going on??
Mike
View 1 Replies
View Related
May 7, 2008
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows
"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
View 10 Replies
View Related
Jul 6, 2007
HI,
I need to open a File through File connection manager and want to assign these file properties to SSIS precreated varibale or Newly created varibale. I want to show file properties in Propertygrid. Properties grid will conatin File Propeties Column and SSIS varibale Combobox column. The combo box will contain New variable field. When user select New Variable field, then a new SSIS varibale window will open and we can able create New variable and that Newly created variable should add to that property comboBox.
For Instance if we create a new varibale Name "Creationdate" by clicking on New Varible in ComboBox, then that CreationDate variable should add to Property ComboBox in PropertyGrid. After adding when we select that variable Name "Creationdate" then that selected file Creation date should assign to SSIS varibale "Creationdate" field.
Any Comments or sample will help me.
Nitin
View 2 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
Aug 24, 2006
I am writing a vb application that is supposed to let the users set the connection string for the datasources in the package. After new connection strings are entered the application is supposed to run 8 packages in a certain order, but I haven't been able to set a new connection string successfully. Is there a way to programmatically modify the connection string of a package's datasource? (the packages are moving data from a D3 database to sql server 2005)
Here is what I have tried so far:
A.
Dim pkgLocation As String
Dim app As Application = New Application()
pkgLocation = "c:Package1.dtsx"
Dim pkg As Package = app.LoadPackage(pkgLocation, Nothing)
Dim myConns As Connections = pkg.Connections
MessageBox.Show(myConns(0).ID.ToString)
Dim myConnMgr As ConnectionManager = myConns(0)
Dim connProperties As DtsProperties = myConnMgr.Properties
connProperties(0).SetValue(myConnMgr, "notsupposed=towork;")
MessageBox.Show(myConnMgr.ConnectionString)
B.
ConMgr = p.Connections.Add("OLEDB")
ConMgr.ConnectionString = "Provider=SQLNCLI.1;Data Source=192.168.0.233; User ID=sa; Initial Catalog=Rmt_New"
ConMgr.Name = "SqlDatabase"
ConMgr.Description = "Sql Connection"
Any help would appreciated.
View 1 Replies
View Related
Feb 28, 2006
I am connecting to a DB2 mainframe to pull data into SQL 2005. Very simple import. SSIS package works fine on 32 bit. However, once deployed to the 64 bit machine, I get "invalid product license" on the Acquire Connection method.
I've worked with IBM support. I have the correct version of the DB2 Connect client installed. The license is there and in the right place. I can connect to the mainframe from the 64 bit server using the DB2 client tools. I just can't seem to execute the package from Integration Services or run a job in SQL Server that executes the package.
According to BOL, the package should automatically detect the 64 client I installed. It and the 32 bit client I developed with share the same name/id.
Am I missing something? Any hints?
View 3 Replies
View Related
Feb 7, 2007
Where are SSIS packages stored and how can I see them in SQL Server Management studio?
View 6 Replies
View Related
Sep 15, 2006
I read in Kirk Haselden's book "Microsoft SQL Server 2005 Integration Services" that if SQL Serfver 2005 and 2000 are installed on the same machine as seperate instances then you can view the SQL Server 2000 DTS packages in 2005 Management Studio under the Management tree, Legacy, Data Transformation Services node.
But in my case, I am not able to see DTS packages in Management Studio. Is there a property or a setting that we need to configure for that?
Thanks.
View 2 Replies
View Related
Jan 17, 2007
I'm still new to SSIS packages and I'm NOT a developer. I am in the process of doing preliminary/prepatory work for migrating our SQL 2000 platforms to SQL 2005.
I am having a REAL headache with migrating/moving DTS packages from SQL 2000 to SQL 2005. Here are things that I know :
1. I know that some packages cannot be migrated due to ActiveX issues and other issues. Fine.
2. I know that I can install DTS backwards compatibility components on the server in order to be able to edit the DTS packages using a SQL 2000 DTS GUI. Fine.
3. I know that I can use the Migration wizard to migrate packages (and that some of them can't be migrated this way). Fine.
Here's what I don't know/or am conjecturing:
1. In a clustered environment, I have to edit the <%Install Path%>/90/DTS/Bin/MsDtsSrvr.ini.xml file to set the <ServerName> property to the Virtual Server name. Correct? Why can't M$ do this for me?
2. Do I HAVE to export the SSIS package to a .DTSX file in order to be able to edit it with Visual Studio? Is there ANY way around this?
3. If I am running in a clustered environment and I use the File System for storing packages, then the pacakges must be stored on a shared volume, right?
4. I did not find SQL Server Integration services on the B- (Passive) node. Do I have to install it separately onto the B server (much like having to install the Client Tools)?
If anyone has some guidance or tips on running SSIS in this brave, new, wonderful world, I would sure appreciate it.
And yes, I am going to go out right now and order a new book on SSIS.
Regards,
hmscott
View 3 Replies
View Related
