derived column transform (flat file blanks to 0)

Hi,

Is it possible using derived column transform to change all blank values in a flat file to say a "0"

Basically convert "" to "0"

Thanks for any help,

Slash.

Hi,

I think you can generate your derived column like this :

([source_column] == "") ? "0" : [source_column]

Arno.

|||

Top man,

Worked a treat,

Thanks again,

Slash.

Derived Column / Change date format

Hi,

Is there a way using a derived column transformation to change the format of the date eg:

The input flat file's date is in yyyy/mm/dd and i need to convert / change this to yyyy/dd/mm

Is this possible?

Thanks in advance,

Slash.

Substring and concatenation, perhaps?|||.. would work because it is not really a date is it? It is just a string. Worth bearing in mind since display of dates is really a client issue not a SSIS/Server issue, unless you are taking a string and converting to a date type. Then SSIS will use the "client" as in user account settings that the process runs under when interpreting the string.|||

The weird thing is that the original flat file date is all yyyy/mm/dd but when it comes to import into the database as datetime. It inconsistently changes the format of the date some dates appear correct and some appear as yyyy/dd/mm. Is there anyway to over come this / have you heard of anything like this before?

Thanks for the replies,

Slash.

|||

slash85 wrote:

The weird thing is that the original flat file date is all yyyy/mm/dd but when it comes to import into the database as datetime. It inconsistently changes the format of the date some dates appear correct and some appear as yyyy/dd/mm. Is there anyway to over come this / have you heard of anything like this before?

Thanks for the replies,

Slash.

The database isn't returning multiple formats once inserted... That is, when doing a select from a database table, you aren't seeing multiple formats in a datetime field. It's impossible. If the destination field is a character field, and you're converting to datetime before inserting, then perhaps you have some invalid dates that cannot convert.

To my knowledge, without converting to a string, and using the substring function, you will not be able to get the format you desire.

derived column - lots of columns-automate?

I'm sending a lot of columns through my derived column transform, checking for empty strings from a flat file - I was wondering is there a way that I could "script out" all the transforms instead of enduring this click hell that I'm stuck in inside the derived column transformation editor. I've got probably 100+ columns to configure with the following sort of transform....

Replace Col1

TRIM(Col1) == "" ? NULL(DT_WSTR,2) : Col1

Basically - if the string is empty, then throw Null in the data stream. I'm about a third the way through but it would really be nice if there was a quicker way. Even with the most efficient copying & pasting & keyboard shortcuts, it's still painful.

Using a script component, you can loop through the columns and perform this operation. Here's a few samples:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

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

The top one requires a little more coding, but is more flexible. The other uses reflection, which is slower but requires less code. Or you could use something like this, a slimmed down version of the first one that only handles strings.

Code Snippet

'Note: this code was originally written/posted by the SSIS forum user, jaegd. http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=133544&SiteID=1

'Credit has been given where credit is due

'Original post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=864401&SiteID=1

'Trimmed to bare minimum code by jwelch

Imports Microsoft.SqlServer.Dts.Pipeline

Public Class ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim counter As Integer = 0

For counter = 0 To inputBuffer.ColumnCount - 1

If (inputBuffer.GetString(counter)) = "." Then

inputBuffer.SetString(counter, Chr(0))

End If

Next

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

End Sub

End Class

sql

Derived Column - Date Conversions?!?

It was my understanding earlier that -- it is mandatory to have a Derived Column for a DATE String in a Flat File
to be loaded properly into a table with "datetime" column type

However, i tried running my package with the Input Column as "DT_STR" and the Destination on the table is DATETIME.

Looks like the process went on fine.. Am i missing something here?

My Date Format from the source looks like -- "DD-MON-YY"

Regards
As long as it is in a format the DB understands, going from DT_STR to DateTime is an implicit conversion. So it's converting as it is inserted.

Deployment options help!

Sorry I am a newbie in SSIS.

I have got a small project that requires to feed in a .CSV flat file and load the data into SQL server 2000.

I developed a SSIS package for this and get it working in my computer, but I need to deploy it to customers that they don't have VS 2005 or SSIS installed. May any one of you give me some clues on that?

I played around with the flat file deployment and again it seems only working on my computer as I have everything installed.

Did you read BOL?

http://msdn2.microsoft.com/en-us/library/ms180167.aspx

http://msdn2.microsoft.com/en-us/library/ms137900.aspx

or the SSIS management whitepaper?

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx

or other sources?

http://www.databasejournal.com/features/mssql/article.php/3600201

http://blogs.conchango.com/jamiethomson/archive/2006/01/23/2702.aspx

http://blogs.conchango.com/jamiethomson/archive/2006/02/20/2902.aspx

I found these by typing "SSIS deployment" into Google. The help is there if you go and look for it.

-Jamie

Deploy to SQL Server or flat files

I'm in a quandry about how to deploy my SSIS packages and was hoping someone has some experience of the same.

There are 2 approaches: 1) Deploy to SQL Server. 2) Deploy to .dtsx files.

I suppose the default approach is to deploy to SQL Server however I have a problem with that. My packages contain Execute Package Tasks which use different connection managers depending on whether the package that they are calling is on SQL Server or in a .dtsx file.
That means that my packages have to contain 2 connection managers for each Execute Package Task - one for pointing to a package deployed to SQL Server and one for a package that is still in development and hence sitting in a .dtsx file. There is also the overhead of managing the deployment to make sure my Execute Package Tasks are pointing to the same place.

For this reason I am contemplating just leaving my packages as .dtsx files even when they are deployed to a production server.

Has anyone else encountered this problem? How have you dealt with it?
Has anyone found a distinct advantage of one approach over another?

Any comments welcome.

Thanks
Jamie

On a side note, what is the performance impact on FF deployment compared to DB?

|||Here were the responses I got from project REAL team. Wish they'd throw their stuff on the web for everyone to see how they did it.

---

From: Gary Cabana
Sent: Tuesday, September 06, 2005 04:13
To: Grant Dickinson
Subject:

Grant, for project REAL, you have one master workflow package that calls execute package tasks with file based connections, correct? How does this work for migrating the code from a development machine to the production box? In particular, when the package starts, are you using a configuration to set the connection string for the execute packages’ file based connection, or is it implemented such that the file locations of the packages being called cannot change?

thanks

---

From: Grant Dickinson
Sent: Tuesday, September 06, 2005 12:33 PM
To: Gary Cabana
Subject: RE:

Hi Gary

We originally had the file locations in the config as you have mentioned, but we realised that in our case we could use a convention of always having the files in the same place, eg c:\etl - thus the configs were redundant and I removed them. If you cannot use such a convention then the config worked fine and I'd recommend that - remember to secure the folder containing the packages if need be. I cannot comment on any experiences using the SQL store for the packages since we have not used that option in REAL.

Kind Regards

Grant Dickinson

---

From: Gary Cabana
Sent: Tuesday, July 26, 2005 13:44
To: Grant Dickinson
Subject: RE: Project REAL questions

One more question:

Did you use multiple packages or combine all the ETL into one giant package with multiple data flows? I’d like to use multiple packages so that development can be done in parallel.

If multiple packages, did you do the execute package in sql server or execute in the file system and why?

From: Grant Dickinson
Sent: Tuesday, July 26, 2005 5:09 PM
To: Gary Cabana
Subject: RE: Project REAL questions

Multiple - as with any code project I prefer modularity. As you say, this makes team development easier too. Currently I am using the file system, no particular reason except that it was easier since the VS project is file-based, as is SourceSafe. Let me know if you find any interesting reasons to use SQL instead.

---

From: Gary Cabana
Sent: Tuesday, July 26, 2005 5:13 PM
To: 'Grant Dickinson'
Subject: RE: Project REAL questions

The dev group said performance was the same.

One thing I don’t like about the file approach is that you need to create a new connection for each execute package you’re executing. Although, the current CTP 15 has the same problem for creating a new connection for each package that resides in the sql server (although Kirk said it’s a bug and I submitted it).

thanks

|||Gary,
That's superb stuff, thanks very much. If its good enough for Project REAL then its good enough for me!!

-Jamie|||The performance impact should be minimum.

The location of the package only affects the load time (i.e.
load from file vs. establish SQL connection and load from SQL table),
it does not matter during execution. For typical packages that run
several minutes to hours, the time to read package from file or
from database (less than a second, unless the server is really
overloaded) should not impact anything.

I think the decision should be driven by administative habits
and convinience. E.g. many people prefer SQL deployment
since they already backup SQL database regularly, and thus
get SSIS package backup for free. But many people backup
file system as well and prefer simplicity of file deployment.|||Package Access is another consideration when deciding the deployment option. Once Package is deployed on Sql server, User Access to the package is controlled at the database level. Whereas in the oher case its filesystem level.

For a scenario where many users are executing a package, having it on a centralized SQL server is just more simple. Esp. in case SSIS package connects to multiple databases. Its more secure rather than having connections set in a package saved on file system.|||

Suresh Bansal wrote:

Package Access is another consideration when deciding the deployment option. Once Package is deployed on Sql server, User Access to the package is controlled at the database level. Whereas in the oher case its filesystem level.

For a scenario where many users are executing a package, having it on a centralized SQL server is just more simple. Esp. in case SSIS package connects to multiple databases. Its more secure rather than having connections set in a package saved on file system.

All good reasons. However the issue regarding deployment that I mentioned earlier swings it.

Thanks all.

-Jamie