August 17, 2011 at 4:14 am
Following some great advice on another post I made about importing flat files. I took the leap and started with SSIS packages, probably the best SQL decision I ever made, they are a god send. How anyone lives without their use is beyond me:cool::w00t: 😉
Anyway, the data I import is rough to say the least, two questions;
1) I have tried to use OLE DB command to call a scalar function I created which takes a text input and outputs a valid date (previously I imported a date as text and converted with my function, the dates are often in various formats unrecognised by sql). Is it possible to call my user function with a parameter of one of the input coumns from the flat file? I cannot seem to get this working.
2) I am only scratching the surface so far but cannot file any component for selecting only certain records on the import, I want to omit the date (text) fields which are empty altogether.
Finally, is it realistic to be able to import a non normalised flat file and parse and clean the data then insert the data into my normalised tables using ssis, or is this a pipe dream, even once I have mastered the whole shooting match?
Thanks in advance for any help, I do try and search before posting but found nothing for calling user functions from SSIS.
cheers
jake
August 17, 2011 at 4:28 am
dji (8/17/2011)
Following some great advice on another post I made about importing flat files. I took the leap and started with SSIS packages, probably the best SQL decision I ever made, they are a god send. How anyone lives without their use is beyond me:cool::w00t: 😉Anyway, the data I import is rough to say the least, two questions;
1) I have tried to use OLE DB command to call a scalar function I created which takes a text input and outputs a valid date (previously I imported a date as text and converted with my function, the dates are often in various formats unrecognised by sql). Is it possible to call my user function with a parameter of one of the input coumns from the flat file? I cannot seem to get this working.
2) I am only scratching the surface so far but cannot file any component for selecting only certain records on the import, I want to omit the date (text) fields which are empty altogether.
Finally, is it realistic to be able to import a non normalised flat file and parse and clean the data then insert the data into my normalised tables using ssis, or is this a pipe dream, even once I have mastered the whole shooting match?
Thanks in advance for any help, I do try and search before posting but found nothing for calling user functions from SSIS.
cheers
jake
Good to hear that you are enjoying getting familiar with SSIS.
If you are importing a flat file and have written your scalar function as a SQL Server UDF, I can think of no way that you can invoke it prior to the data having been stored in a SQL Server table.
You might, however, be able to define your function in .NET and use it within a Script Component to process the data.
To throw away records that you are not interested in, I suggest that you investigate the Conditional Split - you should be able to redirect those records you are not using to an output which you ignore (essentially firing the unwanted records straight down the toilet).
The answer to your final question is a qualified yes - though it might not be simple. A combination of SSIS, staging tables and Execute SQL tasks will probably do it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 17, 2011 at 5:15 am
Many thanks for the swift reply. I was trying to run a four minute mile before I could walk I guess!
The conditional split is perfect, just what I needed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply