March 31, 2011 at 4:24 pm
So I am running into an issue when pulling data from my data source and sending it to a Flat File destination. My connections are fine, I am able to pull data from my source and into my destination file. My problem is only certain data is getting pulled and placed into the flat file after I execute the process.
For example, I am pulling 10 columns that all contain data from my source, after I execute the process, only 2 fields are being populated (Amount, Date) in the Flat File. I am able to view my column headers in the flat file since they are being pulled correctly, but the data that is contained in those columns are missing when in fact they do exist in the source.
I've done some investigating and think it may be an issue when configuring the properties of each column. I may have the wrong "DataType" or "OutputColumnWidth". The missing data are VARCHARs format from the source, but I do not know which DataType to select when configuring the column properties in SSIS. I've tried "String [DT_STR]" and a couple others and still having the same problem. Although that is where I think the issue might be but I could be completely wrong.
I would greatly appreciate any help!
Thanks,
CP
April 4, 2011 at 1:09 pm
bump
April 4, 2011 at 1:54 pm
Can you preview the data source in your package? Or add a data watch to the flow? (Right-click the green arrow from the source to the destination and it will give you an option to view the data at that stage.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 4, 2011 at 2:20 pm
Thank you for the response!
I do have a data flow that I watch, it shows the correct column names at the top but the data being populated is all NULL except the Amount and Date fields.
Here is what it looks like...(try to imagine the columns below lining up)
RefNumber | Amount | Date | RefName | RefAddress
NULL | 100.00 | 11/2/2010 | NULL | NULL
NULL | 200.00 | 11/7/2010 | NULL | NULL
and it displays that for every record. I believe the Amount and Data field get populated because the data type for numeric and date is easy to define. What is not populating are Varchar data types. I've been spinning my wheels on this for a while and just completely stuck. Any ideas??
Thanks!
CP
April 4, 2011 at 2:48 pm
copett86 (4/4/2011)
and it displays that for every record. I believe the Amount and Data field get populated because the data type for numeric and date is easy to define. What is not populating are Varchar data types. I've been spinning my wheels on this for a while and just completely stuck. Any ideas??
Hey CP, my guess is this isn't a datatype problem (SSIS typically whines loudly when that happens), but a data flow problem.
So, can you confirm a few assumptions and provide what information you can?
I assume you're in a data flow task within the source control. I assume you're pulling from an OLE DB datasource that connects to SQL Server, and have linked that directly to the outbound flat file. I assume you've gone into the flatfile and doublechecked that your mappings are hooked up from the fields in the stream (what is coming into the object, the line you dragged) and to the outbound items on the file. I'm assuming nothing else is happening between the source and destination.
Can you tell me what in those assumptions are inaccurate?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 10:25 pm
I have a DataReader Source using ODBC driver(ADO.NET) for a connection that links to an outbound flat file destination. I use to have a data conversion step, but took it out since the flow has been running successfully with no errors and probably causing more over head. I've gone into the flatfile and checked my mappings, all header rows are being populated correctly and all date/numeric fields are populating too. What I've noticed is any field that is a VARCHAR from my data source is displaying as NULL.
Thanks for the help!
April 5, 2011 at 9:29 am
copett86 (4/4/2011)
I have a DataReader Source using ODBC driver(ADO.NET) for a connection that links to an outbound flat file destination. I use to have a data conversion step, but took it out since the flow has been running successfully with no errors and probably causing more over head. I've gone into the flatfile and checked my mappings, all header rows are being populated correctly and all date/numeric fields are populating too. What I've noticed is any field that is a VARCHAR from my data source is displaying as NULL.Thanks for the help!
Hey CP,
Is it possible for you to share some sample data that you are using to push to the file. As I am using the same case that you had mentioned here. But everything is working fine at my end.
I wonder there may be something to look upon in data that you are using!!
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
April 5, 2011 at 1:03 pm
copett86 (4/4/2011)
I have a DataReader Source using ODBC driver(ADO.NET) for a connection that links to an outbound flat file destination. I use to have a data conversion step, but took it out since the flow has been running successfully with no errors and probably causing more over head. I've gone into the flatfile and checked my mappings, all header rows are being populated correctly and all date/numeric fields are populating too. What I've noticed is any field that is a VARCHAR from my data source is displaying as NULL.Thanks for the help!
I'm assuming you get the same problem at design time as at runtime.
Can you put in a data viewer between the steps and see what you're getting out of the source? (Rt-Click and click data viewers...)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 5, 2011 at 3:08 pm
I was able to find a solution to my problem and everything is working now. Apparently my data-source contained certain data types that caused the issue. Any data containing that specific "type" all got converted to NULL values. In my SQL task I converted my data types to SQL VARCHAR and that did the trick!
I appreciate all the help!
April 7, 2011 at 6:13 am
Now you've made me curious what the type was.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply