I'd like to extend a package functionality. I created it drag/drop way with hard-coded table names.
Now for the same source and destination connections I'd like somehow in a loop transform 20 source tables of the same structure to 20 destination tables of the same structure providing table names in a loop. I also have in the package preparation SQL tasks such as dropping destination table if exists, and then re-creation , so it needs to consume a table name as parameter from my loop.
I have a legacy SQL 6.5 system where they have articles that they relate to multiple topics by using a column with comma seperated values example ArticleID: 1, Topics 3,5,8,9. Im trying to find a way to automate this in SSIS to create a cross ref table. Is there any transformation componets that can help me with this?
Hi All,I'm not sure how best to describe what I'm really looking to do, soI'll just give you my example and hopefully someone can help.I have a table that looks similar to this:Primary Key Field Type Field Value--------------------------------------1234 FName Sally1234 LName Smith1234 Phone 555-555-55557777 FName John7777 LName Riley4444 LName Johnson4444 Phone 222-234-5555etc...Which I want to move into a format that looks like this:Primary Key FName LName Phone-----------------------------------------1234 Sally Smith 555-555-55557777 John Riley4444 Johnson 222-234-5555What is the most efficient way to do this? I'd rather not create aseparate query for each of the field types (there are more than 3 in myactual situation).Any help you can offer would be appreciated!Jeff
Hi all, using transactional replication in SQL 05, I have a schema that is different at the subscriber and wanted to know the best approach for transforming the published data. For example, the subscriber is more normalized than the data being published causing 1 published record broken out into to many tables at the subscriber. Also need to applying scalar funtions to a few published columns before committing at subscriber.
I've read DTS, although backwards compatible, is being deprecated in a future release. I suppose I can use either SSIS or custom procedures. I didn't see any tie-in to SSIS in the system procedures to setup replication (only DTS). Any advice on which would work in this type of scenario?
Hi ... I need to read rows from a large source file , check if the data selected already exists in the destination and if it does, upate the destination , else insert a new row . Now i could use a sp, but that means i have to call it for each row in the text file ... any ideeas on what kind of package can be created /
I'm having a hard time trying to transform a date that comes in for import in this format: 2015-04-08T18:00:48.000Z to DT_DBTIMESTAMP, I get potential loss of data error. (I've tried other SSIS data types.)
I had never seen this type of date before so I'm at a loss.
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
I've tried creating a simple SSIS package but I get the error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
THis is a known bug and while we wait for SP1 to fix this error, is there another way to simply copy tables (with indexes and without having the identity column renumbered) ?
In previous versions we could query the system tables to get metadata. In SQL 2005 they seem to be hidden. They are there. You can query them, but I can't find them.
Question: Is there any way to see them in SSMS?
Yeah, I know you aren't supposed to, but hey, they are usefull and I like them.
Microsoft has given us a confusing collection of alternatives for finding metadata; System views, Catalog Views, Dynamic Management Views, Compatibility Views, Information_schema views.
I have tried to organize some thoughts on what these are and here is what I make of them. If any of this is in error, I would like some corrections.
SYSTEM VIEW - the stuff below and more. Shows system information. ...CATALOG VIEW - is a system view on the system tables ...DYNAMIC MANAGEMENT VIEW - is a system view on system tables and various buffers for current status info. ...COMPATIBILITY VIEW - is a system view which shows ONLY 2000 information. It is there for compatibility. ...INFORMATION_SCHEMA VIEW - each database has it's own set. Holds db-level metadata.
I am trying to implement paritioned tables in SQL 2005. So far, I am successfull implementing partioned tables using filegroups. I have created three filegroups and partitioning table in those three file groups using PARTITION FUNCTION,PARTITION SCHEME and I am successfully partitioned table on my local machine . But now I need to implement a partitioned tables residing on multiple machines. Would anyone help me for this topic? I am using filegroups to partitioning table. Is there any way to partition tables across multiple database servers without using filegroups?
I have problems importing tables from db2 using the import wizard using IBM OLE DB Provider for DB2
The tables and data ('Preview...') can be viewed but when trying to map the columns ('Edit...' next to the tables) or import the data an error is recieved
[DB2/NT] SQL0443N Routine "SYSIBM.SQLCOLUMNS" (specific name "COLUMNS") has returned an error SQLSTATE with diagnostic text "SYSIBM:CLI:-727". SQLSTATE=38553
Hello All,I am trying to use a SQL Server 2005 Temporary table with a hash(pound sign) in front e.g. '#OrderTmp'. . ASP.NET is objecting with the message viz "Incorrect syntax near '#OrderTmp' ". I guess it is objecting to the hash.Has anyone any ideas about how to specify this temporary table correctly.Thanks for your help,Peppa
HI, I current have two 2005 boxes running 9.0.3050 in different DMZ with the source running a DTS to drop and copy its tables to the source every night. It was working up until last thursday. Nothing has changed in the FW rules and getting no errors. One is 3.5GB which copies fine, the other is 21GB and runs all night with only getting a fraction of the tables populated. I'm the hardware guy, but have some understanding w/ sql. Thanks in advance for any help.
Iam reading this forum from some time but its my first post here
Today i found by accictend this really nice PDF poster with SQL 2005 internal System Tables and thought that maybe someone here will be interested having it.
Poster is really huge, just like db schema on it ;)
But ok no more words, just grab it from here if You want http://rapidshare.com/files/79265626/sql.2005.sys.tables.pdf
Regards, Tom
PS. 1. If You can't read PDF after download with FireFox (like me) turn off pdf plugin and then download starts normally 2. If someone from admins think that its not good forum for this post pleas move it to somewhere that it best fits.
Can I know is it possible using database mirroring for only certain tables from the database, not for the whole database in sql server 2005. Hopefully able to get any assistance here. Thx alot.
hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?
I have a application where users are supposed to edit data from a table in a datagrid.
I want to use a dropdownlist to let the user choose a table to edit.
The users are members of different windows-goupes and different users have only access to edit there own tables. Let's say that they own data in different tables.
Is it possible to use windows authentication to find out what tables to show in the dropdownlist?
I am trying to add a linked table to my server, it is an access table.
Here is the code i am using but i get an error: EXEC sp_addlinkedserver @server = 'AITdb_be2', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'Access', @datasrc = '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb' GO
-- Set up login mapping using current user's security context EXEC sp_addlinkedsrvlogin @rmtsrvname = 'AITdb_be2', @useself = 'false', @rmtuser = 'Admin', @rmtpassword = '' GO
-- List the tables on the linked server EXEC sp_tables_ex 'AITdb_be2' GO
ERROR: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2" returned message "The Microsoft Jet database engine cannot open the file '\kcapp03deptsCommonEIQAIT DBTestAITdb_be.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AITdb_be2".
I am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:
[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.
[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.
To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
Probably a stupid and regularly asked question but I can't seem to find an answer, so here goes,
we have 16 .txt files, some with over 350 columns.
That info from each individual file needs importing to multiple sql tables.
need to look at sql table1 does record exist? if not create new then add in data once its been transformed eg datetime from yyyymmdd into datetime values [managed to get this using derived column] for first 20 columns, otherwise do update for the 20 columns...
then look at sql table2 and repeat for next n columns....
So I was wondering is it going to be better to write this as a dtsx package? if so can you point me to an example
or should I just write the code as part of a code behind page that scrapes the info and does a standard update/insert procedure?
