SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ETL Magic with SSIS – Part I


ETL Magic with SSIS – Part I

Author
Message
marko.stelzner
marko.stelzner
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 89
Hello Kevin,

when I have to import some unknown flat files, I change the max length of each column (string) to 8000 (see picture). After that, I can transform the data or migrate, as I need it. Now I have the possibility of a simple error handling.

Unfortunately you need here two tables - one for reading and of course the real aim ...
Attachments
flatfile.jpg (12 views, 69.00 KB)
spam.protected.account
spam.protected.account
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 4
Back in 2004 we looked into MS offerings in the EDI space and figured out that nothing (including BizTalk) was addressing our reliability requirements, so we started our proprietary software development and never looked back.

Out of curiosity I looked into this article and OMG, a simple case of reliable FTP download (not sure it does SFTP or uploads) is not even a part of a framework? How on Earth in the real world are you going to deal with situations when the file has not fully arrived into the source folder yet or they put it there twice by mistake simply because you already picked it up before they saw it? How are you uploading the files to (S)FTP? Does that have a chance to break your client’s EDI because they fell into the same trap?

For our software to run you can use Pentium 3 and no SQL server is required! It has agents and APIs that can be deployed to the client because some clients may be just desktop applications with no EDI capabilities. The other way for this sort of clients is sending attachments to by email.

I understand that MS needs to sell a product that is extremely configurable, but reading this article it seems like even a simple ETL is quite a lot of work and workflows. The advantage of our proprietary software is that if something has to be a part of the framework, we put it there, so over the years we ended up with an extremely robust framework. That is also a reason why we have not productised it. Instead we provide a VAN. The rationale for our clients is that they need no hardware, employees with associated costs (including compliance costs), and time to the market are days, not months!


Have a read at http://allelectronicmessages.com/B2B_Messages.aspx?r=s07
DJH-445911
DJH-445911
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 195
I am trying to follow this and am getting an error regarding my connection string. I have set the environment variable and set the package configuration to use it but I get:

'The connection string format is not valid'

It is the same connection string that is in the properties of my connection manager so I am not sure why it doesnt like it.

Anyone else get this?

Thanks in advance...
kev4king2000
kev4king2000
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 36
Thank you very much for the wonderful article.

I am able to download and installed the File Watcher Task successfully but when I clicked the ellipsis path button in the file watcher task to take me to where my file is located, then I get the following error:

Could not load file or assembly 'Microsoft.Data TransformationServices.Design, version=9.0.242.0, Culture=nuetral, PublicKeyToken=89845dcd8080cc91' or one of its dependences.

The located assemply's manifest defination does not match the assembly reference.

(Exception from HRESULT:ox8013040)

Any help how to resolve this problem or is there any other task I can use as a replace?

Thanks
kev4king2000
kev4king2000
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 36
Thanks a lot for the such a wonderful article.
I am able to download and installed the File Watcher Task successfully but when I clicked the ellipsis path button in the file watcher task to take me to where my file is located then I get the following error:

Could not load file or assembly 'Microsoft.Data TransformationServices.Design, version=9.0.242.0, Culture=nuetral, PublicKeyToken=89845dcd8080cc91' or one of its dependences.

The located assemply's manifest defination does not match the assembly reference.

(Exception from HRESULT:ox8013040)

Any help how to resolve this problem or is there any other task I can use instead of the File Watcher Task?

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search