LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » SQL Server Database Administration » Integration Services (SSIS) and DTS » SSIS or SSRS

SSIS or SSRS

Options

Previous Topic · Next Topic

Poppey

Posted: Wednesday, August 04, 2010 9:05:11 AM

Rank: Newbie

Joined: 8/24/2009
Posts: 4
Points: 12
Where do you live?: Buffalo, NY

I need to create a process where on a weekly basis the process will extracts data from SQL Server, format that data into a particular format (HL7) then transmit that formatted data via FTP.

It sounded like SSIS to me (straight up ETL) but I don’t see how to format the data into HL7 format (a hierarchical format) in SSIS. Unless I somehow worked the ‘FOR XML’ clause into the SQL statement to extract the data but that seemed like the wrong direction.

I starting thinking that I could create a batch file in SSRS. I think I can probably put one together but there isn’t an FTP option useless I create a custom delivery extension (which I don’t know how to do).

Have you you done anything like this before and which direction would you choose? Or would you take a different direction all together that I didn't think about.

Scott Whigham

Posted: Wednesday, August 04, 2010 9:11:37 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hmmm - I'm not familiar with HL7 but I just looked it up and it seems fairly easy to do what you want w/ SSIS, I think. SSRS would not come to my mind though since I don't have as much control over the hierarchy. If I'm understanding the HL7 formats correctly, I would just use a data flow task w/ a text file destination.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.