Flat file source not option for importing data

I was getting the product error associated with the full version of SSIS not installed so I ran the installation again and selected the Integration Services check box.

Now when attempting to import data into a database, the drop down list doesn't have a flat file option.

How do I import data from a txt, csv file?

Thanks

PatrickCrofoot@.hotmail.com

did you stop and restart Integration Services? is it running under a domain administrator account?|||I have. And no..

Simply using SQL Server Management Studio and right clicking my db then choosing import data under tasks. There is no Flat File datasource.

So the other route was to attempt the tutorial via integration services. That's when I get the more descriptive error that the FLATFILE option is not installed correctly.

I've completely un-installed and re-installed SQL Server 2005 and all associated tools. Same problem.
|||I'm having the exact same problem. I'm about to install CTP SP1 to see if it corrects the problem.|||I uninstalled and re-installed the complete application.... twice.. And now it seems to work. Nothing to do with SP1.

Flat file source not option for importing data

I was getting the product error associated with the full version of SSIS not installed so I ran the installation again and selected the Integration Services check box.

Now when attempting to import data into a database, the drop down list doesn't have a flat file option.

How do I import data from a txt, csv file?

Thanks

PatrickCrofoot@.hotmail.com

did you stop and restart Integration Services? is it running under a domain administrator account?|||I have. And no..

Simply using SQL Server Management Studio and right clicking my db then choosing import data under tasks. There is no Flat File datasource.

So the other route was to attempt the tutorial via integration services. That's when I get the more descriptive error that the FLATFILE option is not installed correctly.

I've completely un-installed and re-installed SQL Server 2005 and all associated tools. Same problem.
|||I'm having the exact same problem. I'm about to install CTP SP1 to see if it corrects the problem.|||I uninstalled and re-installed the complete application.... twice.. And now it seems to work. Nothing to do with SP1.

Flat file produced but need line returns

My Integration Services creates a flat file using OLE DB Source and then a Flat File Destination. The flat file is created from data from my data source with is just a table with many rows.

Each row in my flat file .txt is appended onto each other, there are no line returns after each record. How can I put in a return after each row in my flat file that is outputted from the Flat File Destination component in conjunction with the properties in my Flat File Connection Manager. What am I missing here in order to ensure each row from my table creates a carriage return in my .txt flat file?

The column separator for your last column need to be {CR}{LF}

HTH,
Ovidiu

|||

In my File Connection Manager, I have {CR}{LF} for the header row delimeter but it's not putting a return at the end of each row in my flat file still.

I do not have a header row since this is coming straight from the table so I don't understand why the requirement to specify header information in the File Connection Manager at this point but it is required.

So I'm not sure what else I'm missing, my flat file still doesn't return each row, it appends each row right after another still.

|||I see what you're saying, put

{CR}{LF}

in the column delimeter for the last column's properties, is this correct? If so, I can't that property is greyed out (because it doesn't need it when specifying a flat file) when I click on the last column in the Advanced Tab of the File Connection Manager.

I just don't see where to specify the row delimeters after looking at all the tabs.

|||

Here's my flat file, still doesn't have carriage returns:

http://photopizzaz.biz/junk/flat_file.txt

|||What format are you using for your flat file? You should use RaggedRight if you want carriage returns at the end of your row.|||

The file needs to be a fixed format of 100 bytes. It needs to be formatted like this:

http://www.photopizzaz.biz/junk/final_flat_file.txt

It has to have line carriages just like this one.

|||Ranjeeta , you just made my day, thank you! it was so obvious but I had no clue!|||I had trouble figuring that out too and found the ragged-right solution on my own. But I have a different problem now. I specified a multi-line header in the flat-file destination component, but the text file produced only uses the line-endings of the header when viewed in WordPad or some other advanced text editor.

Notepad only shows little boxes and this make the header useless.

How do I get a real line-ending into the file at the end of each of my header lines so that notepad will display it properly?

Thanks!|||Don't use notepad. Try VIM, or wordpad, or TextPad or something like that. Notepad is a "dumb" editor and can't handle too many special characters.|||That particular solution is already in place. Some of my users are stubborn. You know how that goes. Notepad is their friend... In any case, there are a large number of less savvy software packages out there that will have the same trouble as notepad, and it would be nice for everyone using SSIS to be able to produce flat files that they can consume.

TextPad is what I generally use. Wordpad works too.

Thanks though.|||Right, but Notepad only processes CR-LF line terminations. If a line terminates with LF, it won't display correctly in Notepad. You can't do anything about that, unless you issue a CR-LF.

Flat file produced but need line returns

My Integration Services creates a flat file using OLE DB Source and then a Flat File Destination. The flat file is created from data from my data source with is just a table with many rows.

Each row in my flat file .txt is appended onto each other, there are no line returns after each record. How can I put in a return after each row in my flat file that is outputted from the Flat File Destination component in conjunction with the properties in my Flat File Connection Manager. What am I missing here in order to ensure each row from my table creates a carriage return in my .txt flat file?

The column separator for your last column need to be {CR}{LF}

HTH,
Ovidiu

|||

In my File Connection Manager, I have {CR}{LF} for the header row delimeter but it's not putting a return at the end of each row in my flat file still.

I do not have a header row since this is coming straight from the table so I don't understand why the requirement to specify header information in the File Connection Manager at this point but it is required.

So I'm not sure what else I'm missing, my flat file still doesn't return each row, it appends each row right after another still.

|||I see what you're saying, put

{CR}{LF}

in the column delimeter for the last column's properties, is this correct? If so, I can't that property is greyed out (because it doesn't need it when specifying a flat file) when I click on the last column in the Advanced Tab of the File Connection Manager.

I just don't see where to specify the row delimeters after looking at all the tabs.

|||

Here's my flat file, still doesn't have carriage returns:

http://photopizzaz.biz/junk/flat_file.txt

|||What format are you using for your flat file? You should use RaggedRight if you want carriage returns at the end of your row.|||

The file needs to be a fixed format of 100 bytes. It needs to be formatted like this:

http://www.photopizzaz.biz/junk/final_flat_file.txt

It has to have line carriages just like this one.

|||Ranjeeta , you just made my day, thank you! it was so obvious but I had no clue!|||I had trouble figuring that out too and found the ragged-right solution on my own. But I have a different problem now. I specified a multi-line header in the flat-file destination component, but the text file produced only uses the line-endings of the header when viewed in WordPad or some other advanced text editor.

Notepad only shows little boxes and this make the header useless.

How do I get a real line-ending into the file at the end of each of my header lines so that notepad will display it properly?

Thanks!|||Don't use notepad. Try VIM, or wordpad, or TextPad or something like that. Notepad is a "dumb" editor and can't handle too many special characters.|||That particular solution is already in place. Some of my users are stubborn. You know how that goes. Notepad is their friend... In any case, there are a large number of less savvy software packages out there that will have the same trouble as notepad, and it would be nice for everyone using SSIS to be able to produce flat files that they can consume.

TextPad is what I generally use. Wordpad works too.

Thanks though.|||Right, but Notepad only processes CR-LF line terminations. If a line terminates with LF, it won't display correctly in Notepad. You can't do anything about that, unless you issue a CR-LF.

Flat file export

I am new to the Integration Services and have a question. I need to export some data to a flat file. I set up a project and have a OLE DB Source object that I wrote a query to grab the data. I then pass that data to a Flat File Destination object. My question is that in the database one of the fields is stored at True or False, but in the flat file I need for it to be -1 or 0. Any help would be appreciated.

Thanks

The easiest way to do this is in your data set. Use a sql CASE statement to read the value and return the appropriate values to the column for TRUE and FALSE.

Hope that helps.

|||That will work perfect, Thank you.
|||

You can also do the same thing in a derived column transform using:

Code Snippet

[YourColumn] ? -1 : 0

assuming 0 is false and -1 is true.