I'm trying to copy data from MySQL to SQL Server 2008. My SSIS is generating error for time (DBTime) column in MySQL database. (cannot convert dbtime to dbtime2) What datatype can i use in SQL server for time? I tired nvarchar, varchar and also tried data conversion task but i get same error. ...
In SQL Server 2005, Import Data (SSIS), my desire is to import a text file and have it append to an existing table. The first time through the wizard on the Column Mappings step I swear the Append rows to the destination table radio button was enabled. But, now, it's disabled (grey) and even re-starting the wizard won't cause it to re...
I've perused the threads here on migration from SQL 2000 to SQL 2008 but haven't really run into my question, so here we go with another one. I'm building a strategy to move specific SQL 2000 databases to a new SQL 2008 R2 instance. My question comes with regards to the best method for transferring the schema and data. One way I know ...
I am using SSIS 2008 to execute multiple stored procedures in parallel in control flow. Each SP is supposed to ultimately update 1 row in a table. The point to note is that each SP has a defined responsibility to update specific columns only. It is guaranteed that the different SPs will not update each other's columns. So the columns t...
Hello How to loop through / access values of RecordSet Destination in SSIS Script Task. Thanks ...
I'm importing a flat txt file into SQL Server 2005 using SSIS. The problem is that negative numbers between -1 and 0 in the txt file are in a format without leading zero, e.g.: -.15 If I insert such number into my database using plain INSERT syntax it works without complaining. However, SSIS reports an error if it encounter such number...
I have a CSV File, from that file i have to consider only two columns suppose A & B. Now my requirement is Map all the values which are there in the column A from the CSV to the table and update other column in the table with the values from Column B. So far, i have created a DataTable which has these two columns. But I dont know how t...
I'm updating a table from another table in the same database - what would be the best way to count the updates/inserts? I can think of a couple of methods: Join the tables and count (i.e. inner join for update, left join where null for inserts) then perform the update/insert Use the modification date in the target table (this is maint...
Hi , I have a package that does a row by row update of values. When i run this though the debug mode (opening the solution and pressing 'play') it takes about 2 mins. However when i try to run it by clicking thre dtsx, it takes about 3 hours. Any ideas? Thanks Abhi ...
Hi , I get the following error when i try to schedule my ssis package. Its failing when i tries to process anything above 4000 records from a flat file source. Anything below 4000 and it processes fine. Any suggestion would be very helpful. Thanks , Abhi Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. E...
Since the SQL query box of an SSIS Lookup transformation is almost unusable, I usually write my code in SSMS (formatted with Redgate's SQL Prompt tool) and copy it to the Lookup. When you close this window and later come back in, sometimes the formatting is preserved, but sometimes the carriage returns are replaced by the "square" chara...
I'm writing an SSIS Transformation component. I would like to allow the user to select which input fields he would like to pass-through to the output. I thought that the SetUsageType() function would control this - it takes an enumeration (DT_READONLY, DT_READWRITE, DT_IGNORED). DT_READONLY is for input-only fields, DT_READWRITE is for ...
Hello all, I need to programatically unzip a zip file in c# in an SSIS Script task. I know there is a free library called Icsharpcode. Has anyone done this. If so any hints or suggestions would be appreciated. ...
I spent half an hour trying to figure out what I cant do in SQL 2008 Standard in terms of importing data using SSIS pacakges vs Enterprise edition and simply couldnt find the answer.. I am sure someone already knows that.. Please tell !! :) ...
I need to be able to extract and transform data from a data source on a client machine and ship it off via a web service call to be loaded into our data store. I would love to be able leverage SSIS but the Sql Server licensing agreement is preventing me from installing Integration Services on a client machine. Can I just provide the clie...
I have an ssis package that imports a view from one database into different db and places the data into a table. The view has all datatypes set to varchar which I cannot change. Some of these varchars are really datetime values. The view has set all NULL values to N/A and i want to replace the N/A with db null. The table I am importi...
I have this query: declare @company as varchar (20); declare @query as varchar (500); select @company=Name from Company; set @query='SELECT [Name] ,[Address] FROM [' + @company + '$Customer]' exec (@query) It can run succesfully in SQL Server and 8 rows returned, but when i copied that query and pasted it in SSIS OLE DB...
Hi, I need to load and execute SSIS or DTS packages ASYNCHRONOUSLY from ASP.NET C# page on click of a button and report the success or failure at the end of the execution and if it fails the details of the exception should be shown to the user. it needs to be asynchronous because job could take a long time to finish. user should also ...
Hello, I'm writing my first SSIS pkg and I'm stuck. Any insight would be greatly appreciated. I'm running a sql agent job that kicks off a SSRS report. (The job was generated via a scheduled subscription.) This report relies on 2 stored procs which require the parameter 'When' (date type) and it dumps a PDF of the report to a file sh...
I'm currently building our Data Warehouse, primarily using Ralph Kimball's methods and guidance. We are using the Microsoft stack for this (so SSIS, SSAS). I am a bit stuck deciding how to handle BOMS (Bill of Materials) which is effectively an unbalanced hierarchy. The BOM handles assemblies which are a collection of parts. Each part...