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

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

How to read Hex 00 character in SSIS

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
hello there   i have a large text file which i need to import into sql server. i have been able to map the fields and load the data. it is a fixed width file. the process works. however during testing i found that there are some character which are not visible in notepad. on a hex editor they show the value of 00 - null. in the file . the ssis package reads till the column it finds null and then considers it as a carriage return and moves to the next line.   please suggest how i can resolve this issue. i wrote a vbscript where i could open the file and save it again using notepad, it works for small files. but my input file is around 2 gb.   thanks prasanna

View Complete Post

More Related Resource Links

SSIS And Sybase Source OLEDB connectivity issue with code page for character set

Hi I am using SSIS 2008 to connect the Sybase Server version 15.0.3 64-bit. The default character set id used by this Sybase server is 4 (i.e. roman8). (http://manuals.sybase.com/onlinebooks/group-charc/chg0300e/charsets/@Generic__BookTextView/1706;pt=266) I had installed Sybase Client for Sybase OLEDB Driver (ASEOLEDB) to integrate this in OLEDB Source Editor. As per guidance from one of the past MSDN forum discussion, I tried setting property of AlwaysUseDefaultCodePage property to "FALSE"  (http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f5af9328-e1fb-48d7-a85d-1d08bb7cf0e5/). However, I am still experiencing error message as follows:- ********************************************************* Error at Package2 [Connection manager "xxxxxx.xxxxxxx"]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[00000] [Native Code: 30061] [ASEOLEDB]Could not load code page for requested charset". An OLE DB record is available.  Source: "ASEOLEDB"  Hresult: 0x80004005  Description: "[1ZZ001] [Native Code: 30016] [ASEOLEDB]Internal Error". Error at Data Flow Task 2 [OLE DB Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMC

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

Wild card filter character in SSIS

In my Data flow task i need to select certain rows which are matching my criteria. The criteria requires using wild card characters like Field1 = '%aaaa%'  or Field1 = '%bbbb%' ......                                                                          Field2 = '%aaaa%'  or Field2 = '%bbbb%' ...... There are 6 fields ( Field1.. Field6 ) that have to be compared against values from a table ( aaaa,bbbb,ccccc etc from table) FindString is not a option. Any help will be appriciated!  

Use SSIS to read files in SharePoint library

Hi,I'm trying to use SSIS to read data from an Excel file in a SharePoint document library and I am having some problems with it.First I tried to use the Excel file as a source file in an Excel Source task which worked well in BIDS but not on the SQL server. ("CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER")Next I tried to read data from an Excel file on the SQL server file system which worked well on the SQL server.Next I tried to use a File System Task to copy the Excel file to the SQL server file system. This also worked well in BIDS but not on the SQL server. ("The file name \\sharepoint\mylibrary\myfile.xls specified in the connection was not valid.")I have also tried to let the SQL server agent run on a domain account that is set up as a reader on the SharePoint document library without success.Where am I going wrong?

SSIS Function for find the Occurance of a character


Please help me to find the solution for the below requirement.

Input data: "AAAA,BBBB,CC" or "A,B,CCC"

I need to check whether the record is having any comma(,) or not. I need to check this this in a "conditional split transformation".

Please let me know which function will help me.

Many Thanks,


Delimiter File Read Task Installer failed to show in SSIS Data Flow Items


I downloaded the Delimiter File Read and followed the instruction and installed it. The file is on the correct directory (>:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents) The file DelimitedFileReader.dll is there.

However, when I go to BI Development Studio and try to add the new task I can't see it in the SSIS Data Flow Items...it is not visible. Does anyone know why I can add/see it?



Read Access Files from Folder with SSIS

Can some body suggest how to read access files from folder location and insert the data from access Db table to Sql 2008

Error while trying to assign a value to a Read Write variable in SSIS package script component



       I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute".


What should be done to over come the problem please help me on this regard 




Is Dirty Read Isolation the only option to import data from informix using SSIS consistently


Hi there

I've got a package that imports data from informix database. I'm using iBM Informix ODBC Driver 3.50 with ADO.NET data source to import data. Every now and then the package fails with "Could not do physical order read to fetch next row" issue so did some investigation and found that its due to the lock issue. Most of the guys suggested setting up isolation level to "Dirty Read".

Is it the only option to import data consistenly without any error from Informix? Because with "Dirty Read" you get concurrency issue. 

My other question is; can we use the transaction option with isolation level available is SSIS to get around this issue and if yes then how to approach it. And if it's no then is it limited to oledb sources only.


Regards, Vivek

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.

Serial Port Communication Read and Write using C#.net

In this simple article you will learn that how you can Open and read COM port data using System.IO.Ports Namespace.
Simply just create New Project of windows application in C#.

Import ServiceProcess APIs
using System.IO.Ports

Imports System.IO.Ports

Write Parsing functions which parse port data according to attached device

Scripts not read when in head tag


I just upgraded to VS2010 if that matters.  I'm learning JQuery and noticed that scripts don't work if they're in the head tag.  Also, I need the script tag with type="text/javascript" in the head (with nothing else) in order for the JQuery scripts in the body to work.

Very bizzare, any suggestions?

With a separate script tag in the <body> with my JQuery script, this works

<head runat="server">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" />
<script type="text/javascript">

This doesn't:

<head runat="server">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" />

WebMatrix conversion failed when converting from a character string to uniqueidentifier?


I am trying to retrieve xml from a small function in my .cshtml page. Its throwing me an error. This however, runs fine in a console/form environment.

It takes 3 parameters and 1 exception.

myDll.GetXML(year, username, uniqueidentifier, out ex);

When I run this page I get the error, "Conversion failed when converting from a character string to uniqueidentifier."

Or a stack of.

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting from a character string to uniqueidentifier.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   at System.Data.SqlClient.SqlDataReader.get_MetaData()

   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBeha

I am not able to read xml file with XDocument if its Descendants has Attribute xmlns="http://abc.co


I am not able to read xml file with XDocument if its Descendants has  Attribute like:-

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
  <row ID="1" xmlns="http://www.opentravela.org/OTA/2003/05">
    <var country_name="Caribbean and Rest of the world" city_name="Kingston" flight_category="Economy Class" via="Atlanta" flight_departure="London Heathrow" airline_logo="Delta" adult_fare="Delta" child_fare="660" infant_fare="561" date1="132" date2="2010-08-15" />


I don't know what to do ?

Looking for some help..

How to read XML using LINQ in VB.NET


Can someone please tell me (sample code) how to read this XML into a SQL DB?  Should I use LINQ to XML?  Does anyone have any sample code?  I am working in VB.NET


<?xml version="1.0" encoding="utf-8"?>

<ReadOrderResponseType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<Status xmlns="urn:mysol:apis">Success</Status>

<TimeStamp xmlns="urn:mysol:apis">2010-07-25T15:26:49.1150235Z</TimeStamp>

<StoreUrl xmlns="urn:mysol:apis">http://xyz.COM</StoreUrl>

<PageResponse xmlns="urn:mysol:apis">




Read Excel file from Web Application (C#)



I have a web app where user uploads a file.

File is saved in server locally and I want to read some data from it.

Currently my development  server has office 2003.

So do I need office 2007 in order to be able to  read an excel from office 2007 ?

What are other requirements  ?

I tried a test, using

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wrkBook; // = new Microsoft.Office.Interop.Excel.Workbook();

wrkBook = excelApp.Workbooks.Open(@"C:\www\excel_files\b.xls", ....);

But When trying to open the file I get the error:

Exception from HRESULT: 0x800A03EC

So I'm wondering what I'm missing.


How to read value of CheckBox in EXCEL sheet


I am using OLEDBConnection (@"Provider=Microsoft.ACE.OLEDB.12.0;") to connect to the EXCEL file and read contents. When I read the contents, I get all the contents from the excel sheet but i dont get contents for checkboxes placed inside.

My Excel sheet contains set of checkboxes and I need to know If they are checked or not.These checkboxes are controls which are added from Developer tab in EXCEL.

I dont want to use Excel Assemblies for reading contents as it is not recommended. And also i am using OLEDBconnection and want to stick to this .

Awaiting replies from you guys. 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