is this possible in SSIS?

I got a OLE DB source pointing to 1 table

and 1 flat file destination.

currently this is how i export data from 1 table to 1 flat file.

To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.

anyone can help me?

Unfortunately not because the metadata i.e. the columns involved in the transform need to be the same. So you can't have 1 data flow in a loop that is reconfigured for different tables and destinations.

If the source is SQL you could use BCP to produce the flat files but this then really isn't SSIS. Although you could run the bcp from within SSIS.

|||

i was wondering if there can be a conditional loop in between the OLE DB source and the flat file.....

like if the table name is A then go to File destination A

if table name is B then go to File destination B etc....

hope someone understands what i m saying.

|||

You can direct rows to 1 of multiple destinations based on characteristics of the row. This is done usnig the Conditional Split transform. You should look into that and see whether it will do what you require.

I don't know why you used the word "loop". There is no notion of looping in a data-flow.

-Jamie

|||

no tats not what i want....

I trying to backup many tables into many text files using 1 SSIS package project. Is that possible?

trying to reduce the no. of SIS packages file i need to maintain.

|||

Why not have 1 package containing many data-flows?

-Jamie

|||

I got a OLE DB source pointing to 1 table
and 1 flat file destination.
currently this is how i export data from 1 table to 1 flat file.

To make things easier, I was wondering whether i can have only 1 OLE DB source pointing to few tables pointing to few file destinations so I dun need to create 1 SIS project for each table data exporting.


anyone can help me by posting a screenshot of how this can be done in SSIS....the data flows diagram i m not very sure...cos i just started using SSIS in SQL Server 2005.

any guides to SSIS will also be appreciated. Thanks!

i tried using 1 ole db source + file A

and 2 ole db source + file B separated in the Diagram but when i execute it , it doesnt run :(

|||

Ah ok. You can't do this, as Simon explained earlier!

-Jamie

|||

ok Jamie, lets look at it the other manner

I got a table on database server -> export to text file -> import to my local database....

I m doing this task several times...

can this be run consecutively in a data flow diagram? i tried but its not working.....cos of concurrency issues i guess.

can some expert enlighten me?

|||

You can run them all in the same data-flow (in which case there will be as many source and destination adapters as there are tables you are moving data from) or concurrently in seperate data-flows.

-Jamie

|||Be aware that even if you have 20 sources and 20 destinations they may not all run at once. SSIS has a process that determines the threads to use and the amount of concurrency. If running on a 1 proc machine you will get very different results that running on a 4 way machine.|||hmm so simon...what do u recommend?|||

Brohans,

In this scenario its really hard to make a recommendation. These are your options where you have N tables that you have to move data from:

1) Have 1 data-flow that contains N source adapters going to N destination adapters

2) Have N data-flows, 1 for each table. Run them all in the same package

3) Have N packages

Its generally accepted that option #1 will be quicker when N is fairly small (e.g. 4 or 5 tables. I wouldn't like to speculate as to what will be quicker when (e.g.) N>25, I would guess at option #2 but that's only a guess. Option #3 probably isn't a goer. The amount of hardware will come into play here whatever you do. Perhaps this will help: http://blogs.conchango.com/jamiethomson/archive/2005/10/02/2227.aspx

To be honest, the only person that can answer this is yourself. Test and measure,Test and measure, Test and measure...

And let us know how it goes cos this could be really interesting.

-Jamie

|||

great, my boss says he wants the individual packages which i just make to be used in

creating 1 entire database......like program them in sequence so that the data gets into

just nicely into tables which has foreign keys constraints....

i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......

SSIS is really a pain ...arghhhhhhh

|||

brohans wrote:

great, my boss says he wants the individual packages which i just make to be used in

creating 1 entire database......like program them in sequence so that the data gets into

just nicely into tables which has foreign keys constraints....

If you want to execute things in a defined order then put everything in seperate data-flows and make sure they execute in that defined order using precedence constaints. There is no need to have more than 1 package.

brohans wrote:

i just beginning to figure out SSIS....how do i configure the file path for the data in each of these packages and how do i link these packages together......

The file path can be made dynamic through the use of expressions. For example, put an expression on the connection string of the flat file connection manager.

brohans wrote:

SSIS is really a pain ...arghhhhhhh

Why is it a pain? So far you haven't got a requirement that cannot be achieved.

Is it SSIS that is a pain or the fact that you're still learning how to use it? Its a hugely powerful tool but because of that there is a learning curve - I am confident you'll like it when you know how to fully leverage it. Like any technology it takes time to learn it properly.

-Jamie

Is this a silly way to use SSIS?

I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.

I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.

My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.

I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?

Thanks in advance for any info.

I'm a bit biased but in my opinion if you're moving data about then you should use SSIS. There's no point in building an application when you have a product available to do the work for you!

-Jamie

|||

Thanks for the feedback Jamie, I've read a lot of your posts so I will take what you say very seriously. I guess about the only thing I can think of to reply is that if my client decided tomorrow to scrap SQL Server for MySQL or Oracle or DB2 (all of which they have and run at various locales in the organization) then a properly written VB app could be modified slightly and keep on ticking. But if they scrap SQL server (and hey, with the way acquisitions, etc. work these days it's not beyond the realm of possibility) then they would have to start from scratch with their ETL process. But, I guess that would really argue more for a 3rd party ETL tool than for a special purpose written app.

Thanks,

Steve

|||

Note that your client's company could choose to use other database storage engines, but continue using SQL Server Integration Services, especially in light of the favorable pricing (for SQL). We knew of companies that purchased SQL licenses in the past just to get DTS...given the increased power of SSIS, this may become even more common (although we'd love to see everyone taking advantage of the entire SQL package, of course).

|||

Absolutely, SSIS has no reliance on SQL Server.

Its also worth saying that if you were to change your RDBMS its unlikely that your SSIS packages would be too affected - assuming the metadata of the tables is the same. In certain (lucky) scenarios the only thing you might have to change is a connection string. And if you're using best practice of storing connection strings external to your package then that is incredibly easy

-Jamie

Is there anyway to do Data Mining model test via SSIS package?

Hi, all here,

I am wondering if there is any kind of ways for us to test data mining models via SSIS package? That'll be quite helpful if there is such a way.

Looking forward to hearing from your guidance and thanks a lot in advance.

With best regards,

Yours sincerely,

I would believe there is, but nothing immediately straightforward. The approach I would take is to use SQL Profiler to capture the query sent to the server when the accuracy chart is created. You could then put that query into SSIS and capture the results. The response to the query is (pretty much) just the coordinates for the chart you see, so they are easy to interpret.

|||

Hi, Jamie, thanks a lot for your guidance.

I was thinking about if there is any possible data flow component in SSIS for data mining models verification straightforward like what they got for 'data mining model training'.

Best regards,

Yours sincerely

Is there any way to deploy an SSIS Package (File Mode) to an App Server without SQL Instance?

We would like to deploy SSIS packages as an ETL Tool to an appserver that does not have SQL Server 2005 installed. Is this possible, or does it HAVE to be executed on the server with SQL 2005 installed?

Kevin
SQL Server (the database engine) doesn't have to be installed. However, you will need to install the SSIS full client (from the SQL Server installation media) and have a valid SQL Server license for that machine.|||

What Phil said is true that you can do this, however, there are other considerations on why you may not want to.

One main reason is if you are moving large amount of data through SSIS. If you have it on a seperate machine:

[Source Machine] - - - large amount of data - - > [SSIS machine] - - - large amoutn of data - - > [destination machine]

VS

[Source Machine] - - - large amount of data - - > [SSIS/Desitination machine]

As you can see, you will be moving large sets of data accross your network twice.

Is there any easy way to evaluate complex date logic in expressions?

Hello all,

I am new to SSIS, so I am hoping there is an easier way to do this...

I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:

IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)

Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:

(DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2

Any help you all could give would be appreciated.

Thanks!

Josh

I've just given this a go with a column called [OrderDate] (which comes from a SalesOrderHeader in AdventureWorks). The following works fine for me:

OrderDate < GETDATE() ? "Y" : "N"

Can you not do that? Why are you trying to parse out the different parts of GETDATE()?

-Jamie

|||This expression will determine if the DUE_DATE is in the current month, as defined by GETDATE().

YEAR(GETDATE()) + MONTH(GETDATE()) == YEAR(DUE_DATE) + MONTH(DUE_DATE) ? "value1" : "value2"

|||

jaegd wrote:

This expression will determine if the DUE_DATE is in the current month, as defined by GETDATE().

YEAR(GETDATE()) + MONTH(GETDATE()) == YEAR(DUE_DATE) + MONTH(DUE_DATE) ? "value1" : "value2"

I would suggest using :

(YEAR(GETDATE())*100) + MONTH(GETDATE()) == (YEAR(DUE_DATE)*100) + MONTH(DUE_DATE) ? "value1" : "value2"

That will result in 200612 for Dec 2006.

|||

Hi Jaimie,

Thanks for the advice. However, I need to be able to tell if the due_date is between the 1st and last day of the currernt (or any given) month. I think your suggestion would return true for all orders with a due_date less than today... which would exclude orders due in the remainder of the month and would include orders due prior to the beginning of the month.

Best regards,

Joshua

|||

Hi Tom,

Thanks for the help! I think that will do what I'm looking for and will be a lot easier...

Best regards,

Joshua

|||

jrbarker33 wrote:

Hi Jaimie,

Thanks for the advice. However, I need to be able to tell if the due_date is between the 1st and last day of the currernt (or any given) month. I think your suggestion would return true for all orders with a due_date less than today... which would exclude orders due in the remainder of the month and would include orders due prior to the beginning of the month.

Best regards,

Joshua

Whoops. Sorry. I need to read things more accurately!

Tom's code is definately the way to go then.

-Jamie

Is there an example to accomplish the following?

I am new to SSIS and I have been playing with it for the last couple of days. I would like to know if anybody can give me some feedback or point me in the right direction ( best by looking at an example ) to acomplish the following :

I have a table called Policy ( free foxpro table ). This table contain policy insurance information and also customer information. I would like to upload and normalize this table to an existing "PolicyAttributes" SQL Server 2005 table.

Also I would like to create a new record in the Customer table if there exist a customer in the policy Table, thus separating the customer information from Policy table ( normalizing the table ).

The policy table has a field called CustomerName_1 if it is empty then this policy does not have any cutomer, but if it has a name then I would like to insert a new record into the SQL Server Customer table and then get the unique number from the newly created record in Customer table and update it in the SQL Server Policy table ( let say CustomerID ). This way I can keep the relationship PolicyAttibutes >Customer on the CustomerID field.

So basically here are the logical steps:

1.- Read Policy records from foxpro table

2.- insert record to SQL Server PolicyAttributes table

3.- for each customer name in the foxpro Policy table insert a new record in the SQL Server Customer table.

4.- Get the unique number from the new Customer record ( Primary Key of Customer record)

and update this number in the PolicyAttibutes field called CustomerID ( see above step 2 ).

This way I can keep my PolicyAttributes->Customer relationship.

Also the foxpro Policy table can contain more than one customer. It can have up to 4 customers.

So if there are 4 customers then I will have to insert 4 records in the customer table.

CustomerName_1

CustomerName_2

CustomerName_3

CustomerName_4

Any help is greatly appreciated!!!!!!!!!!!!!!!!!!!!!!!!!!!!.

Thanks,

Sergio

I yhink the simplest way you can resolve your problem is:
1. With Import/Export Wizard copy foxpro records in and SQL table named SQL_Policy. Save the procedure as a package in a job to run time to time.
2.Create an "on insert" trigger on SQL_Policy and do what you want to do. Here you can use try/catch for errors, cursors etc. all in T-SQL

This procedure thinking you are new in SSIS.|||

Sergio:

I am not sure that you are going to find an example specific to your situation. If you are new to SSIS but have done programming before I would start of by first creating a logical flow of your data in Visio. You would then know what kind of transforms you would need. You can then look at the SSIS pallette and choose the ones appropriate for your situation. If you need help in using a particular transform use Books Online to get more information.

I apologize if I am not giving you the specific solution you are looking for but, as I said, every flow is going to be different.