SSIS Hangs On .Bat File
Mar 30, 2007
I am encountering a weird issue with SSIS.
I have a very simple SSIS package that executes a .bat file. Here's
the
actual file it executes:
@Echo Off
c:
F:
cd ReportingServicesScripts
rs -i NoteBlankSnapshots.rss -S http://10.90.160.13/ReportServerTest
EXIT
The .bat file executes a Reporting Services .rss script using the
rs.exe
utility. When I run the command from the http://10.90.160.13/ReportServerTest
EXIT
The .bat file executes a Reporting Services .rss script using the
rs.exe
utility. When I run the command from the command prompt, it runs just
fine.
When I execute the .bat file manually, it runs and completes. When I
execute
the actual package, it also completes. But when I schedule the package
as a
job, it just hangs...it never finishes.
The owner of the job is an administrator in SQL Server. I have the
SQL
Server Agent configured to interact with the desktop - although
my .bat file
requires no input from the user.
I've created other jobs that just execute plain old SQL using the
same
owneer and these jobs complete just fine.
What am I missing?
Any help would be appreciated!
Thanks!!
__________________
Anthony Robinson
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."
View 11 Replies
ADVERTISEMENT
Oct 12, 2014
I have an SSIS package that inserts website URLs from a SQLServer table into a variable used by an HTTP Connection Manager, then downloads the data files from those URLs using a ForEach Loop and a Script Task. Works beautifully when a data file is found at the URL, but hangs if no data file is found. I've set the Timeout property on the HTTP Connection Manager to 30 seconds, but doesn't work. how to first check if a data file exists, of if the request returns nothing, or how to trap this situation in a try-catch?
Here is the VB code I'm using in the Script Task:
Public Sub Main()
Try
' Connect to website using HTTP connection manager
Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
' Create a new HTTP client connection
Dim connection As New HttpClientConnection(nativeObject)
[code].....
View 0 Replies
View Related
Nov 28, 2007
Hi,
I have a package that runs just fine from BIDS. There are no message boxes in any scripts, or prompts of any kind.
However, when I attempt to the exact same package from a job, it just hangs forever.
Is there an obvious explanation for this?
Thanks
View 11 Replies
View Related
Mar 29, 2007
I am encountering a weird issue with SSIS.
I have a very simple SSIS package that executes a .bat file. Here's
the
actual file it executes:
@Echo Off
c:
F:
cd ReportingServicesScripts
rs -i NoteBlankSnapshots.rss -S http://10.90.160.13/ReportServerTest
EXIT
The .bat file executes a Reporting Services .rss script using the
rs.exe
utility. When I run the command from the command prompt, it runs just
fine.
When I execute the .bat file manually, it runs and completes. When I
execute
the actual package, it also completes. But when I schedule the package
as a
job, it just hangs...it never finishes.
The owner of the job is an administrator in SQL Server. I have the
SQL
Server Agent configured to interact with the desktop - although
my .bat file
requires no input from the user.
I've created other jobs that just execute plain old SQL using the
same
owneer and these jobs complete just fine.
What am I missing?
Any help would be appreciated!
Thanks!!
View 3 Replies
View Related
May 23, 2008
I have a stored procedure which when executed from management studio completes successfully.
If I invoke the stored procedure from SSIS which in turn is called from a SQL Server job the jobs runs indefnitely.
Now I wanted to see how far did it go in the execution of sp and I had some logging done at various places in stored procedure....It came till the RETURN 0 , the last statement of sp. The call to the SP is the last task in the SSIS package...So if the sp is alsmost completed and execute sql task is the last one in SSi, why is the job running indefinitely.
Please help....MVPs....
View 1 Replies
View Related
Oct 9, 2007
Hello everyone,
I am having an odd behavior with an SSIS package that transfers data from one database to another. It seems to me that with some of the larger databases the services just stops working (0% utilization) when it is almost done. I don't get errors and the job stays in the "executing" state.
This doesn't happen always, but it will happen once the package is ran many times over the course of the week, I was wondering if you guys could point me in a direction on how I can find out the source of these hangs. Right now I am leaning towards it being a memory issue (or other misc. hardware problems) but I would like to rule out that it is software first.
Much help is appretiated.
Thank you
Jaime
View 13 Replies
View Related
Aug 7, 2006
I have two SSIS packages that I want to run in one SQL Agent job as two individual steps. The two packages run fine when they are in separate jobs. However, when I run the job conaining both SSIS packages (under the same proxy), the first SSIS package starts, but hangs in the middle.
I then tried setting the DelayValidation flag to True as suggested for a similar issue in another thread from this forum. After changing the DelayValidation flag to True for all containers and tasks on the second SSIS package, the first SSIS package ran completely through sucessfully, but the job continued executing for hours and the second SSIS package never started. I finally killed the job.
Any ideas as to what is the problem here? I have logged to the event viewer and see that the first package completes sucessfully. They run successfully in separate jobs, but I can not get them to run together within the same job without hanging.
Any help is appreciated,
Paulette
View 5 Replies
View Related
Oct 19, 2007
I have a number of packages that I have moved from an old server. Each package was scheduled with a SQL Agent job. On the old server everything ran fine. All of the packages run fine from VS, from DTEXECUI and I have tried one from the command line with DTEXEC and it worked.
When I run from the SQL Agent job, I don't get a failure, the package just hangs. I let one of the agent jobs sit for an hour with no progress. The package typically takes about 15 minutes to complete.
Below is the output from my package log up to the point that it hangs:
#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnInformation,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,1074016266,0x,Validation phase is beginning.
OnProgress,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,0,0x,Validating
OnProgress,SIC0AD12,NT AUTHORITYNETWORK SERVICE,Item Pool,{0E40BC3D-A3E3-406A-B80B-D5989C49A589},{94F0C620-67CA-4DB7-8E68-F562ABC1450C},10/19/2007 4:23:24 PM,10/19/2007 4:23:24 PM,50,0x,Validating
I've been reading all of the posts concerning using a Proxy. I tried to set that up and got the same results.
Any help would be greatly appreciated.
View 4 Replies
View Related
Apr 29, 2015
I created a SSIS package which loads CSV into database. The package is called in a C# console application which is set as scheduled task in a server. The problem I having is that the package hangs during validation stage: "Truncation may occur due to inserting data from data flow column "Reading Type" with a length of 50 to database column "ReadingType" with a length of 2."There is no problem loading same data from development machine.
View 5 Replies
View Related
Mar 2, 2006
Hi, we are writting a SQL Server Integration Services package to import data from a MySql database to a Sql Database.
We are using ODBC 3.51 Drivers to connect to the MySql Database in SSIS. The package runs perfectly in design mode. When we schedule the package to run, the package seems to hang about 1/3 of the times.
What can this be. We used to the package and ran it from a Sql Server 2005 to a Sql Server 2005 database. This setup works perfectly. When i'm doing MySql to Sql Server 2005, 1/3 of the times, it does not work.
Can anyone help me with this?
Giovan Gentile
Ayuda Media Systems
View 5 Replies
View Related
Jan 19, 2007
When I push my SSIS packages up to my production server (which has a different data source than my developement environment) and I try to open the package on the production server, it takes forever for to validate all the steps of the SSIS package because it's trying to validate against a datasource that isnt there, so it just waits for each element it's validating to time out. This is exceptionally annoying.
Is there a way to turn off this validation 'feature'?
View 14 Replies
View Related
Mar 8, 2007
Hi there,
after we've upgraded to SP2 my flat-file-source component hangs in the validation phase.
normally at 50% or 66%.
i see no activity what-so-ever cpu-wise/memory-wise.
have no idea how to fix it.
(sorry if i am posting this twice, couldn't see my previous post)
View 1 Replies
View Related
Feb 10, 2006
i m using the hands-on labs for SQL server 2005.
Step 6 age 14 i went thro and it make my SQL server 2005 hangs when i try to use the flat file destination.
View 1 Replies
View Related
Jun 16, 2015
I've built an SSIS package in SSDT 2014. The package was running successfully
When I close and open the package it hangs on validation of single task out of all.
OLEDB DESTINATION Task
I have tried several times to close the SSDT and open but facing same issue.
These seems like bugs in SSIS/SSDT. What would cause the relocation of some script code to hang the validation process like this?
View 2 Replies
View Related
Nov 2, 2006
We're experiencing a problem where intermittently our SSIS packages will hang. There are no log errors or events in the event viewer. It will happen whether the package is executed from the SQL Job Agent or run from BIDs. When running from BIDs it appears to hang inside one of the data flows (several parallel pipes with sorts, merge joins etc...). It appears to hang in multiple pipes within the data flow component. The problem is reproducable, we just kill it and re-run, and it appears to hang in the same places.
Now here's the odd thing: as we simply open and close some of the components in the pipe line after the place it hangs, a subsequent run will go further in the pipeline before hanging. If we open and close all the components after the point it initially hung, the data flow will run fine, from there on out. When I say "open and close" I mean no changes are made, we simply double-click the component, like a merge join, then click 'close.'
To me this does not seem like a memory problem but likely something is wrong with the metadata, where opening a component and closing it somehow alters the metadata to "right it".
This seems to occur intermittently after we make modifications to the package. It's like if you make any mod, even unrelated to the data flow, you then have to go through and open and close every component in your package to ensure it will work. Again, no errors or warnings are fired.
Has anyone seen this type of problem?
View 10 Replies
View Related
May 4, 2007
Help!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
View 4 Replies
View Related
Dec 11, 2007
I'm on SSMS under Management --> Maintenance Plans --> (my transaction log backup plan)
I right click and choose "View History" at which point the log file viewer window opens and it just sits there... with the progress circle runnning just saying "Initialize Log #2..."
After roughly 5 to 10 minutes it returns results.
Any suggestions on how I should resolve this or what to trouble shoot?
Thank you.
View 6 Replies
View Related
May 13, 2008
Hello Experts,
I am createing one task (user control) in SSIS. I have property grid in my GUI and 2 buttons (OK & Cancle).
PropertyGrid has Properties like SourceConnection, OutputConnection etc....right now I am able to populate Connections in list box next to Source and Output Property.
Now my question to you guys is depending on Source Connection it should read that text file associated with connection manager. After validation it should pick header (first line of text file bases on record type) and write it into new file when task is executed. I have following code for your reference. Please let me know I am going in right direction or not..
What should go here ?
->Under Class A
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
//Some code to read file and write it into new file
return DTSExecResult.Success;
}
public const string Property_Task = "CustomErrorControl";
public const string Property_SourceConnection = "SourceConnection";
public void LoadFromXML(XmlElement node, IDTSInfoEvents infoEvents)
{
if (node.Name != Property_Task)
{
throw new Exception(String.Format("Invalid task element '{0}' in LoadFromXML.", node.Name));
}
else
{
try
{
_sourceConnectionId = node.Attributes.GetNamedItem(Property_SourceConnection).Value;
}
catch (Exception ex)
{
infoEvents.FireError(0, "LoadFromXML", ex.Message, "", 0);
}
}
}
public void SaveToXML(XmlDocument doc, IDTSInfoEvents infoEvents)
{
try
{
// // Create Task Element
XmlElement taskElement = doc.CreateElement("", Property_Task, "");
doc.AppendChild(taskElement);
// // Save source FileConnection
XmlAttribute sourcefileAttribute = doc.CreateAttribute(Property_SourceConnection);
sourcefileAttribute.Value = _sourceConnectionId;
taskElement.Attributes.Append(sourcefileAttribute);
}
catch (Exception ex)
{
infoEvents.FireError(0, "SaveXML", ex.Message, "", 0);
}
}
In UI Class there is OK Click event.
private void btnOK_Click(object sender, EventArgs e)
{
try
{
_taskHost.Properties[CustomErrorControl.Property_SourceConnection].SetValue(_taskHost, propertyGrid1.Text);
btnOK.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
#endregion
}
View 10 Replies
View Related
May 2, 2007
I want to enumerate all *.xls and *.csv file. How to fill the Files box? I tried
*.xls, *.csv
*.xls *.csv
*.(xls|cvs)
all doesn't work
View 4 Replies
View Related
Oct 24, 2007
Hi all,
In a foreachloop, I am inserting records into a flat file which is working fine. But the thing is that as the file grows, it takes longer for it to locate the EOF(End of File) of the flat file so as to insert the records.
I have around 70-100 lines written to the file at each loop and there are more than 20k records to be looped. wihich means that at the end I should be having 1400k - 20000k line in the text file.
One solution would be to insert the records at the start of the file itself so that it does not has to lookup the EOF each time before writting.
Another would be to generate separate files and then merge it.
Any idea how can this can be done?
Beside this I have to zip the file and then SFTP to a given address.
Any suggestion or help would be welcome.
Rdgs
David
View 5 Replies
View Related
Jun 19, 2015
Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
View 0 Replies
View Related
Dec 4, 2014
I am trying to create and later read a data file from a package deployed in SSISDB, but it is not reading it while it is successfully creating the file. The same package when run from the file system package, runs successfully. Generating ispac and deploying in SSISDB is running for infinite time. Is it a permission issue?
View 7 Replies
View Related
Aug 1, 2007
I need to build an asp.net/C# application to read values from an Excel spreadsheet. Once the values are read from the spreadsheet, the C# code will do some elementary statistics on the values read. Then the values read and their computations will be written to a sql server database.
My manager suggested that SSIS might be a good candidate technology for doing this type of work. Does that sound correct? My only hesitation with using SSIS is that I want to keep the application as simple as possible, so that the code can be more portable. Maybe might argument is not a good one, but maybe someone can help me out here.
Ralph
View 1 Replies
View Related
Jan 4, 2008
Dear Friends,
I store several configurations in the main database of my SSIS packages. I need to get the servername from a xml or txt file in order to get those configurations stored in my database.
How you think is the better way to do that?
Using a FlatFileSource to read the file and a script to save the value into a SSIS variable?
Using the package configuration I cant do that... maybe I dont know, but I can save the SSIS variale in the configuration file, but what I need is to do the inverse, read the configuration file and save the value in the SSIS variable.
How the best way you suggest?!
Regards!!
Thanks.
View 18 Replies
View Related
Oct 1, 2006
Hi,
I am pulling text files in gzip format from UNIX system. I want to unzip these files and then import data from these files into database using SSIS.
View 15 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Jul 14, 2015
Is there anyway to send excel file from ssis using send mail task without saving the excel file locally. I need to automate the process which involves loading the excel file from the database and send it to some people.
View 6 Replies
View Related
Jan 30, 2008
Originally in my DTS, the logfilename property is assign by a variable in a dynamic properties task.
After migrate, it appear commented in the script task. So may i know how can i assign the logfilename with the variable value?
Please advice, thank you.
View 2 Replies
View Related
May 29, 2007
Hello,
Can we keep a database connection information in .ds (datasource) files. Is it possible to use these instead of the standard approach of putting the connection string in the configuration file.
Furthermore, when they run the SSIS package from the command line (using DTEXEC), is it possible to pass the .ds file as a parameter?
View 4 Replies
View Related
Apr 22, 2008
Hi,
I am using an XML source component to integrate it into an SQL server data base.My problem is that when I try to open my data flow containing the XML source component, it take much time to open it, so I don't know why beacause I have a very good machine with 2GO of RAM. is it a problem of SSIS with XML files?
thank you in advance
View 8 Replies
View Related
Sep 26, 2006
I want to rename a file that is a variable to a fixed file name in a specific folder in SQL Server System Integration Services using the File System Task Editor. Example: File1.txt to Users.txt or File2 to Users.txt. The source file is a variable the destination file is not. Here is an example of the File System Task EditorHOW DO I SPECIFY A FIXED OR VARIABLE FOLDER NAME WITH A SPECIFIC FILE NAME?????????File System Task EditorProperties ValueDestination ConnectionIsDestinationPathVariable TrueDestinationVariable User::ArchivePathOverWriteDestination TrueGeneralName Rename a fileDescription File System TaskOperationOperation Rename fileSource ConnectionIsSourcePathVariable TrueSourceVariable User::ImportFilePath Thanks
View 1 Replies
View Related
Apr 3, 2007
We are deploying our SSIS packages into different folders. For example:
Test1
est2
production
Test1 points to the Test1 database, etc.
So, I configured my SSIS package to use a database connection called dbMAIN.
I then setup the SSIS File Configuration (XML) so that "dbMain" points to Test1. This xml file, called Global.dtsConfig, sits in C: est1, the same place as my ssis.dtsx file.
I want to copy my dtsx file and my dtsconfig file into est2 and production. The problem is that the the location of dtsconfig is HARDCODED in the ssis package!
How do I dynamically change it?
I tried to hardcode the location to ".Global.dtsconfig", but that did not work! Please advise!
View 1 Replies
View Related
May 28, 2013
have a URL that I get from a partner by calling another URL and feeding it some query parameters. That call returns to me a URL which is what I need to then go get the file I'm downloading. I have no problem getting that URL into a variable in my SSIS package. URL....there are several key-value pairs after that question mark which denote the login information, the report ID that it's picking up, etc.
If I manually take that URL and dump it into IE and hit enter, I get the standard "Open/Save/Cancel" prompt. If I open it, I see the XML that should be in my file in the browser. If I save it, I get the standard save dialoge box so that I can save the xml file.How do I do this in SSIS? I can create a file connection for where I want this XML file to be saved, but I don't see an option in the XML task to simply save the file from a URL.
View 2 Replies
View Related
