# Tables In SSIS
Jan 24, 2008
Hi
I have a scenario that would like to find a solution in SSIS package; this is a challenge.
Question: How can I create a # table on Server2 and load it with data from Server1 and have the # table available in the whole SSIS package?
Let's explain what I'd like to do:
I am working with two databases that are installed on two physical servers
1-Server1: database staging
2-Server2: database biolap
I’d like to load data from Server1 (staging DB) into a temporary table (# table) on Server2 (biolap DB) to be able to join and update # table with other dimensions located on Server2 (biolap DB); and then load # table into the Fact table located on Server2(biolap DB).
- I do not want to use link server between these two servers
- I do not want to use LookUps in SSIS ( as much as possible) since I need to join to too many dimensions.
P.S: We are going to install staging and biolap databases on two physical servers. There are hundreds procedures in place that each one is joining these two databases. Since we do not want to use link server ( because of performance issue), we are going to convert them to SSIS packages.
Thanks in advance.
View 2 Replies
ADVERTISEMENT
Feb 26, 2007
Hi -
I have been trying to work with a data flow task that uses temporary tables on the remote DB.
I have seen Jamie Thompson's SSIS Temporary table guide, available here: http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx, and successuflly got his download package working against my AdventureWorksDB.
I cannot get a temporary table implementation working in my SSIS package despite following JT instructions.
Current status:
Marked the OLEDB connection as retainsameconnection = true
Created a seperate execute sql task in control flow that creates the temporary table '#BUSessions'
A data flow task follows the execute sql task, the ole db source is marked a delayvalidation = true
At this point, I try to enter the query using the interface, validation kicks in and says this object is invalid. If I try to use the properties window to set sqlCommand and manually enter the query then an error icon appears on the Ole Db Source.
Can anyone help me with this?!?! I'd really appreciate it! The query provided to me (for the remote DB) uses temporary tables and I have little say in this...
p.s. - I have also ensured I manually create the global temporary table with same name before starting this process.
View 2 Replies
View Related
Jul 26, 2006
Does SSIS (other than, maybe, via the Execute SQL task) support the idea of temporary tables? (I want to make a backup of production data (in temproary tables), truncate the production tables, and populate them with new data. If an error happens in the process, I'd copy the temporary backup tables back into the production tables. When the process has ended the temporary tables should "vanish")
TIA,
barkingdog
View 3 Replies
View Related
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 3 Replies
View Related
Oct 21, 2007
Hi there-
Here is my situation.
I have got Table A in database DB1 and Table B in database DB2 with same table structure. I need to compare the data in table A and Table B and add the missed records in Table B.
Can anyone help me on how to do this in a SSIS Package?
Thanks,
Sundar.
View 6 Replies
View Related
Nov 19, 2007
Hello,I am a beginner with SSIS. I have a scenario ...
I have source DB 'MySource' and a destination DB 'MyDestination'
I have created a SSIS to push data from my source data base to detination DB.
I have a scenario where in i have to push data from the source DB to the Destination DB , depending on a join condition for which the data is in Destination.
I would not be able to acces both the DBs while writing query to pick data from .. source DB. How do iproceed ..
let me give an exmple
Source DB:
Table1
---col1
--col2
--col3
--
--
--coln
Destination DB;
Table2:
--col1
--col2
--col3
--
--
-- col n
Table 3:
--col 1
--col 2
-- col 3
Now i Have to move data to table 3 into destination DB getting all the records which pass a join on table1 and table2 on cloumns 1 ,2 and 3 ..
I have only read access on the the Source DB.. pls let mw know how i can solve this scenario in SSIS.Thanks and Regards,Raja Tirumala Rao
View 3 Replies
View Related
May 21, 2008
Hi All,
I am trying to design a package that needs to compare two tables in two diiferent servers. Basically I need to insert records into one server by comparing existing records with second table in other server. Is ther any way I can do it with out using Linked server?. Both tables have same structure.
Hope any one will reply soon.
Thanks,
lmp
View 7 Replies
View Related
Apr 3, 2008
1) Scenario
Source Oracle database with 400 tables, each table has a field 2 common fields
Change_date and Company_ID
2) Required
To copy 50 tables to at least 2 different SQL Server databases based on the
Change_date and Company_ID
for Compnay_ID = €˜A1€™ and Change_Date = Last_Working_Date , copy 50 tables to Database1
for Company ID = €˜A2€™ and Change_Date = Last_Working_Date , copy 50 tables to Database2
Also I am not copying all the fields from each table, just a subset of fields
3)What I have done so far to create Database1
a) Created a SSIS project
b) Create 5 individual packages for the project.
c) Each package has 10 pairs of OLE DB source to OLE DB source representing the transfer of data for 10 individual tables.
d) For each data source, I use SQL command as the data access mode
E.g
Select field1, field2, fiedl10 from Table1 where Compnay_ID = €˜A1€™ and Change_Date =
Last_Working Date
4) What I want to do
However, since the where condition is the same for all tables (Compnay_ID = €˜A1€™ and Change_Date = Last_Working Date) , I want to find a way to set this as variable, so if the where condition changes, I only have to change it once.
Any assistance with this would be greatly appreciated.
<!--[endif]-->
View 3 Replies
View Related
May 16, 2007
Hi,
I am new at SSIS and I am trying to create a Datawarehouse using SSIS. I have the data files as flat files I have the Dimensional Model ready on Paper and Now I need to use the SSIS for the ETL process.
I am trying to figure out how to make dimension tables in SSIS? I mean I want to create the 5 Dimension tables and then create a Fact table out of it but I cant understand where to start? Can any one tell me how we create Dimesion tables in SSIS. Like one of the dimesion tables I need to create uses 2 flat files and is like a flattened dimension, How would I create this in SSIS?
Even if there is any tutorial which shows this step by step do let me know. I would really appreciate any guidance on this.
Thanks,
Sarang
View 11 Replies
View Related
Nov 3, 2006
I need to create a fairly simple package. And almost because of the simplicity, I'm stumped.
I need to copy all non-system tables from server1.database1 to server2.database2. Additionally, four of the 30+ tables need to be renamed on the fly -- i.e. their name will reflect the year and month that the copy takes place.
I've tried using the Transfer SQL Server Object Task to simply copy the tables, but I get flaky results at best with it. Sometimes it tells me the source table doesn't exist, when I can clearly see it (and I've selected it from the list). And even though I have turned on the Include Indexes option, they don't always come through.
I'm wondering if I need to do a For Each loop looking at an ADO object?
Any suggestions?
Stephanie
View 5 Replies
View Related
Jul 19, 2007
Hi
I am new to SSIS and am looking to load XML files (with a DTD definition) into tables via a SSIS package. I have created a XML task and am able to load the XML and output it to file. I have also stripped out the DTD definition and am able through a dataflow using XML source an OLE db Destination load and map the XML to table sin my DB. But have no idea how to get the data in when it has a DTD definition included. I either want to put each file into a row in a table then query it. Or from the SSIS package input the relevant info into a set of staging tables or the real tables.
Any help/pointers would be appreciated.
View 10 Replies
View Related
Mar 26, 2007
Hi,
I'm trying to create a package with SSIS to replace the DTS process that we have in place already.
DTS package copy four table content from one server to another. I have created a simple SSIS to do the same processes but the process it alot slower than DTS!!
I did ran the SSIS package using ctrl+F5 and also from command prompt but still it's quite slow.
SSIS uses SMO to access to server and both are running on 2005
Thanks
View 9 Replies
View Related
Nov 27, 2006
Hi,
I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage
Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'
This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.
Similarly I have to hard code creating the indexes into the package as well.
Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?
And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?
Thanks in advance.
Iain
View 4 Replies
View Related
Feb 4, 2007
I followed the steps outlined in
http://msdn2.microsoft.com/en-us/library/aa337084.aspx
and was successful at importing ".dbf" files from a dBase 7.0 folder.
However, this import was quite slow: approx. 2000 rows per second. Some of the tables I must import have over 8,000,000 rows, which would then take 4,000 seconds, or over one hour. This import rate seems unaffected by whether or not I put a "Copy Column" Data Flow Transformation task between the "OLE DB Source" and "OLE DB Destination" objects.
In SQL Server 2000 it was much faster to import such large tables. The 8 million row table takes only 14 minutes (800 seconds) with SQL Server 2000 -- giving a transfer rate approx. 5 times as fast.
Is there any way to speed up this process in SQL Server 2005? (In SQL Server 2000 there was a "Use fast load" option on the "Options" tab of the "Transform Data Task Properties." I have not yet found any similar option for SQL Server 2005.)
My receiving database is using the "Simple" model, so there is no need to create transaction entries for loading these large tables. There are also no indexes on these tables: the data are being loaded into new tables created in SSIS.
Thanks for your help with this issue.
View 3 Replies
View Related
Aug 24, 2007
Due to some legacy requirements, I'm using an OLEDB connection with the Jet driver and creating dBASE III tables (.DBFs) as the destination for a data export. The source is SQL Server 2000. The SQL table has a bunch of varchar columns, and I can't send them directly to the .DBF because SSIS complains that it can't (implicitly) convert from non-Unicode to Unicode. UNICODE?! dBASE III is ancient. Why would Jet/SSIS assume that these .DBFs are Unicode? As it stands, I have to put a data conversion task between the source and destination and convert all the columns. It's a real pain.
And no, I don't want to make all my SQL Server columns nvarchar. Is there any setting I can put in the connection manager or the connection string to prevent this error? I've already set AlwaysUseDefaultCodePage to True on the OLEDB destination component and the default code page is 1252. That didn't work; I still got the can't convert error.
View 3 Replies
View Related
Feb 21, 2012
So we are running SQL 2000 and we use a DTS Package to load about 100 txt files to different tables in a SQL DB.
So in DTS packages you define the connection (database) and the different source files will connect to the connection.
You define the destination on the flow arrow. My question is how do i accomplish this in SQL 2008 using SSIS. (upgrading SQL).
View 9 Replies
View Related
Apr 6, 2015
Currently we are trying to load the xml files into sqlserver tables by using ssis 2012,We are getting xml files as a column in source table ,so we have to push these xml files into destination tables.
I'm following the below way to perform this activity
[URL]
But We have standard XSD structure for all the xml files ,and if xml file matches the XSD structure then only we have to load ,else it should skip to next xml file.
View 1 Replies
View Related
Oct 16, 2007
Hi,
I'd like to create a temporary table (# or ## table) and have it available in DataFlow to work with it. I see I can create it using "Execute SQL Task" component in ControlFlow; but it's not available in DataFlow. It seems it should be created by a DataFlow component to be seen in the same DataFlow.
Any Idea on this?
View 2 Replies
View Related
Feb 28, 2008
The challenge: I have to extract and convert data between 2 SQL server systems - only 4 tables on the source systems, 8 tables on the target system. Source tables have between 5,000 rows and 16,000,000 rows. For most of the tables (for example Customer, which goes into 4 target tables), there will be 1 row in target tables for each row in the mapped source system table - so my 13.5M customer rows will end up as around 40M rows across the 4 target tables. So far, so good. But - this is a 24x7 online retail web-site, and to get the data across as a clean process, we require the smallest possible duration.
I have progressed on the customer migration, and am testing on a test environment (2xdual core HT processors, 4 GB ram) which was 2.15 million rows. Live environment is likely to be a 4xdual core with 8-16 GB ram.
I am trying to optimize the extract data flow, and have read the SSISperfTuning doc. I am now trying to put that into practice.
I have a row size of approx 340 bytes, so based on that, and my test environment of 2.15 million rows, I work out at around 700 MB ram required to buffer the data. That is a factor of 7 times greater than the max buffer space for a data flow of 100 MB, which it seems, means I should divide the base MaxBufferRows (10000) by 7 to go down to 1400 rows?
I see a LOT of the following messages in my progress, when running with default settings:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 30 buffers were considered and 30 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
The design of the data flow at the moment is:
..........................................|--target table 1
SOURCE SP ---- MULTICAST---|--target table 2
..........................................|--target table 3
..........................................|--target table 4
any thoughts on Buffer tweaking, corrections to my assumption and other hints/techniques?
*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!
View 7 Replies
View Related
Nov 23, 2006
I need to bring over a large number of tables' records (200+ tables) with the Import/Export Wizard. The tables are being imported from MS Access. A separate script run previously will create the tables, so the DTS wizard is only to bring over the data from the Access tables into the empty SQL ones.
First, I get the warning that indicates "a large number of tables are selected for copying, and the wizard may not be able to copy all the tables in a session. Select no to go back and unselect some tables, or select Yes to attempt to copy all the currently selected tables at one time".
Well, I proceed with the DTS and it tries to validate and takes a fair bit, but then it errors indicating:
"Error 0xc0202009: {2F0FABA0-5F4B-4310-97C0-76EA19893547}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)"
Can anyone shed any light on why I receive an "unspecified error" when tring to DTS a larger number of tables. It does not error, if I import 40 or so tables.
This was never an issue with SQL 2000 DTS.
Thanks
View 4 Replies
View Related
Feb 21, 2008
Hi
I'm new in SSIS I'm trying to create SSIS that can copy tables,permission,index,constrant... to a sqlserver2000 from sqlserver2005. Can any one help stepbystep.
View 1 Replies
View Related
Apr 4, 2006
Hello Everyone,
I need to know which system tables hold the pkg information when you import a pkg into SQL Server 2005.
Thanks
Shabnam
View 3 Replies
View Related
Nov 21, 2007
Hello,
I have the following Datawarehouse problem (SQL Server 2005 and SSIS 2005):
2 Fact tables and both with some foreign surrogate keys of dimensions (some of them with common dimensions, other with different dimensions).
I have to create another FACT table that is an aggregation and join table between the 2 ones.
How can I do?
View 6 Replies
View Related
Feb 1, 2008
Hello,
I have an SSIS package that affects data in fourteen tables. This SSIS packge is supposed to insert, update, delete and apply various business rules to the data in the new database tabes in the same way that it occurs in the DTS package that affects the old tables in the former database.
After both the DTS packge and SSIS packages are run, the data in the fourteen tables from both databases need to mirror each other. I figure this is the best way that I can know that the new SSIS package is working correctly.
Is there something I can do in an SSIS package that can compare all of the tables between the two databases, record for record then (maybe as error output) produce output in the case where a mistmatch was detected between two given tables? Would a lookup transformation be helpful for this kind or thing, or should I think about doing it some other way?
The old database is on SQL Server 2000, and the new one on SQL Server 2005 64 bit. I've run into problems creating a linked server between the two to where I could just run queries to do this kind of check.
Any ideas?
Thank you for your help!
cdun2
View 3 Replies
View Related
Jul 17, 2007
Hi Guys,
Yet another question again on the issues with SSIS. I have a package now which is working fine.
The package consists of a control flow and i have 2 DF tasks which are unionall first and then saved into a sql server destination.
It's fine up to this point but i've just been notified that i would need to generate 2 files based on different values after i combined the data from 2 sql server DF tasks.
My question is how can i know the rows which are being saved on this sql server destination.
I have a primary key which is an autoincrement column.
Thank you
Gemma
View 45 Replies
View Related
Oct 13, 2006
I am just getting started studying SSIS with Kirk Haselden's "Integration Services" book. The problem I am trying to solve would seem easy enough to solve in code, but I am still early in the book and would like to be able to focus on the aspects of SSIS that would help me expedite this with SSIS, or to find out early whether what I need to do cannot be easily done.
The problem itself is simple enough: I have a database of roughly 100 tables. Ignoring the poor normalization in the database for the moment, my more pressing problem is that that I need to rekey all of the main OLTP tables from a mashup of different key schemes to UNIQUEIDENTIFIERS. For example, Client table is presenrly keyed on an INT, Client Number. ClientFile table is keyed as FileType = NVARCHAR(2), ClientNumber INT, FileNumber INT (incrementing, meaningless number). Child tables to ClientFile are the same key structure as ClientFile, plus yet another (incrementing, meaningless number) INT, etc like this. I would like to know if and how or where I should be looking to convert the Client table to a UNIQUEIDENTIFIER key, and the same for the ClientFile, makes its key also a GUID, and have a reference to the new Client tables GUID key as a foreign key in the ClientFile table, and on and on like that. The Client is at the top of the food chain.
In essence, I would like to have every table's key be called ID and be a UNIQUEIDENTIFIER (GuidRow), and I would like for example, the ClientFile table to reference the Client table with a column named ClientID. I would like ClientFile's children to have a foreign key called ClientFileID, and their own keys to be ID (RowGuid).
There are also several lookups in each table where, of course, the actual string values were stored instead of a key to the value (i.e. full state or country name instead of a code from a state or country lookup table) that I need to convert to something more sensible, like replacing the state name with a state code and a country name with a country code and link to appropriate respective tables. :) In fact, some of the values I need to break out from columns could also be keyed with RowGuids as well and I would much prefer to use those than string or INT keys.
Other than those problems, most of the rest of the data in those tables could essentially ba a straight copy operation since the source database is SQL Server 2000 and moving to SQL 2005 (one notable exception is that I am converting ntext columns to nvarchar(MAX) columns.
I am assuming this is probably ridiculously simple and I just haven't found my way there yet, but I still have much of this book and the help files to go through and the Index didn't give me any comfort that this was something I will or will not be able to do easily.
The real help I am looking for is two fold: a) somebody tell me to stop reading this 700 page (very well written) tome if I would be better off writing this all in code myself, and b) if this is something that most of you could do with SSIS with both hands tied behind your back, please at least help me focus on the important transforms and tools so that I don't smend a month becoming a data warehouse wizard and ultimately not solve the problem I am most concerned with.
Please be mecriful with the heat, I have already confessed that I am new to this and am scrambling to come up to speed as fast as I can, but am beginning to think this problem is either to trivial for coverage in this book, or perhaps just not what SSIS was designed to do.
Thanks much in advance for any guidance.
View 10 Replies
View Related
Jun 15, 2006
Supose I have two records in a parent-child relationsuip (actually I have many more such records). Does SSIS offer any support ask for inserting one record into the parent table, the other into the child table, and updating the foreign key of the child to point to the parent?
TIA,
Barkingdog
P.S. I have to do just this as part of the datawarehouse test I'm running. Seems like a common task but I don't recall anything in SSIS addressing the issue.
View 5 Replies
View Related
Jan 25, 2013
So i have about 80 different SSIS PAckages that load different text files into tables. each package has a config file that contains password, server name and user id. I want to forward these packages to a use who will execute them. So instead of giving the user 80 packages to execute manually, how do i put these packages into one parent package so that the user has to only execute this parent package instead of executing 80 different packages NB. package exec is manually. (we will automate later) so this is a temp measure.
View 2 Replies
View Related
Jun 26, 2014
I have three databases and 40 tables within each database on my server, for each of the tables I want to know which SSIS package generates that table. Is there a script that can do this?
If the SSIS package does not create or populate a table on the server I then want to check if there is a stored procedure that populates this.
View 6 Replies
View Related
Sep 26, 2007
Hi,
I want to create a local temporary table in execute sql task and and want to use the same in Data flow task as source table.
I follow the following steps to achieve this:
01. Created a new SSIS package
02. Create a connection string to "(local)/." server, "tempdb" database
03. Set the "RetainSameConnection" property value to "TRUE"
04. Set the "DelayValidation" to "TRUE", where ever I found this property
04. In Control Flow I added to items
a. Execute SQL Task
b. Data Flow Task
05. For "Execute SQL task" I set the connection to "tempdb"
06. I written the following query
Create table #transfer_CompaniesToProcess_tbl
(
companyID int not null
)
GO
07. In Data Flow task I added "OLE DB Source" and "OLE DB Destination"
08. In "OLE DB Source" I changed the "Data access mode:" to "SQL command"
09. In "SQL command text:" I entered "select * from #transfer_CompaniesToProcess_tbl"
10. When I clicked on the "OK" button; I ended with following error:
TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Invalid object name '#transfer_CompaniesToProcess_tbl'.".
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
I gone through the following article and it seems I missed some thing.
http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx
Can any one have any idea where I am doing wrong?
Thanks
Sreekanth
View 9 Replies
View Related
Nov 7, 2015
I am going to set up a new SSIS package that will import data into 5 different tables on a SQL Server database. Â The source of the data is on another SQL Server and I will use to select the data. Â If one of the tables fail to import I do not want the SSIS package to import any of the data.What is the best way to create this package? Â Is it best to create one SSIS package, with five data flow tasks that are linked to each other. Â Within each data flow task, is a Source and Destination to transfer the data to each table. Â
View 3 Replies
View Related
Jan 2, 2008
I have a SSIS package that transfers a couple of tables from one database to another database on the same server. It works fine in most of the machines we tested with. However on one of the customer machines, it consistently fails with the error message, The Return value was unknown. The process exit code was -1073741795. The step failed.
This package runs as a scheduled job on the sql agent. When i did a sql profiler to see what is going on, i noticed that the last step before the bulk insert, it gets the collation and the schema id of the 2 tables. My guess is, it compares these values from the source and destination databases and makes sure everything is ok before copying.
On a machine where this works, it goes ahead with the the next step which is the bulk insert itself. Whereas on the machines where it doesn't work, it stops right after this step. i.e. it does not even bother to call the 'bulk insert' api. Which makes me think it is doing some kind of validation with these values and it is getting something that is not expected.
If the collation or the schema was an issue, why throw an 'return value unknown' error? Can the error be more specific?
Any other possible reasons for such a behavior? Any clues?
Thanks
Gopal
View 2 Replies
View Related
Jan 14, 2008
Hi,
I am new to SQL Server 2005 SSIS Packages. I want to transfer data from multiple tables from sql server to oracle database. I cannot use export wizard as it creates new tables in the destination (oracle) DB. I already have tables created in the destination DB. When I created an SSIS package, it allowed me create package to tranfer data for only for one table from source to destination. I have created DTS packages in 2000, where you have source db and destination db and just add links for multiple tables. Is there a way I can do it in SSIS. Please let me know.
View 3 Replies
View Related
