Showing posts with label ssis. Show all posts
Showing posts with label ssis. Show all posts

Friday, March 30, 2012

I hate this

I'm migrating a fu... dts to SSIS. That's fine. I've got a derived column task for stuff as cdbl(DTSSource("Col014") /100).

But how the source plain file have a lot of columns suddenly I find this:

Function Main()


Fecha=Right(DTSSource("Col014"),2) & "-" & Mid(DTSSource("Col014"),5,2) & "-" & left(DTSSource("Col014"),4)
If IsDate(Fecha) then
DTSDestination("FechaOp") =Fecha
Else
DTSDestination("FechaOp")=null
End IF
Main = DTSTransformStat_OK
End Function

So that 'Derived Column' is useless at all for to encompass this rule unless to anyone be able to tell me how to implement that conditional snippet inside...

I'll be to use a Script Component Task only for that dammed column when the rest ones already defined.

!!!!

Do y'know hare and tortoise tale? So here is the same but in reverse order. jezz|||

There is a conditional operator in the SSIS expression language. It will do what you need to do.

Here's the BOL topic on it: http://msdn2.microsoft.com/en-us/library/ms141680(SQL.90).aspx

-Jamie

|||Thanks Jamie. hurries doesn't provide us good advices..|||

OK.

I've taken as example this and then I've converted into this line:

'Fecha = Right(DTSSource("Col014"), 2) & "-" & Mid(DTSSource("Col014"), 5, 2) & "-" & Left (DTSSource("Col014"), 4)
'If IsDate(Fecha) Then
' = Fecha
'Else
'DTSDestination("FechaOp") = null
'End If

Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing

But fails:

Error at CargaModelo187 [Derived Column [10345]]: Attempt to find the input column named "Column13" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at CargaModelo187 [Derived Column [10345]]: Attempt to parse the expression "Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: Cannot parse the expression "Isdate(Right([Column 13],2) & "/" & Mid([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing". The expression was not valid, or there is an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: The expression "Isdate(Right([Column 13],2) & "/" & M

id([Column13],5,2) & "/" & Left([Column13],4)) ? [Column13] : Nothing" on "input column "Column 13" (11693)" is not valid.

Error at CargaModelo187 [Derived Column [10345]]: Failed to set property "Expression" on "input column "Column 13" (11693)".


Let me know which is the next step or what guidelines must I follow on this issue.

|||

In your expression you refer to "Column13" but the SSIS error message shows that the column in the data flow is in fact named "Column 13"

Donald

|||

A couple other things:

The expression language does not have an IsDate() function. What exactly are you testing for with that? Itstill might be possible, depending what you are looking for...

There is also Left() function -- you should use Substring() for that.

Instead of "Nothing", you need to use the NULL() functions, which are strictly typed.

|||

Mark Durley wrote:

There is also Left() function -- you should use Substring() for that.

Another alternative to the lack of a LEFT() function is RIGHT(REVERSE())

-Jamie

|||Thanks a lot to all of you|||

Well, this follow failing:

Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing

Column 13 on the first row own this value:

20050214

TITLE: Microsoft Visual Studio

Error at CargaModelo187 [Derived Column [10345]]: Attempt to find the input column named "Nothing" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at CargaModelo187 [Derived Column [10345]]: Attempt to parse the expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: Cannot parse the expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing". The expression was not valid, or there is an out-of-memory error.

Error at CargaModelo187 [Derived Column [10345]]: The expression "Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4) ? [Column 13] : Nothing" on "input column "Column 13" (12091)" is not valid.

Error at CargaModelo187 [Derived Column [10345]]: Failed to set property "Expression" on "input column "Column 13" (12091)".


ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)


BUTTONS:

OK

|||

I was wrong although this new line-version fails too...

[Derived Column 1] == Right([Column 13],2) & "/" & SubString([Column 13],5,2) & "/" & SubString([Column 13],1,4)

Thanks in advance,

|||

It's already solved.

Instead of & you must use + and drop '[Derived Column 1] =='

RIGHT([Column 13],2) + "/" + SUBSTRING([Column 13],5,2) + "/" + SUBSTRING([Column 13],1,4)

IsDate function is not necessary because of I saw concretly in that file that all of them have the same structure yyyymmdd

|||In any case what function is intented do the same that ISDATE?|||

There is no function that can do IsDate. As Mark suggested there may be possibilities using other functions - checking that a string has certain characters and that substrings, can be cast to valid integers - but that expression would likely be rather complex.

Script component does all this really nicely, of course.

Donald

Friday, March 23, 2012

I can't open my dtsx

Hi everyone,

After a while I'm come back to SSIS but a drawback appears.

I’ve copied from our server a folder which contains a few dtsx on my local folder and when I’m going to open dtproj and then choose any of them:

Error1Error loading 'M_Hac_Modelo187.dtsx' : Error al recuperar un generador de clases COM para el componente con CLSID {E80FE1DB-D1AA-4D6B-BA7E-040D424A925C} debido al siguiente error: 8000401a..c:\ssis\hacienda_anual\M_Hac_Modelo187.dtsx11

..

..

..

(I think that is not necessary to translate into english due to simpleness of the message.)

What the hell is happening? I perfectly remember have been doing the same a month ago and I was able to open them without problems.

TIA

It's strange. How odd.

Now, I can't be able to create any SSIS project from my BIDS. SSAS no problem at all.

In the last month I've installed Visual Studio Professional Edition 2005 and Enterprise Library January Edition 2006 for Framework in my own workstation.

What happened?

I don't want that workaround for it have to be install again SSIS component from my installation CD.

Let me know any comment or link related.

Thanks again,

|||

Hi again,

Does anyone ever faced any problem like that?

Following actions has been commited:

-Desinstalling assemblies related (Microsoft.SqlServer namespace) and installing them again - UNSUCCESSFUL

-Reinstall SSIS component - UNSUCCESSFUL

-Repair Visual Studio 2005 Professional Edition installation - UNSUCCESSFUL

-A full desinstallation of Visual Studio 2005 Professional Edition - UNSUCCESSFUL

-A full installation of Visual Studio 2005 - UNSUCCESSFUL

-A full desinstallation of Sql Server 2005 Developer Edition - UNSUCCESSFUL

I've searched that CLSID in my registry and it belong to C:\Archivos de programa\Microsoft SQL Server\90\DTS\Binn\DTS.dll

So that, Package Neutral and Package Neutral Classes involved...

CLSID: E80FE1DB-D1AA-4D6B-BA7E-040D424A925C
TypeLib: {D1D9A51E-B4FE-420B-97B1-28A383EF9168}

So that, I've unregistered that COM component by means regsvr32 and registered again...

Unsuccessful.

I'm stuck with that. I don't get the point at all.


Any idea?

Friday, March 9, 2012

I am new this SSIS, Please help me.

Hi,

I am working on .Net tech with SQL Server 2000.

I want to update my technical skills to DW.

I mean, i planning to learn SSIS and i am new this topic.

Please help me........

thanks

babu

Two options-

Pay for Books and/or Training

or

Teach yourself, perhaps get hold of SQL Server 2005, perhaps try the free documentation and tutorials? Have you you even looked on the MS site for this?

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

|||

thanks for reply. I am asking is there any free documents and tutorials are available in any free sites.

thanks

Babu

|||

There are tutorials in SQL Server Books Online.

Also, try the followink searches:

http://search.live.com/results.aspx?q=ssis+tutorials&mkt=en-us&FORM=OPNSCH

http://search.live.com/results.aspx?q=ssis+examples&form=QBRE

|||

Sridhar,

Apart from msdn and latest downloadable samples from microsoft site, There are lots of online resources.

1) www.sqlis.com by Darren Green and Allan Mitchell

2) Download Free Samples at Wrox official site

2) Blogs by Jamie Thomson,Rafael Salas, Brian Knight, Donald Farmer, Kirk Haselden, Scott Barret, John welch, Ashwin Sharma, Phil Brammer, Michael Entin, Alberto Ferari

Many Thanks

Subhash Subramanyam

|||I suggest you download tutorial from www.microsoft.com/downloads/details.aspx?FamilyID=B1145E7A-A4E3-4D14-B1E7-D1D823B6A447&displaylang=en.
It's great for the begining.