.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 2008 and output txt file of specifies structure

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hi All.
I have query to Oracle 11.

I need to iterate it and make txt file (UTF8).
In fact it consist of header + body + footer
body is made of records like:
"Afghanistan","Aland Islands","Albania"
values of in quotes. (in fact it is json file)

1?) Can I do it w/o script
 using Foreach Loop Container
 I don't see how to do
S_all = S_all + ',"' + field1 + '"'
where S_all is SSIS var

2?) If use script in data flow
Could anybody give code example (bettre in VB but C# also ok)
how to iterate recordset and make var like:
S_all = S_all + ',"' + field1 + '"'

View Complete Post

More Related Resource Links

Output parameters in OLEDB Command component - SSIS 2008

I have a package that I developed in SSIS 2005 and recently ported to 2008. Everything runs great except an OLE DB Command component that calls a stored procedure that uses output parameters. When I try to run this, I get this error: Error: 0xC0202009 at Data Flow Task, OLE DB Command [100]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error". Error: 0xC004701A at Data Flow Task, SSIS.Pipeline: component "OLE DB Command" (100) failed the pre-execute phase and returned error code 0xC0202009. When I do the same thing in SSIS2005, it works fine. So I setup a new package with 1 data flow. In that data flow, I added a source component, an OLE DB Command, and a destination.  I then tested this with a stored procedure that took no parameters. It worked fine. I then tested it with a stored procedure with 1 parameter defined as OUTPUT. This generated the error.   Is there an issue with output parameters in an OLE DB Command in SSIS 2008?   Thanks!http://bobp1339.blogspot.com

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

SSIS 2008: Problem reading dynamic file during execution



I have a SSIS 2008 project that reads a bunch of files from a file (always the same file) from Machine A.
My package is running on Machine B and this is where the information is being imported to, my Reporting DB (sql 2008) is also located in Machine B. 
I have a dynamic log file that stores the execution errors every time the package is executed and its name is made up of the timestamp of when package starts executing. The "DelayValidation" option for this connection is set to "TRUE". When i execute my Package from Visual Studio 2008, it runs without a problem.
The problem is when i deploy my package into SQL Server 2008 and try running it through DTEXEC. I always get the following error:


Error: 2010-11-03 14:57:29.06
   Code: 0xC001401E
   Source: MyImportPackage Connection manager "ImportLog"
   Description: The file name "\\ip-address\d$\Logs\ImportManager\20101103_145726.txt" specified in the connection was not valid.

SSIS 2008 R2 Flat File Destination Producing Duplicates


I am writing to a flat file destination in a 2008 SSIS package. 99.99% of it works correctly. However, I get one duplicate record in the destination file.


Here is the basic flow of the package:

 1. Read two ISO-8859-1 encoded files and encode their text to UTF8 in memory

 2. Combine the two files together in memory and load them into a lookup cache

 3. Read another source file from disk

 4. Match an ID column

Using Conditional Split data Transfer in SSIS 2008

This article uses the Integration Services Conditional Split Data Transformation element to filter and transfer data from a set of flat text files to SQL Server database table. The concept can be easily extended to apply to any other source or destination such as Microsoft Excel. This scenario is useful in creating denormalized database tables in a reporting and analysis situation.

FLV file in Visual Web Developer Express 2008


I'm trying to embed a flv in my web page and I don't have a clue how to do it.

I downloded and installed ASPNetFlashVideo and the tools do not appear in my toolbox.

So if anyone has a work around it would be greatly appreciated.

Issues with SSIS and SQL 2008 - Integration services not running

Hey guys, I installed SQL 2008 with complete BI studio including Integration Services. When i check in my services - i cant see my integraiton services in there When i try to connect to Integration Service using localhost, I cant connect it And when i try to run setup of SQL 2008 - It shows that Integration Services already installed and that what i checked when i fetched my Installation tool report from SQL Server to verify i have it installed. Why can i access Integration services, if i already have it, Please suggest with ideas, checks i can make to ensure every setting is in place. Thanks. Would appreciate for quick response.  

SQL Agent Jobstep output file contains automatically generated header

Hi,   I've created a stored procedure that creates a script to create a number of objects within the database (based on what existing objects are in the database).  From Management Studio, this works fine, and the output is exactly as I want it.   I'm now trying to create a job that will execute this stored procedure, and deposit the results into a file somewhere on the server.  When the job runs, the script is created in the correct place and is essentially ok.   However, there are a couple of questions I'd like to ask.   Why does SQL Server Agent put a header at the top of the output file?  I was hoping to be able to use that output file 'as is' and execute it automatically to recreate my objects when required.  (Obviously, I can manually remove the header, but this is an inconvenience in this situation).  How do I stop it?   Also, when executed from SSMS, the output is correctly line-spaced.  But, the output from the scheduled job adds an extra line between each line of text, which is, again, inconvenient.  Why does it do this, and how can I prevent this (again, without manually editting the output)?   Any thoughts and help greatly appreciated.   Cheers Simon

Site File Structure, Folders, Learning project, subweb, web.config

I am just learning asp.net.  I purchased an "ASP.Net 3.5 web programming in C#" book, which I like allot.  I am on chapter 3 and want to complete a shopping cart exercise from the book.  I have to create several folders and files.  The exercise has the files and under the root directory and a few folders below that.  I already have a site that I've been learning on.  Can I create a subfolder on my site and put all the files in there?  Like the subfolder is the root of the project.   Here is list of Folders and files... Code/CartItem.cs Code/Product.cs Data/Cars.mdb Images/car.jpg Cart.aspx Order.aspx Web.config ................................ On my website I want to create a subfolder to keep alll the files in, as so... ShoppingCart/Code/CartItem.cs ShoppingCart/Code/Product.cs ShoppingCart/Data/Cars.mdb ShoppingCart/Images/car.jpg ShoppingCart/Cart.aspx ShoppingCart/Order.aspx ShoppingCart/Web.config ................................ Can I have two web.cong files?, one in the subfolder?  Or do I have to use the single, web.config file in the root directory of the site?  Are there any other pitfalls or trouble I might get into setting up my file structure like this?  Anything I should be aware of? Any thoughts or advice would be appreciated. Thanks, Mike   

Post Upgade task, Upgrading SSIS Pacakges to SQL Server 2008.

Hi, I am trying to upgrade sql 2005 packages to sql 2008 after doing in-place upgrade of DE and SSIS. Can I know what packageformat column in msdb.dbo.sysssispackages refer to, as according to http://msdn.microsoft.com/en-us/library/cc879336.aspx the value should be 2 if the package is in sql 2005 and it should be 3 if it is upgraded. But I am seeing only 0 or 1.   Can I know any other method to figure out version of the ssis packages? I am having issues upgrading SSIS Packages from 2005 to 2008, using SSIS package upgrade wizard.   Thanks for your help. Regards, KRanp.

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.

SSRS 2008 Having more than one subreport changes output

I have a master report which has a list control.  The list contains employee header information.  I have ticked the option to have a page break between each instance of a group on the details group properties page as i want each employee on a seperate page. There is no grouping on the list other than the default details.  I have two sub-reports which are linked to the main report by an employeeID.  When i add the first sub-report it displays the header and associated detail without a problem.  When i click on the next record it shows me the correct next employee header and the correct associated details.  However, when i add the second subreport to the list and review it, the first record shows okay, but when i click on next all the header information is gone - when i then click on previous to show the previously ok record - all the header for that record has now gone.  It doesn't matter which subreport i use as the first one on the master - it happens in exactly the same way as soon as i add the second subreport to the list control.  This is a vitally important report and i am now starting to get very frustrated as i cannot easily see a solution.  Your help would be most appreciated.  Barry  

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Meng Chew

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.

FileStreamResult how to rename the output file?????

i have an action to return a pdf file in my controller,if the return file is for downloading, the following code can be used to modify filename successfully.HttpContext.Response.AddHeader("content-disposition", "attachment; filename=form.pdf");HOWEVER, i found that when the pdf file was returned to browser( rather than a pop up window to download), the filename always remain same as the action name! (when u right click file and try to save it on ur hard disk), i was wondering if there is a automatic way to rename the file rather than "right click" and "save as" manually.so  can anyone tell me how to rename the outputfile when it is shown in the browser?

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

Diffrences in SSIS 2005 and SSIS 2008?

Hi All,   How to convince interviewer in  major differences in ssis 2005 and ssis 2008.In my system I have ssis 2008.Can I install ssis 2005? How to install? Thanks CMK…
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