.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS Hangs on .Bat file

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
I am encountering a weird issue with SSIS.

I have a very simple SSIS package that executes a .bat file. Here's
actual file it executes:

@Echo Off
cd ReportingServicesScripts
rs -i NoteBlankSnapshots.rss -S

The .bat file executes a Reporting Services .rss script using the
utility. When I run the command from the

The .bat file executes a Reporting Services .rss script using the
utility. When I run the command from the command prompt, it runs just
When I execute the .bat file manually, it runs and completes. When I
the actual package, it also completes. But when I schedule the package
as a

View Complete Post

More Related Resource Links

Problems running a bat file within a SSIS package

I have a ssis package which has 2 tasks, first it builds an excel sheet with data on the server from where I am running the ssis pkg and the second task (a sql process task), runs a bat. the bat file that basically copies the  excel sheet created from the first step to another server. Problem is that the ssis pkg runs succesful when i run it from BIDS, bust when I run is as ajon, it gets hung. It completes the first step, it is the sescond step that is get hung. I noticed that when i run from BIDs, it does come up with a window open file - security warning and says the publisher could not be verified, are you sure you want to run this software. why does it do that ??? the file I am asking to run is a .bat file. it should automatically use the cmd.exe app to run the bat file. Please help !!!   Thanks in advance.

SSIS multiple data flows accessing the same raw file

I have multiple data flow tasks accessing the same raw file at virtually the same time.  I would like to know if the data flow tasks lock the raw file during read?  Can multiple data flow tasks read the same raw file at the same time.  I'm currently developing with a very small data set, but I would like to know if I should be concerned with the possibility of the data flow tasks competing for access to the same raw file. Thanks.

SSIS 2005 - Foreach loop container - Stopping the loop after processing X number of file?

I need to stop the Foreach loop container from processing more files in the folder than desired. Scenario:  If I only want 1 files processed, i need to stop the loop after it finds 1 file.  No matter what the contraints or tests or variables I set, the loop processes all the files in the folder.  This is bad because I need the value of the 1st mapped variable and not the last one it finds. I have tried counting records and setting variables used in the contraints but to no avail  Nothing seems to stop the loop. Please advise

SSIS throws error when it doesn't find file

Hello All, I am facing one issue that i dont know in advance if there is file on FTP or not. I scheduled a job which runs every hour which in turn calling SSIS package. Now, when there is file on FTP, it works fine. But, when there is no file on FTP then itthrows error which i want to handle. Is it possible to check for any files present on FTP? Cheers, Pranav

Saving SSIS results to Log or text file using dtexec

Hi, How to save the SSIS package results to log file using dtexec command............please help regaridng this...........   Thanks in advance,

SSIS FOR each loop to load 1 file after other into database for particaular folder

HI I am using ssis 2008,i have folder in which i have 10 excel file.i wont to load them one after other in data  base I have created a Dataflow task that load one file into database.i have inserted this dataflow task into For each loop and in For each loop i have give that folder path and *.XLSX  and in variable mapping i have created one variable  which i have passed in Database destination connecion string (properties->expression) but still i get error "cannot acquire connection from connection manager" please guide me or provide me with some reference so that i cud load file from folder into database one after other

SSIS - XML Source to SQL Server Destination. How to handle new tags in xml file?

Hi, I've created a SSIS package, which reads my XML file using XML Source, using SCD(Slowly Changing Dimension) to identify whether to do an Insert/Update and everything's fine so far. But there are chances that my xml file will have new tags added apart from what are already there. So In that case, i'm not able to figure out whether to alter the table to add new column or something like that. Need your suggestion. Thanks in advance Vijay

ssis 2008 Loading MULTIPLE EXCEL file

HI it is possible for me to load multiple text file using for each loop,but if i try the same process for EXCEL file then my excel source throws an error. Also when i created my variable in value  i have given the path of my excel file ,still it didnt help me..because the moment i map the variable with the connection string of source excel connnection(property->expression->connection string) it start giving error if you could help me or provide some reference it will be of great help http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html i tried this link but got stuck with it on step 12 and 14 (below  is my connection string) and variable is @Filename which i have created Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mushtaq.sheikh\Desktop\ForEach\New Microsoft Excel Worksheet1.xlsx;Extended Properties="Excel 12.0;HDR=YES"; The moment i try to edit my connection string by comparing connection string provided in link it gives error(an OLEBD error has occured with error code 0x80040E73) on excel source Thanks in advance

Read Binary Data which is nothing but a Zip file and unzip through SSIS 2005 SP2

Hi ALL, I need some help in developing a task. I have a source database which is Oracle and it has a ZIP file stored inside the database in Binary format. When I move this data into the sql server 2005 database I get the data as binary data. Now the task begins with SSIS, I need to read the binary data which gives us a zip file and then unzip this zip file and read the XML data which is present inside the Zip file. I beleive some one might have already developed this task can you share the solution with us. Note: As this has to be moved into production I dont have permission to use third party tools like Cozy roc or install winrar.exe and simpy calling this exe from the execute process task in SSIS.  Raju

if ssis package writes to excel file that might be open by a user

I have written a package that writes to an excel file to a network folder. Eventually this package will run as a job. What is the best way to handle the situation where someone opens the file or inadvertantly leaving it open while the package is running and and stop the package.? No one needs to modify or write anything to this file after it is finished

How to redirect bad date(xxx,9999) in datereceived column in text file in ssis

I want to redirect bad date format from flate file source to log table. I tried with redirect row but it is not working. Thanks in advance

Import a flat file with combined data into separate SQL tables using SSIS

I have a flat text file (comma delimited) that is essentially multiple files, each with its own format, combined into one file. The file is coming from an external software vendor so unfortunately we don't have much choice but to work with what we are receiving. Here is an example of what the file could look like: Customer Data CustID,FName,LName,PhNum,Email 12345,John,Smith,,jsmith@gmail.com 12346,Jane,Doe,8001111111,jdoe@hotmail.com Customer Plan CustID,PlanType,PlanName,PlanStart 12345,0,Plan1,01/01/2010 12345,2,PlanVis,01/01/2010 12346,3,PlanLf,04/01/2010 12346,0,Plan1,01/01/2010 Customer Payment CustID,LastPayment,Amount 12345,09/01/2010,100.00 12346,05/01/2010,50.00 There is an empty line between each 'section' of data. I adapted a VB script I found online that can take the incoming file and save off each section as its own file so that each one can be separately imported, but this seems inefficient. I'm really new to SSIS in general, but it seems like it shouldn't be that difficult to take the data, split it where there is an empty line, and then import each section into the appropriate SQL table. Any ideas would be most welcome. Thanks!  

Batch file for use with SSIS

Greetings all, I'm trying to run the following batch file outside of SSIS.     DTEXEC /f "M:\Tim\SSIS\ImprtInvestOneNAVsGeneric.dtsx" /conf "M:\Tim\SSIS\EPIC.dtsConfig"      \Package.Variables[User::FilePath].Value;"M:\Tim\SSIS" /SET      \Package.Variables[User::SourceFileName].Value;"DIREXION_NAV.CSV" /SET         \Package.Variables[User::DoneFileName].Value;"BIAUSC50.DONE" /SET      \Package.Variables[User::SponsorID].Value;"56" /SET      \Package.Variables[User::FileDescription].Value;"Direxion NAVs (USA)" /SET      \Package.Variables[User::DBConn].Value;"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=M:\Tim\New             Basket DB\EPICUDT_Development.mdb;User Id=admin;Password=;" /SET  The purpose of this batch file is to allow our team to use the same SSIS package but with multiple file inputs. I am getting the following errors:   - Requires a higher level edition.  I've done some research on this and have found that you must have SSIS installed locallly (which I         do, the services indeed running).  I've also come across a 'fix' that says you can use the E

SSIS For Each loop with data flow hangs on DTC (SID -2) when inside transaction

The short story: I have a data flow inside a For Each container that is inside a sequence container.  A transaction is established at the sequence container, with Isolation Level Serializable.  The For Each container is limited to execute only 4 iterations.  The data flow iterates once fine, but the second iteration hangs.  The Activity Monitor shows the process is waiting on a another process with a SID of -2.  From other research in the past, I have found that this is an internal process dedicated to DTC (Distributed Transaction Coordinator). Environment: Microsoft SQL Server 2005 (SP3) on Windows 2008 R2 Standard.  I'm using SQL Server BIDS 2005 for building these packages.  I also have SQL Server 2008 installed and the databases are installed there. The details: The data flow merges data from the same table in two databases, source and target, using a Merge Join component.  It then performs a few transformations and lookups from other tables in the target DB to get IDs.  Then it inserts new records into the target DB or updates existing records in the target DB.  The two source tables are using OLE DB Source components with customized queries.  The insert target component is an OLE DB Destination and the update target component is an OLE DB Command.  In this particular scenario,

SSIS package - change Web reference's addres from Configuration file?

Greetings. The SSIS packages provide very nice options to configure them from an XML file, which allows to change credentials, database names etc w/o editing the package. This lets me, say, transfer package from one server to another or from test to production w/o any hassle - just changing the xml. Problem is, i need to acces a web service in my package, and built-in SSIS web service task is useless and just SUCKS. It fails to work even with miscrosoft's own sharepoint web service. So, i have to use scripting task. And scripting task uses "web reference" to access web services, which in turn gives no way to change it's url.  So, if i want to test and then move to production, i'll have to edit my package to change the web reference url from "testserver:5123/mywebservice" to "productionserver:81/webservices/mywebservice". And then change it again to test it again. Etc. Is there a solution to this? A way to programmatically change web reference's url from inside scripting task?

Filter out logically deleted data from dbf file using SSIS?

Hi Guys, while loading dbf file to sql server using SSIS,How can i filter out the records that are logically deleted in dbf file?    ~Technology Analyst~

SSIS File Does not exist or do not have permission


Hi there,

below is the description of  SSIS package,

Step 1 : Creating one excel file in step one using Script task

Step 2 : Uploading data to this newly created Excel file using Dataflow task.

Step 3 : Mailing the Excel file to user

Now the first 2 steps completes successfully but at step 3 it fails with following error:

[Send Mail Task] Error: Either the file "FileName" does not exist or you do not have permissions to access the file.

I think the file is locked with step2 how can i remove the lock?

Thanks in advance!!!

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend