July 10, 2014 at 2:01 pm
In my OLE DB Source, I have a SQL command that output a datetime field to "mm/dd/yyyy", however in my Flat File Connection Manager, the Preview display as "yyyy-mm-dd 00:00:00". When I execute the package, it outputs to flat file as "yyyy-mm-dd 00:00:00". How do I output as "mm/dd/yyyy"
July 10, 2014 at 5:07 pm
Track the metadata in the flow and determine where you switch from DBDate to String (or if you even do).
If you don't, and it's DBDate from beginning to end, the date has never been formatted, it's being formatted for outbound (internally it's a Julian value). You'll have to include a Derived Column between the beginning and the end, convert the Datetime to a String, and format it using datepart (No, it's not fun, and no, there's no easy way, it's going to be ugly) to the exact way you want it to look.
Either that, or convert it to string using CONVERT( VARCHAR(10), column, 101) on it's way out which will also strip off the time component.
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
July 10, 2014 at 6:21 pm
I already have CONVERT( VARCHAR(10), column, 101) in my SQL command in my OLE DB Source. Also in my Flat File Connection Manager, the Properties for that column has DataType = string[DT_STR]. Please advise.
July 10, 2014 at 6:38 pm
is250sp (7/10/2014)
I already have CONVERT( VARCHAR(10), column, 101) in my SQL command in my OLE DB Source. Also in my Flat File Connection Manager, the Properties for that column has DataType = string[DT_STR]. Please advise.
Um, whut? That's... wth? I've never seen it do that unless something in between the source and target plays with it.
What version of SSIS are you running? Is there any controls in between the OLEDB Source and the Flatfile target?
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
July 11, 2014 at 8:57 am
There is nothing between the source and target amd I am on 2005
July 15, 2014 at 8:22 am
anyone?
July 15, 2014 at 11:47 am
SSIS issues are one of the harder to debug on a forum. For many of us not having the actually SSIS package(s), the underlying tables, and other database objects used in the package and sample data in those tables, it is hard to provide suggestions on what to do.
July 15, 2014 at 12:24 pm
This is only my 2nd package so any help would be much appreciated. It's a very simple package consisting of a single Data Flow Task. Within the task are 2 objects...OLE DB Source and Flat File Destination. In the OLE DB Source, I have a SQL Command Text with the below script
select a.personnum,a.first_name,a.middle_init,a.last_name
,convert(varchar(10), a.hire_dt, 101) as hire_dt
,a.base_wage,a.status,b.locss as location
,a.locss_id,a.supervisor_locss_id,a.locss_pw,a.locss_sec_lvl
from LOCSSDemographicData a
left outer join e2l_loc_xlat b on b.etime = a.location
order by 2
When I click on Preview, it shows the Hire Dt as "mm/dd/yyyy". In the Flat File Destination, it's pointing to a Flat File connection manager called "etime_to_locss.txt". When I open the "etime_to_locss.txt" connection manager, and click on Preview, it shows the Hire Dt column as "yyyy-mm-dd 00:00:00".
July 15, 2014 at 12:47 pm
is250sp (7/15/2014)
This is only my 2nd package so any help would be much appreciated. It's a very simple package consisting of a single Data Flow Task. Within the task are 2 objects...OLE DB Source and Flat File Destination. In the OLE DB Source, I have a SQL Command Text with the below scriptselect a.personnum,a.first_name,a.middle_init,a.last_name
,convert(varchar(10), a.hire_dt, 101) as hire_dt
,a.base_wage,a.status,b.locss as location
,a.locss_id,a.supervisor_locss_id,a.locss_pw,a.locss_sec_lvl
from LOCSSDemographicData a
left outer join e2l_loc_xlat b on b.etime = a.location
order by 2
When I click on Preview, it shows the Hire Dt as "mm/dd/yyyy". In the Flat File Destination, it's pointing to a Flat File connection manager called "etime_to_locss.txt". When I open the "etime_to_locss.txt" connection manager, and click on Preview, it shows the Hire Dt column as "yyyy-mm-dd 00:00:00".
This doesn't really help. All you are showing me is the query you run in a SQL Command text. I still don't see what you see which is the actual SSIS package, the tables queried by the SQL script, the file you are creating on output. Anything I suggest is just shots in the dark with little chance of really helping.
That's why I said SSIS packages is one of the hardest things to help with on a forum.
October 9, 2014 at 4:49 pm
SSIS can be rather quirky. Did you initially not do the conversion in the select query within the data source? And then changed it after realising you need to? That may cause an issue with the meta data. Try just deleting and recreating the dataflow as you have it now and see it that fixes things here. Ensure of course in the meta data for the pipeline, that you see a string for your date representing column.
----------------------------------------------------
October 10, 2014 at 12:06 pm
I try not to use SSIS for Data conversions.
We usually just Import into a staging table then do all the data massaging in SQL.
Also if you create Stored Procs to do this, they are a lot easier to maintain than be modifying SSIS packages frequently.
October 10, 2014 at 2:15 pm
Data conversions are a norm in the ETL world. Overall SSIS handles them great. The OP did the right thing by doing the conversion in the data source adapter with a sql statement. This lets the SQL Server engine perform this rather than SSIS. So substituting a stored proc here is no different in this regard, except they are easier to maintain than opening an SSIS pakage to see the code.
I should note that creating SPs does involve documentation and source control (at least where I'm at here) on top of that already needed for the SSIS package.
----------------------------------------------------
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply