Dynamically Pick The Source And Destination Tables

Feb 27, 2007



I want to write a SSIS which picks up the source and destination tables at runtime is it possible. As we have a SSIS which is used to pull data from oracle but the source and destination table name changes.

View 5 Replies


ADVERTISEMENT

Dynamically Selecting Tables In OLEDB Source

Mar 17, 2008

hello guys,

I have 10 tables, table1, table2, table3, table4.......table10. all these tables have different structure.
From each of these tables I want extract data and dump into flat file csv.

So i have OLEDB source and FlatFile Destination.

If i write seperate data flow task for each of the tables, then there is no issue.

But i want to use a single data flow task for all these tables. So for this, i use a variable @SQLStr . And i dynamically set the value of this variable to select * from table1, select * from table2.........selct * from table10.

So in the OLEDB source I select Data Access Mode as : SQL Command from variable. And I use @SQLStr for this varible.

For Destination, i dynamically generate the flat file for each of the table.

But this doesnt work, i get validation errors.

So, first can i use a single data flow task to dynamically change the source(different source tables) and destination. If so, what i am missing in the above process?

any help appriciated.

Thanks

View 8 Replies View Related

Importing Data From One Source In Two Destination Tables Linked By A Foreign Key

Nov 14, 2006

Hi,

I have a new problem when I import data from an xml source file in two destination tables. The two tables are linked by a foreign key... for example :

table MOTHER (MOTHER_ID, MOTHER_NAME)

table CHILD (CHILD_ID, MOTHER_ID, MOTHER_NAME)

After a lot of transformations data are inserted into MOTHER table and I want to insert other fields of the data flow in CHILD table. To do this, I need the MOTHER_ID field that is auto incremented in MOTHER table.

My problem is to chain the insertion in CHILD table after the insertion in MOTHER table to be sure that the relative row in MOTHER table is really inserted. I haven't find any solution to chain another transformation task after my flow destination "Insert into MOTHER table".

The only solution I have found is to create a new flow control to insert data in CHILD table, using a lookup transformation task to bind with MOTHER table... But with this solution all my flow control transforms are made two times...

Is there a solution to chain two insertions with a foreign key constraint in a data flow?

Thanks

Regards,

Arnaud Gervais.

View 4 Replies View Related

How To Replace Data Of Tables From Source To Destination Database Using SSIS

Apr 29, 2008

I would like to replace data of some tables from STG to DEV database daily using SSIS package. Should I use "Transfer SQL Server Objects Task" to do that? Thanks.

View 5 Replies View Related

DTS - Need To Set Destination Filename Dynamically

Nov 24, 2006

I have a stored procedure that (after selecting, incrementing and updating)returns the next sequential filename.I haven't been able to plug it in to the destination textfile filename in myDTS package.I know it has something to do with Dynamic Properties and I can navigate tothe property I want to set, but just can't seem to get the result of mystored procedure into the filename.I know somebody must have done this before.ThanksJoe

View 1 Replies View Related

Changing Dynamically The Destination Name In Ssis

Apr 15, 2008

i am transfering the table from one database to csv file format..i did it.. again i want to shift that csv files to another databse as tables. how to do this task.. pls help me.. its very urgent..out TL had given me the dead line.. send reply soon....

View 5 Replies View Related

Dynamically Create Text File As Destination

Feb 16, 2007

I am trying to create a text file from an SQL query on a SQL table. I would like the SSIS package to prompt for the file name and path. The text file is tab delimited and the text qualifier is a double quote.

Thanks,

Fred

View 9 Replies View Related

Create Destination Table Dynamically In A Program

Feb 17, 2007

HI,

I'm programmatically able to import data between tables when the Destination table already exists but when Detination table has to be created on the fly (Name will be provided), I'm not successful in doing so.

Basically the requirement is to dump the resultset from the source in to a temp table so that the temp (Destination) table matches the Source's Schema exactly.

Has anybody done that?

Any help in this regard is greatly appreciated.

Pavan



View 3 Replies View Related

How To Dynamically Map The Coloumn To A Flat File Destination?

Jun 5, 2007

Hi All,



I am struck at one point. I am trying to this operation and not able to go further.



1. I have got the dataset to a variable in the control flow.

2. I am looping through the dataset based on a coloumn.

3. Now inside my For each loop i have a dataflow task.

4. In the data flow task i am trying to build a dynamic query using the OLEDB Source and i have selected SQL Command from variable. And the variable build the Query as select * from @othervariable.

Now my question is

Can i send the data of each of the Query resultset to an out put text file using Flat File Connection? If yes pls guide me how? I have tried to create a flat file connection but i am failing how to map the data comming from step 4 dynamically for every query, since every query gives you a different resultset with different coloumns.



Thanks in advance..



Regards,

Dev.

View 15 Replies View Related

Dynamically Configure Text File Destination In SSIS

Feb 7, 2008

Good Afternoon,

So this one has been bugging me for a while and I am ready to punt...

Is it possible to dynamically create a text file destination in SSIS and then pump the results of a query stored in a variable to this text file?

So my package looks like this
1) SQL task that pulls back a list of tables to be exported
2) For Each Loop ADO enum that passes the table name to a SQL Task that builds the select...ie select * from <DTS.Variables()>
3) Data flow task that sets the command from variable from step 2
4) Text File destinaiton that is built using a varable as the connectionstring

I am delaying validation in steps3 and 4 above without any luck...basically I am curious if i can even do what I am thinking I should be able to do here...I get as far as getting metadata errors because SSIS can't seem to handle dynamically filling the pipeline with the columns from the variable/SQL statement.

Am I missing something? Is this possible?!

Thanks in advance.
Dave

View 6 Replies View Related

Dynamically Create Text File As Destination From Sql Script In SSIS

Mar 27, 2007



I have a select Script as follows:



SELECT c.ABC AS 'ABC'

, a.Qty AS 'Quantity_Recived'

, b.PC AS 'PC'

, b.PC AS 'PC'

, 'I' AS 'Flag'

FROM TNRInventory.dbo.tInventoryAlloc AS a

LEFT OUTER JOIN vwInventoryAllocMapping AS vwMap ON a.TNRAllocTypeID = vwMap.TNRInventoryAllocID

LEFT OUTER JOIN ABC.dbo.ZREFRESHTAB AS b ON a.DispenserID = b.Asset

LEFT OUTER JOIN ABC.dbo.TableJoinKey AS c ON a.TitleID = c.TITLE_ID

WHERE (vwMap.DataSourceID = 3) and vwMap.[DataSourceAllocName] = 'I'

group by c.SKU_NO , vwMap.[DataSourceAllocName],a.Qty , b.Profit_Center

order by c.SKU_NO,vwMap.[DataSourceAllocName]

GO



i have to send the result of aforesaid script in batch of 300 records per file (tab delimited text file)
now the file name must be dynamically created as each file will contain 300 records.



I have found some document related to same issue on this url

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1238184&SiteID=17

but still there is a catch.



Can any one guide/suggest me better way to do the aforesaid.



Thanks

View 8 Replies View Related

Integration Services :: Load Multiple Flat Files Into Destination Dynamically?

May 29, 2015

how do you load the multiple flat files  to into destination dynamically?

View 9 Replies View Related

FLAT FILE SOURCE DYNAMICALLY

Dec 1, 2007

hi all

I have a table which has the paths where the source files need to be collected.

So I have a set of files which I need to collect. So I need to iterate through the result set which retrieves the paths

where the files can be found and set this dynamically to connection string of flat file connection manager.

Can anyone please let me know how to do this.

Can you be a little elaborate as to how we can do this because I am really new to SSIS.

Please help me as I am new to SSIS.

Thanks

Sai

View 1 Replies View Related

Source Destination Error

Mar 4, 2008

Hi, I need help please!

I get the following error on my OLE DB Destination: column"Oprcode" cannot convert between unicode & non-unicode string data type.
Please Assist on what i should do!

Regards

View 2 Replies View Related

OLE DB Source &&amp; Excel Destination

May 3, 2006

Hi,

My OLE DB Source and Excel desintation values all will be assigned during the run time but it does work during design time but as on runtime columns are different. That's why it does not work.

Here is what I want to accomplish, I have table which contains all my report which needs to dumped to excel at the month end.

SQL Task using ADO enumrator read one record(one report), Give that record to For Each contair which Create the Excel file on the fly using one of variable from my table and uses a stored procedure to dump data to excel using Dataflow Task.

xlsQuery

CREATE TABLE `Sheet1` ( `FiscalYear` Short, `FiscalPeriod` Byte, `STORE #` Short, `Total Markups` Decimal(15,2), `Less Markdown SubTotal` Decimal(15,2), `Total Markup` Decimal(15,2) ) GO

sqlQuery

Exec Report.MyReport 1

Does it mean for 10 reports, I have to create 10 different data flow tasks, or it can be done using one data flow tasks but changing columns on the run time.

Please Help

Thanks

Shafiq



View 10 Replies View Related

Dynamic OLE DB Source And Destination

Mar 27, 2007

Hi,

I am building SSIS for 3 different files that have identical
schema and mapping logic.

In my OLE DB Source (object name - "OLEDBSource_SourceTable")
Data Access mode is "Variable name".
As soon as I swithced to this Data Acces mode
it started to give me an error:

[OLEDBSource_SourceTable [1]] Warning: The external metadata column collection is out of synchronization
with the data source columns.

The column "DEAL_NUM" needs to be updated in the external metadata column collection.
The "external metadata column "DEAL_NUM_Flag" (34529)" needs to be removed
from the external metadata column collection.
The "external metadata column "recordID" (33740)"
needs to be removed from the external metadata column collection.

View 22 Replies View Related

Change Flat File Source Dynamically

May 21, 2007

I am a relative newbie to SSIS. I have been tasked with writing packages to import data from our clients. We have about 100 clients. Each client has a few different file formats. None of the clients have the same format as each other. We load files from each client each day. Each day the file name changes. I have done all of my current development work with a constant file name in a text file connection manager.



Ultimately we will write a VB application for the computer operator to select the flat file to load and the SSIS package to load it with. I had been planning on accomplishing this thru the SSIS command line interface. Can I specify the flat file to load via a variable that is passed through the command line? Do I need to use a Script Component to take the variable and assign it to the connection manager?



Is there a better way to do this? I have seen glimpses of a VB interface to SSIS. Maybe that is a better way to kick off the packages from a VB app?



Thanks,

Chris

View 9 Replies View Related

Can I Export Tables So That Existing Tables In Destination Database Will Be Modified?

Jul 20, 2005

I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett

View 3 Replies View Related

Copy Columns From Source To Destination

Apr 10, 2007

Hi all,

I want to copy 2 columns from 1 database to another database.
I managed to do this, using a Ole DB source and a Ole DB destination dataset.

Now I want to merge 2 colums into 1:
Source Database: Column A: first name, Column B: Lastname.
Destination Database: Column 1: First and lastname customer.

Thanks for your help!

Kind regards,

Marcel Hijnen
eXDe Solutions B.V.

View 4 Replies View Related

Unnormalized Source Into Normalized Destination

Feb 28, 2008

Hi
I am new to SSIS. Probaly What I am asking is the simple one. But I dont know. I have a requirement. My Data source is Excel file and Destination is SQL Server . Excel file consist around 10k unnormalized rows which should be loaded to the multiple normalized master and child tables into the destination with the Primary key (identity column) and foreign key constrains. Even sometimes package should check for destination database table for matching Source column (Excel) code with the lookup table code (destination lookup table) and get the Primary key of Lookup table and put it into the destination table. Please help me the way to find the solution for this

View 5 Replies View Related

Dynamic Mapping For Source/Destination

Aug 8, 2007



Hello,
What I'm trying to accomplish is to have a variable names "SourceTable" and "DestinationTable". So for each SourceTable, the DestinationTable will have the same columns. All I need is to auto-map these columns between source and destination via code?

Is this possible?

Thanks,
awiora

View 3 Replies View Related

Remote Source And Destination Performance

Jan 7, 2007

Given the following scenario, what kind of performance should be expected in transferring about half a million rows? We are seeing about a 9 minute execution time. Is this reasonable for about 460,000 records moving from source to target, with 3 inner joins from the source?

Source: Server A.OLTPDB

Target: ServerA.DataMartDB

Server A is running SQL Server 2000. SSIS is running on a different machine, Server B.

The reason for this is that we are distributing the SSIS package for use with a BI product built on SSAS 2005 and the requirements are such that the client could very well have the source OLTP database on a different physical machine than the data mart.

My understanding is therefore that:

1. SSIS will do all of the heavy lifting on Server B.

2. Even though OLTPDB and DataMartDB are on the same server, it is expensive for Server B to pull the records from Server A and then send them back to Server B, but with SSIS being on a different machine, this is inevitable.

3. In the OLE DB Source Adapter, specifying table or view has the effect of an OPEN QUERY command, whereas a SQL command with straight SQL will be executed on Server B and the former would be somewhat more performant.

Can you guys validate/dispel these assumptions?



TIA,

Rick

View 12 Replies View Related

Source And Destination Table Equality

Feb 20, 2006

I have searched the SSIS forum for an answer to my question, and I think I have found my answer but what i have read does not come out directly and answer my question.

I am attempting to create an SSIS package that imports data from a Visual Foxpro table into a SQL Server table. From what I have read, the source and destination tables must match column for column. Is this correct? My SQL Server table has a few more columns in it. However, i consistently get "Cannot create connector. The destination component does not have any available inputs...blah".

From what i have read, it sound like the reasoning is that my source and destination tables do not match.

Is this correct, or am i barking up the wrong tree?

Thanks in advance...

Scott

View 3 Replies View Related

Update Records: Source And Destination Are The Same

Oct 18, 2007



All,

I am having a brain-fart or something. I need help!!!

I'm developing a transform that selects data from a SQL script, then transforms that data, and then needs to update the data back into the same source table. The transform is working great, but I can't figure out the update table part. I've got it adding the transformed records to the table, but that's not what I need to do. I need to update the table with the changed data.

Any sample code or blog links would be greatly appreciated!

Thanks for the help!
-Scott

View 6 Replies View Related

Mapping Source And Destination Columns

Feb 21, 2007

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

View 1 Replies View Related

How To Dynamically Change Source Text File In Sql Server Dts

Sep 25, 2006

Hi ,

I have a table (source file) where all sourse file(its all text file) name and location stored. i have a dts packeage, through this package all the sourse file will upload. So I need to dynamically change the source file name in dts packages.

How i can do it??????

Can any body plz help me out!

Thnaks

SAndipan

View 1 Replies View Related

Processing Data From Source To Destination Table

Sep 22, 2013

I want to process data from source table to destination table without using cursor.

At this point; we are creating temp table and inserting data from source to temp table. once we get data into temp table; using while loop we are processing record one by one to destination table.

while executing stored procedure; we noticed that there are few records in source table which are invalid and stored procedure is terminating from such records.

Any better approach to log INVALID data and resume code to process next record instead of terminating.

View 7 Replies View Related

Transferring Data From Read-only Source To Destination.

Oct 17, 2006

Hi,

I have read only permission in the source (OLTP) database. The source database is running in SQL Server 2000 and the size is more than 200 GB. I need to pull data from source and load target which is running in SQL server 2005.

Following are the objectives I want to achieve.



Data should be loaded on incremental basis.

Whatever changes take place (Update/Delete) in source, that should be replicated to already uploaded data.

Here I want to mention that, the source database does not have any identification key or timestamp column like Updated_Date by which I can filter the data which are recently inserted or updated into the source and upload the same. The source does not maintain any history data also. So I do not have any track of deleted record also.

I don€™t have any scope to change the schema in the source. In this scenario can anybody suggest me the best approach to achieve the above mentioned objectives?

Can I retrieve only the recent updated or inserted date form transaction log back up. Can log shipping solve the give the solution?

One more question. Say I have a table and I am exporting/importing all the data from/to my target table using SSIS or DTS. In this scenario does using query or using directly the table affects the performance?

Regards

Sudripta Rakshit

View 4 Replies View Related

Move Data From Source Database To Destination

May 14, 2008

I need to write TSQL script that will insert, update, delete rows in a production db from new, updated and deleted data from staging db. They are both in different servers and I can only use TSQL. Basically, the production DB needs to be synchornized with the staging DB.

I was thinking using dynamic sql, cursors, and linked servers. Any ideas?

Jim

View 2 Replies View Related

Source As Flat File And Destination As Oracle

Aug 22, 2007

Hello Everyone,
Please do inform me, how can I check if there is a new record or changed record from the source.

NOTE: my source is Flat File and destination is Oracle Table.

What is needed from my side is the history load (Type 2).
This is not possible thru SCD component in Integration Services, If my source is Oracle (Even after I had added the parameters in my OLE DB)
Please do inform me about this process. Very important.


Thank you

View 1 Replies View Related

Lookup For Compare The Source And Destination Data

Nov 19, 2007



Hi

i want to compare the source and destination data my source is Oledb source and destination is sql server destination I want to Compare the Data in destination with source and also want to add validation like iF Any row is there is destination but not in source delete that row if any row in source but not in destination Insert that row nd compare source with destination respect to Primary key if There is change in any column of that row Update that if not change than move to next record. Please tell me what is the sutable object and what to do
Please Help me Out
Thanks
Sandeep

View 10 Replies View Related

Lookup For Compare The Source And Destination Data

Apr 24, 2008

Hi,

I am new to SSIS can u help me out in this i have a source(Flat File) and target SQL Server 2005. Source has got 2 columns- Col1 and Col2 even target has got 2 col's Col1 being the PK. I have created a package that checks if target and if the record exist it updates and if it does not it inserts. My package looks like

Source - lookup on target- Conditional split- Derived Column and then 2 OLE DB Destinations 1 for inserts and 1 for updates.

I have created a relationship in lookup with col1 from source and col1 from target and col2 as lookup col and connected red output to 1 OLE DB for inserts and redirect rows to it. Green out put i have taken to conditional split and gave condition like Col2 from source is not equal to lookup col2. I run the package with this it is inserting new records but not updating it.

I tried to add derived column after conditional split but lacks in writing expression that says update col2 records if they changed at source. Can u help me out in this scenario. I would appreciate if you could get back to me ASAP.

Thanks

View 5 Replies View Related

Excel 2007 Data Source/Destination

Dec 5, 2007

I've SSIS 2005 SP2 and Excel 2007 installed. How come I do not see Excel 2007 on the Excel version list?

Thanks,

Ash

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved