Flatfile Destination Variable Filename

Why does the raw file have an option for a variable path and the flat file destination does not? Not having this feature makes it impossible to work with variable environments. Please add this option to the Flatfile Destination.You can use expressions on the flat file destination to change the location of the file.|||Thank you so much for your quick reply. I have the flat file destination highlighted but there are no properties for Expressions. I also looked in the connection properties also and could not locate a place to do this. I ended up writing a vb script but I would like to have the ability to do it graphically. The SSIS team definately needs to set up a way to set Global Variables (which could contain file path settings) via a sql statement. They also need variable file pathes exactly like the raw file.|||When you right-click on the flat file connection manager, you can select properties. In there is an Expressions property. You'll want to set the ConnectionString property to the full path containing the file.|||Oh awesome! Thanks Phil.

flat file to xml

Hey, all. Is it possible to read a flat file in and convert it to xml in SSIS? Xml is not listed as one of the destination types... OR, is there some easy way to take a fixed width flat file and convert it to xml?

Thanks for any insight!

Jim Work

SSIS does not include an XML destinaiton component. The explanation that I've heard is that this is because XML is too rich and complex a format to easily map to. At least in the first release.

You should be able to do this using a Script Component as a destination, if you have a known XML format to which you should be writing.

|||"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim
|||

It's my pleasure to help. It's always great to see how people are using SSIS, and always better to learn from others' pain than from my own.

Take a look at this page: http://msdn2.microsoft.com/en-us/sql/aa336314.aspx

There are sample component source code projects and tutorials/documentation on creating custom SSIS components available here for download.

|||

Jim Work wrote:

"if you have a known XML format to which you should be writing"

I can design the schema myself, and I know what I want to do with it. I've not messed with Script Components yet... as you may have noticed, I'm new.

Any idea if there's a tutorial out there that might help? Or even a simple example?

Thanks so much for your help today!

Jim

I've run into this a few times recently myself, so I created an example and posted it to my blog. Hope it helps.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/02/xml-destination-script-component.aspx

Flat File to SQL Destination - Unused Columns Warning

I have a flat file data source and SQL Server destination data flow. Only a subset of columns from the source are mapped to the destination. During execution SSIS returns DTS pipline warnings for every unmapped source column. Is some kind of transformation the only way to get rid of these warnings?

Also this data flow subsequently returns an error: [SQL Server Destination [1293]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security."

I'm researching this error, but if anyone is familiar with it your advice would be appreciated. Thanks.

You can either ignore the unused column warnings, or you can deselect them at the source connector so that they are not used. The preferred way is to not bring over additional columns from the source unless you need them for something.|||For the error message make sure that the file exists and that the user running the package has access to it.|||Amazing what you can miss the first time you work with a component. Deselecting the unwanted columns did the trick. Thanks Phil.|||

Rafael, it turns out I need to use an OLE DB destination instead of SQL Server destination because the database is on a remote server. See: http://msdn2.microsoft.com/en-us/library/ms141095.aspx

Specifically: "You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination."

|||which it makes sense. I totally missread your post. Sorry about that.|||

M.Glenn wrote:

Rafael, it turns out I need to use an OLE DB destination instead of SQL Server destination because the database is on a remote server. See: http://msdn2.microsoft.com/en-us/library/ms141095.aspx

Specifically: "You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination."

If you want to know more about why, read this:

Destination Adapter Comparison
(http://blogs.conchango.com/jamiethomson/archive/2006/08/14/SSIS_3A00_-Destination-Adapter-Comparison.aspx)

-Jamie

|||Thanks guys. The comparison article is also helpful.

flat file source and destination - need fixed width output

I have a text file that is comma delimited and im pulling it in with a flatfile connection manager. I want to read some of the data, then output another flat file but in a fixed column width. What settings do I made to the connection manager of the output flatfile ?Choose a fixed-width format when setting up the destination. And then define your columns.|||

Hi

A solution is:

- Go to advanced properties of output flat file.

- Define the fields as text and the property OutputColumnWith with the size you want

- Export your data to that file and convert it to text.

Raul

|||I had some other steps in the middle, so I took them out just to simplify things. In the connection mgr for my destination, it wants to know the input column widths. Should I really need to bother with this, since in the end, I just want whatever output i get during any previous steps, to simply be output to the flat file as fixed width ?|||Ok, ive managed to get my output in fixed width in the output file, but it appears the lines arent terminating where they should be. What controls where the lines terminate ? I dont have a header row (no column names in the first row), so what, if anything should the "header row" settings be set to ?|||Ive got a data file with values seperated with commas. I want to read in this text file, do a lookup and add the lookup column on the front of the other columns and save the output in a fixed width format, similar to what you would get if you saved the query reqults to a file in sql mgt studio.

What I have so far is just a flat file source , a lookup and a flat file destination.

I can get the output to generate, but there doesnt seem to be any row terminating, its all one big string.

help ?|||Use "Fixed width with Row Delimiters" option for the flat file connection manager.|||

Eric Wisdahl wrote:

Use "Fixed width with Row Delimiters" option for the flat file connection manager.

Where is that option available ? Under the general tab on my flat file conn mgr, I have only the options:
"fixed width"
"delimited"
"ragged right"

If I have fixed width selected, and go to the advanced tab, start entering my own columns, the column delimiter option is greyed out.|||When you are first creating the flat file connection manager it gives you the option of delimited, fixed width, fixed width with row delimiters, ragged right. All that fixed width with row delimiters does is add another column to each record which contains the row delimiter. You can accomplish the same thing by adding it in with the derived column and adding it to the end of your output record.

Flat File Source - Add Output Field

I am moving data from a flat file source to a SQL Server table. But I want to add a columm that IS in the destination table, but NOT in the source file. Say the table column name is XXX in destination table, and there will be a global variable called @.[User::XXX] that remains constant throughout the package. I would like to put the variable value into the destination column, even though the source file does not contain the field. Is there an easy way to do this?Add a derived column transformation between your source and destination. Then simply drop the User:XXX variable into it. That will add a new column to the data flow.|||Thanks! This was the solution. I actually came across that while trying to solve a problem or recalculating and input field while passing through.

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 Destination Filename with Date ?

Is there a way to append a date at the end of the filename for whenever a Flat File Destination is run? So far, I only see the ability to browse to a static filename.For the Flat File Connection, which is being used by the Flat File Destination, you can set the ConnectionString using Expressions, to set your file name with dates.|||Do you have any resources on syntax for this?|||

"C:\\file" + (DT_WSTR, 2)month(getdate()) + (DT_WSTR, 2)day(getdate()) + (DT_WSTR,4)year(getdate()) + ".txt"

For example, if i use the above expression for Connection string of the Flat file connection, it will create a file

C:\file12192005.txt

if i run it today.

|||Are you talking about File Connection Manager when you click browse to specify the File Path? If I put that in there of course it doesn't like it. Where exactly are you placing in this script, what component, what tab, what property?|||

For the Flat File Connection Manager, on Properties tab, you see Expressions, when you click there, you can set expressions for some of the properties for the Flat File Connection Manager. In this case, I am asking you to set the ConnectionString property as an expression.

you should see

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

in BOL for further details.

|||Oh, I see, I was double-clicking the file connection manager using the properties there, I didn't think to look at the VS properties pane in VS, thanks!|||

either I'm missing something or this link doesn't work:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

|||Open BOL and paste this Url in the url box. It is not a http link.sql

flat file destination -data all on one line

I have a flat file destination that Im sending data to, from an OLE DB data source. There are two records, but for some reason they are both going on the same line in the output. This is after setting the output to fixed width, from comma delimited.

Help ?

If you are viewing the flat file from a text editor, recheck the option on your editor to verify word wrap is not occurring. The file may be perfectly fine, it's the representation in the editor.|||

If you are using the pure FixedWidth format the row delimiter is not going to be inserted. It assumes all your columns are fixed width and it does not put any delimiters.

If you want to break your rows in new lines, choose the Fixed Width with Row Delimiters option at the time you create the flat file connection from the Flat File Destination. This will configure the connection to use the RaggedRight format and add a dummy column with the new line delimiter at the end. You should be able to do this manually as well, if you are changing an existing connection.

HTH.

Flat file destination could not be loaded

Hi,

I have created a package and when i was trying to configure a flat file destination, i am getting the following error:

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Please advise.

Thanks & Regards,

Deepak

Was it working before? If yes, what has changed in the meantime?

Thanks,

Bob

Flat file destination could not be loaded

Hi,

I have created a package and when i was trying to configure a flat file destination, i am getting the following error:

===================================

The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)

===================================

Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.

===================================

Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)

Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)

Please advise.

Thanks & Regards,

Deepak

Was it working before? If yes, what has changed in the meantime?

Thanks,

Bob

Flat File Destination - File name

Hello,

I'm trying to send a file with FTP.

In the Flat File connection manager I have to write the name of the file.

Is there any way of deciding at runtime what the name of the file should be?

Thank you.

Yes. Use expressions.

http://blogs.conchango.com/jamiethomson/archive/tags/Expressions/default.aspx

-Jamie

|||

Hi Chaps

I am trying to achieve a similar thing with the FTP Task
ie incorporate the date into the filename when sending the file.

I have set IsRemotePathVariable=True

I have created an expression for the RemotePath:
"/FName"+ (DT_WSTR,4)DatePart("yyyy", GetDate())
+ (DT_WSTR,2)DatePart("mm", GetDate())
+ (DT_WSTR,2)DatePart("dd", GetDate())+".txt"

However, an error is produced:
"Error at FTP Task: Failed to lock variable"/FName2007611.txt"

Should I be linking the expression to a variable ?
and if so how

Any guidance would be very much appreciated.

Regards

JohnJames
|||

Hi,

Just so I understand this correctly, do you use a hard coded file name in the Data Flow Flat File Destination (f.ex. outputfile.txt) and then afterwards in the Control Flow you change the file name (f.ex. outputfile_11_06_2007.txt) by using these expressions?

Thank you.

|||Yes

In the Dataflow I am using a hard-coded filename,
to extract data from SQL Server to a flatfile.
In the Control Flow is the FTP Task where I am trying to add
the Date to filename using an expression.

Regards

JohnJames
|||Why don't you create a variable who's value is an expression. You then use the variable in the data flow and the FTP task. That way you are always guaranteed that the file name is correct.
Saves you from having to rename a file as well..|||Thanks Crispin

I'll give a go and let you know

Regards

JohnJames|||

Hi,

I'm trying to decide the filename of my Flat File Destination at run time by using an expression variable.

I created an expression variable:

Name: FileName_Expression Value: C:\test.txt

However, in the Data Flow in Flat File connection manager, I can't seem to put my variable name inside the "File Name" editbox and I have no idea where to put my expression variable.

Any ideas?

Thank you so much!!!

|||

Mr. Hat,

Use the connectionstring property of the flat file connection manager.

ex. ConnectionString = @.[User::myPath] + "\\ERROR_" + @.[User::FileName_Expression]

(Where myPath is something similar to C:\temp)

NOTE: new questions like this should probably get a new thread as many people will just look past the threads which appear to have already been answered.

Hope this helps.

EWisdahl

|||Wow, it worked! Thanks a lot!! |||

Hi

when i'm trying to set the file name runtime for excel destination by setting expression

ConnectionString : @.[User::ExcelFullFileName]

but getting below error

Error at Data Flow Task [Excel Destination [7689]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

please can you advice regarding this?|||I believe (but I may be wrong) that the Excel file needs to exist to be referenced by the Connection Manager.sql

Flat File Destination

Hi,

How can I check if my Flat File Destinatino is empty or not?

I′m sending it with FTP, but I don′t wanna send an empty file.

Any ideas?

Thank you.

I reckon you coudl use a WMI query to get this, but to be honest I don't know how.

I would use the Script Task, just write a quick line of VB to grab the file size and stuff it in a variable. If being good you could read the filename straight off the file connection as well, e.g.

Code Snippet

Dim fileInfo As System.IO.FileInfo = New System.IO.FileInfo(Dts.Connections("Flat File Conn").ConnectionString)

Dts.Variables("FileSize").Value = fileInfo.Length

Note I created a variable called FileSize of Int64, and added it to ReadWriteVariables list of my Script Task.

Next, I would use an expression on the constraint leading from the script task to the ftp task. @.[User::FileSize] > 0, such that the expression must be satisfied to run the FTP task.

Flat File Destination

I have a red link from my OLE DB Destination to my Flat File Destination. I'm not sure why it's read but the OLE DB shoves some data into a table. I want to then take that data and move it into a flat file using the flat File Destination step but when I run my project, it shows the Flat File Destination Step green but my flat file shows nothing at the end.

I'm not sure what I'm doing wrong but the line linking the two is still red. Check out my picture here:

http://www.photopizzaz.biz/junk/1.jpg

and then the properties of my flat file destination module:

http://www.photopizzaz.biz/junk/2.jpg

http://www.photopizzaz.biz/junk/3.jpg

and my flat file connection manager editor properties:

http://www.photopizzaz.biz/junk/4.jpg

http://www.photopizzaz.biz/junk/5.jpg

Actually you redirected only the ErroroOutput of the OLE DB Destination.

Once the rows got into OLEDB Destination they don't move further. You need to add a Multicast transform in front of OLE DB destination to multiply the rows. And you can send one output to OLE DB Dest and one output to Falt File Destination

HTH,
Ovidiu

|||thanks a lot. But then what hooks up to the Multicast transform prior to the multicast transform? After my OLEDB Destination, then what, I just hook that up to the Multicast Transformation or hook something else to it first? How do I continue the flow after the OLE DB Destination is done I guess is what I'm asking...going from the OLEDB to the Multicast ? What's in between those 2?|||what did you mean by "Actually you redirected only the ErroroOutput of the OLE DB Destination."|||

Ok, got it to work, I didn't need what you said. I just used an OLE DB Source like I tried oringally but just deleted and reset it up shown here:

With that, how do I specify order of the workflow between my left side and right side. In other words, how do I ensure that after the OLE DB Destination is run, that it runs my OLE DB Source next? Since I can't hook an arrow between the 2, what is the proper way to ensure workflow and order here between the left set of tasks and right set of tasks show here:

http://www.photopizzaz.biz/junk/6.jpg

|||

You cannot force parallel graphs in the same Data Flow to execute sequentially. By setting them in the same Data Flow task you are actually setting a preference for them to be executed concurrently.

To assure your data flows are executed sequentially use multiple Data Flow tasks and connect them to define a wished order.

Thanks.

|||thanks, I actually figured out the data flow as you suggested.

flat file as source

Hello Ereryone,

I have Flat File as my source. Before i tried to load the data in to ORACLE Destination thru SCD component the error was with ole db.

any ways i try to load the data in Access DB but I’m getting different error in same component (OLE DB) After SCD Component. can any one help me out in this.

thank you

What exactly is your problem? We would need more information to help you out...

What errors are you getting, what transforms are you using, have you looked at the data directly before your destination, if so does it seem to be in order, do you have any column type mismatches, etc.