invalid time entries in import job

[sedgely]

Hi
i have set up a dts job to import data from a csv file, the data contains around 40 time fields, unfortunately until i can get the software that produces the csv sorted some of htese time fields contain invalid times e.g. :15
so until such time as i can get this fixed i need to find any such instances and set these to <null> as part of the DTS job so that i can get the data imported.
Can anyone suggest a way to do this, bearing in mind that there are 40 such fields to check.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JayKusch]

A QD(Quick&Dirty) would be to create a temptable w/ the 40 columns set as VarChar(30).

Import the csv file into the TTable.

Create a script that check each field for a length that should be a valid date field. Maybe something like ...

UPDATE #MyTempTable
SET MyColumn01 = NULL
WHERE LEN(MyColumn01) < 10

Then cut and paste that expample 40 times and change the code to match each of the columns.

Once that is done and executed, you can then try to import from the TTable to your target table. May have to run the clean SP a couple of time before you get all the anomalies covered.

once again this is only a QD approach.



Thanks

J. Kusch

 

[sedgely]

Thanks for that, but i need something a bit easier to mangage, the CSV file is created daily and will need to be imported on a daily schedule, without any intervention.
Think i might just go outside and bang my head against the wall

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JohnDTampaBay]

You can do the same thing as part of the transformation without an intermediate staging table (although the staging table makes debugging much easier). In the Transform Data Task that imports the CSV file on the Source tab, I am guessing that the Table/View option is currently chosen. Select the SQL Query option, then develop a query that selects from the CSV source and translates the bad dates to NULL. Use the Preview to test your query. Something like this, I imagine:

SELECT ColA, ColB, ... --Other non-date columns in table
, CASE WHEN LEN(TimeCol1) < 10
THEN NULL ELSE TimeCol1
END AS TimeCol1
, CASE WHEN LEN(TimeCol2) < 10
THEN NULL ELSE TimeCol2
END AS TimeCol2
...
FROM CSVSource


--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sedgely]

Thanks Angel
Will try that, not today tho' am going home soon

Cheers, Craig
Si fractum non sit, noli id reficere

 

[sedgely]

Angel
Your suggestion &quot;In the Transform Data Task that imports the CSV file on the Source tab, I am guessing that the Table/View option is currently chosen. Select the SQL Query option,&quot;
i have just had a look at this and for some reason i am not able to select the &quot;SQL query&quot; option it is greyed out.
Do you have any other suggestions?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JohnDTampaBay]

Yeah, unfortunately that option isn't available for text files. I suggest using a staging table. All of this can be done through DTS.
[ol][li]Create an Execute SQL task on your SQL Server to test for the staging table existence. If it exists, truncate it; if not, create it.[/li]
[li]Create a Transform Data task to import the CSV file into the staging table.[/li]
[li]Create a Transform Data task with the logic above to import data from the staging table into the destination table.[/li][/ol]

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sedgely]

Cheers Angel
Will try that tomorrow

Cheers, Craig
Si fractum non sit, noli id reficere

 

[sedgely]

Angel
I have now got a working solution to my original problem, i took your advice and created DTS task to bring the data into a temp table and then another task to drop into the proper table and used an ACtiveX script to do the relevant transformations:

Function Main()
	if Len(DTSSource(&quot;tm06TPCPR&quot;))<5  then 
	DTSDestination(&quot;tm06TPCPR&quot;) = Null
	else
	DTSDestination(&quot;tm06TPCPR&quot;) = DTSSource(&quot;tm06TPCPR&quot;)
	End If	
Main = DTSTransformStat_OK
End Function

However this has now brought to light another slight problem that i'm not sure how to get round...
There are a small number of entries in the time fields that are invalid because they are outside a normal time range e.g. 56:17 i know the ideal would be to get this corrected at source, which we are working on, but in the meantime i need to record these as <null> in the table.
i have tried to amend my code to

Function Main()
	if Len(DTSSource(&quot;tm06TPCPR&quot;))<5 [b]Or TimeValue(DTSSource(&quot;tm06TPCPR&quot;))>23:59[/b] then 
	DTSDestination(&quot;tm06TPCPR&quot;) = Null
	else
	DTSDestination(&quot;tm06TPCPR&quot;) = DTSSource(&quot;tm06TPCPR&quot;)
	End If	
Main = DTSTransformStat_OK
End Function

but this does not work, i assume it is because the field is a text field.
If you have any ideas on how to check for a valid time i would be extremely grateful.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JohnDTampaBay]

I am afraid my VB skills are not the best. I'm not sure how to help you. If there an ISTIME function in VB? SQL Server can implicitly convert some text or char values into other datatypes, but I don't know if VB will.

If you have a step that imports the data from a CSV into a SQL staging table, how does my first suggestion work:

SELECT ColA, ColB, ...   --Other non-date columns in table
  , CASE WHEN LEN(TimeCol1) < 10
           THEN NULL ELSE TimeCol1
    END AS TimeCol1
  , CASE WHEN LEN(TimeCol2) < 10
           THEN NULL ELSE TimeCol2
    END AS TimeCol2
...
FROM SQLStagingTable

I like this method because I can use Preview to see exactly what will be imported into the destination table. It takes away a lot of guess work.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sedgely]

Angel
Thanks for replying, i tried your solution, the only problem is it that it will not pick out those times that have been entered incorrectly such as &quot;56:17&quot;.
I will persevere with this, i'm something will 'click' shortly. Thanks again

Cheers, Craig
Si fractum non sit, noli id reficere

 

[JohnDTampaBay]

Is the value 56:17 the only thing in the incoming field? What about trailing spaces? What is LEN(TimeCol1) for 56:17? Perhaps LEN(RTRIM(TimeCol1)) would work better? Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[sedgely]

Angel
I knew something would 'click' reading your last post gave me some inspiration and i think i have got the solution...

Function Main()
	if Len(DTSSource(&quot;tm06TPCPR&quot;))<5 [b]Or Left(DTSSource(&quot;tm06TPCPR&quot;),2) > 23 or right(DTSSource(&quot;tm06TPCPR&quot;),2) > 59 [/b] then
	DTSDestination(&quot;tm06TPCPR&quot;) = Null
	else
	DTSDestination(&quot;tm06TPCPR&quot;) = DTSSource(&quot;tm06TPCPR&quot;)
	End If	
Main = DTSTransformStat_OK
End Function

that appears to work, just need to give it a thorough testing.


Cheers, Craig
Si fractum non sit, noli id reficere