SQL Server Integration Services Forum

Execl with multiple tabs

Tue, 24 Nov 2009 15:39:38 Z

Hello

I use SSIS to create a an Excel file and then sends it to a person. I did that using either a script task and a streamwriter or just using a DataFlow task with a datareader source and an Execl Destination, and then Send Mail task. However, I have a requirement to create a multi tab Execl Report instead of one tab. Basically the SQL Code that I use is for multiple events. For example, Events 1,2, and 3. I want each event to go on one tab.
How can I do that? I have access to SQL Server 2005 and 2008.

Thank you

Abdallah, PMP, ITIL, MCTS

Unable to delete breakpoint in SSIS C# script

Mon, 23 Nov 2009 15:36:46 Z

I created SSIS script task using C#. I placed a breakpoint within a script while debugging. Now I cannot remove it - execution always stops at this breakpoint.

I tried everything - pressing "F9" while at the breakpoint, going to the list of breakpoints and disabling it (there was no "delete" option), saving project and restarting Visual Studio - nothing helps. Each time I start package via "Start Debugging" execution stops in the script.

Anyone has any suggestions?

unable to find version of the runtime to run this application

Sun, 22 Nov 2009 07:36:45 Z

I am getting an error "unable to find version of the runtime to run this application" when i am try to deploy SSIS package by using manifest file.

"Case when" statement in data flow task

Tue, 24 Nov 2009 12:20:58 Z

In a data flow I have to perform an action similar to a "CASE WHEN" statement. In other words, I have a column that needs to be checked on the value, if it's value A, B or C then the output column needs to have value 1, in case it's D or E, the output column needs to have value 2, etc. I can do this with a conditional split and a derived column for every case possible. That works well but when there are a lot of cases, there will be a lot of derived column transformations in there. Is there a better way of doing this?

Thanks!

SSIS Error code -1071610801 ?

Tue, 24 Nov 2009 09:13:45 Z

Good morning every body,
Is any body know the Error code -1071610801. and where we can find the description of each error code?
thx

Creating Exe in DTS 2000

Wed, 25 Nov 2009 06:20:50 Z

Is it possible to create a exe(say, final exe) by combining a batch file and a exe (say, first exe), that is, final exe = batch file+first exe...Is this possible in SQL server 2000 DTS packages?

DB Developer

Sat, 21 Nov 2009 12:32:15 Z

I created DTS package on SQL Server 7.0

What it does, it takes data from sql tables and inserting it to Access .mdb tables.

When I run package manually it is fine, but scheduled job returns error:

... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Delete from Table invo_cost_centers Step DTSRun OnError: Delete from Table invo_cost_centers Step, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error stri... Process Exit Code 1. The step failed.

Need help to fix it.

Thanks,

SFTP for SSIS

Tue, 24 Nov 2009 21:02:15 Z

Hello,

I am currently using the FTP connection manager and FTP task to download files for processing in my SSIS packages. The problem is that our client has migrated this to SFTP and I realized that our process is failing due to this. And I don't see any minor workaround that can start making SFTP connection instead.

How hard is it to make SFTP connectivity from SSIS? I see many blogs asking to use 3rd party tools which we can't due to cost factor.

Is this straight forward to do this in SSIS?

Thanks

Hemal Mehta

Unable to execute both the etl packages and cube process from same job due to buffer failure

Tue, 24 Nov 2009 09:00:01 Z

Hi All,

I have a master package with 26 ETL packages (Execute Package Tasks) and one Analysis Services processing task. I scheduled this master package through sql server agent. It is working fine for few loads, now it is failing once it is reached the cube processing due to buffer allocation problem. If I am executing from my cube solution it is working fine. Is there any work around to release the buffer allocated by ETL ? so that my cube can easily excute from scheduled job. right now I created two jobs one for ETL load and another one for cube process, once the ETL load is finished by using trigger only I am calling the second job. Now it is working fine but I want to do both the task from single job.
so, please tell me how to release the buffer space before cube processing. Your help is greatly appreciated.

My RAM size is 64 GB

Thanks in Advance...

Lakshman

Text import with text qualifier and delimiter appearing inside fields

Wed, 25 Nov 2009 02:29:08 Z

Hi!

Was wondering if anyone can shed some light on how to get SSIS to behave the same way as DTS in this situation.

I'm importing several flat files around 1GB each. Some of the fields can have double quotes (the text qualifier) and also commas (the delimiter). The system that exports these files correctly duplicates the double quotes so it knows it's not the end of the text string. eg -

"ABC", "123","Jack Said ""I know, donkey"", to Jill","xyz"

DTS2000 handles this as expected which produces the following -

ABC | 123 | Jack Said "I Know, Donkey", to Jill | xyz

SSIS however thinks it's this -

ABC | 123 | Jack Said "" I Know | Donkey "" | to Jill | xyz

How do I get SSIS to correctly interpret anything inside the text qualifier as text?

Thanks,

Cameron

Parameter Passing in a For loop container

Tue, 24 Nov 2009 07:28:18 Z

Hi all,

I want to generate data from Apr 2007 till date, so I took a for loop container , took a variable varStartDate & in the for loop used InitialExpression as @varStartDate = 04/01/2007 , EvalExpression as @varStartDate<GetDate()

and Assign Expression as @varStartDate= DateAdd("m",1,@varStartDate). But inside that for loop if I try to take an Execute SQL task & try to execute a statement like this "INSERT INTO MHeadCount_History([Emp ID], [CurMonth])

Select [Emp ID], @varStartDate from HeadCount_Temp Where [Hire Date]< @varStartDate" like this it is getting failed.

If I remove the Execute sql task then it does not give any error.

Can anyone tell me how to pass parameter's. I have also tried mapping the parameter's but it didn't worked.

Thanks in advance

Regards

Amar

Invalid character value for cast specification

Wed, 31 Oct 2007 06:36:27 Z

I have a Data Flow task that is simply copying data from table A to table B. (there is a derived column in between however)

All the columns in table A are varchar(255). The columns in table B vary: floats, decimals, etc

However, I am getting a bazillion errors when I run this, each of them to the effect that:

Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client"

I don't understand why this is happening... I've succesfully run similar scenarios with other packages, ie) moving data from one table to another table even though the tables have different data types.

Help

Thanks

convert unicode to non-unicode

Tue, 24 Nov 2009 19:07:31 Z

I have student information in unicode data which is on linux. i want to convert that unicode data into non-unicode data and migrate to sql database
so can anyone help me for this problem?

set password for the project??

Thu, 19 Nov 2009 12:35:33 Z

hi there

i know that its possible to set a password for security... i have a project containing more than 20 packages that have protectionlevel property of encrypt all with password... but as u can imagine, its always a waste of time whenever i open my project... what i wonder is, if there is way to set password at project level

Best way to denormalize two tables using SSIS 2005?

Wed, 25 Nov 2009 00:32:37 Z

I am very new to SSIS but I think I'm starting to get it.

I have a source database that has two tables, Log and LogResourceMap which I will just call LRM for short. Log's key is "logId" and it has a 1-to-many relationship with LRM whose key is combined logId & resourceId.

To make things fun, Log also has a resourceId.

Usually (but NOT always) there is a matching entry in Log and LRM that has the same logid/resourceid combination, so I can ignore those ones from LRM. Then I want to copy all of the rest of the LRM rows back into Log, matching them up with their original parent (Log) rows to copy the data from them, denormalizing the works (and introducing lots of duplication, I know, but that's what I'm trying to do!) for reporting purposes. There would be a flag to indicate whether a given row was the original log or not

I could do the first part with a straight Data Flow task, and allow SQL Server to bounce the duplicates by making use of a unique constraint on logid+resourceid, but that doesn't seem like it's probably the best way to do it.

Is there a way to set up a Data Flow task to do an unmatched query kind of thing? I'm dealing with about 7,000,000 records so I want to do it efficiently.

Sample data might look like this:

LOG
logid, resourceid, message
1, 5000, test 1
2, 235, test 2
3, 111, test 3

LRM
logid, resourceid
1,5000
1,18
1,92
2, 222
3,111
* Note there is no entry in LRM for 2, 235. I don't know why there aren't maches sometimes. Perhaps a bug in the application that creates the data.

Result desired
LOG_LRM_Merge
logid, resourceid, message, isOriginal
1,5000,test 1, 1
1, 18, test 1, 0
1, 92, test 1, 0
2, 235, test 2, 1
2, 222, test 2, 0
3, 111, test 3, 1

Once I get the key values into Log, I think I would then use a Lookup task between two copies of Log (the reference table side would be filtered by isOriginal = 1) to populate the message column (and all of the other columns that I have not included here for simplicity - there's about 10 of them)

Is this a good approach? Is there a better way to get the unmatched ones than letting SQL Server reject some 2,000,000 records that already exist in both tables?

Actually.... I guess it's only the first time the package runs that it will have millions of rows to contend with. After that it will be running on schedule very regularly and probably only have to deal with a few hundred at a time.

Thanks for any pointers!

SSIS dt_numeric bug?

Wed, 25 Nov 2009 02:08:37 Z

Hi

I meet a strange issue with SSIS sql server2005. My source and target are Oracle. I use "Oracle provider for OLEDB" . One column is set number(2) in both database. After I select the source, SSIS generates this column's type as dt_numeric(2,0). Then I set a mapping , execute. However, I found if the value in this column is larger than(>=) 10, the value in target will be 0. If I change the type in SSIS to dt_numeric(3,0), it works well. Is it a bug of SSIS 2005?

Error in Excel Connection Manager Expression

Tue, 24 Nov 2009 18:52:05 Z

Hi all

I am new to this SSIS.

I am using an SSIS Package to upload the data from an Excel Sheet to SQL Server 2005 Database. I am doing the following Steps:

Excel Data Source to read the data from the Excel sheet.
Data Conversion to convert my columns to String(DT_STR).
SQL Server Destination to write the data to the SQL Table.

I have to do this upload every day. So the Upload file will be in the format Suman_11242009.xls. The next day it will be Suman_11252009.xls and so on. The Excel Connection String has to vary on each upload.

I have put the Data Flow task in 2 for each loops(i followed the process as mentioned in this blog http://cr9itesh.blogspot.com/)
Now at the Excel Source i get the following error:

Error at [Connection manger "Excel Connection Manager"]: SSISError Code STS_E_OLEDBERROR. An OLE DB error has ocured. Error Code: 0x80004005. An OLEDB record is available. Source:"Microsoft Jet DataBase Hresult: 0x80004005 Description: "invalid Argument.".

Error 1 Validation error. Data Flow Task(Import the Aetna PolicyStatus XLS Data into the Staging Table): Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

I also changed the DelayValidation to true

I could not figure out where the error is.

Refresh Tables in SQL Server 2008

Tue, 24 Nov 2009 18:04:31 Z

I'm teaching myself SQL Server 2008 after finding a good book, which is not too heavy to learn from (Microsoft SQL Server 2008 For Dummies).

Have noticed that when doing an ALTER TABLE query, the data is not automatically updated even when all tables and queries are closed. I imagine this also applies to other action queries.

Is there a simple solution for this?

SSIS EDI

Mon, 23 Nov 2009 22:46:12 Z

Is there a free option to use SSIS (SQL 2008) to create (and read) a text file that is in HIPAA ASC X12N 270/271 (004010X092A1) EDI format?

X12 with SSIS Anybody?

Thu, 20 Jul 2006 15:53:42 Z

Does anyone have any examples where they've successfully parsed EDI X12 using SSIS?

ISA*00* *01*000000 *ZZ*00000 *ZZ*ZMIXED *051220*1040*U*00401*000075004*1*T*:~GS*HC*00000*ZMIXED*20051220*104033*75004*X*004010X098A1~ST*837*000000001~BHT*0019*00*075004*20051220*101233*CH~REF*87*004010X098DA1~NM1*41*2*FINAL SUPPORT*****46*00000~PER*IC*CONNIE GOSS*TE*8172820300*EM*V00979~NM1*40*2*TEXAS THIN*****46*ZMIXED~HL*1**20*1~NM1*85*2*MEDICAL CLINICS,PC*****24*451234564~N3*3790 W. FIRST ST~N4*RICHARDSON*TX*75248~REF*1G*Z46489~REF*0B*MDQ4512~HL*2*1*22*0~SBR*P*18*******MC~NM1*IL*1*MARSHALL*JACKIE****MI*354787451~N3*300 LAMESA COURT~N4*CARROLLTON*TX*75006~DMG*D8*19500812*F~NM1*PR*2*MEDICAID OF HAWAII*****PI*D86916~CLM*000133*160***11::1*Y*A*Y*Y*B~HI*BK:4139~NM1*DN*1*BAILEY*WILLIAM*R**MD*24*451234564~NM1*82*1*HEART*JOHN*E**MD*24*451234564~REF*1G*Z46489~REF*0B*MDQ4512~NM1*FA*2*RIVER OAKS*****24*451234564~N3*3790 W. FIRST ST~N4*DALLAS*TX*75248~LX*1~SV1*HC:99213*85*UN*1*22**1~DTP*472*D8*20030723~REF*6R*295~LX*2~SV1*HC:93000*75*UN*1***1~DTP*472*D8*20030723~REF*6R*296~SE*37*000000001~GE*1*75004~IEA*1*000075004~

SSIS kill throughput to SQL 08, causing Network & Buffer I/O waits, slow disk writes on powerful server

Thu, 19 Nov 2009 02:09:29 Z

I have an SSIS dataflow that (source and destination dbs are all on same box, nothing else is running)

1. Runs a query joining a small dates table with an effective period table that is large from one database, that results in 100 million rows

2. I do a few lookups on some tables then as my next few steps, the lookup tables are small.

3. I burn the data to a table with no constraints, keys, simple recovery, etc. etc.

The initial query step fills tempdb up to 60 gigs, then it flushes through the rest of the steps, and burns to the db.

The resulting table is ~60gigs in size when burned.

The process takes 50 mins on a 16 core monster server with 64 gigs memory, tempdb, the mdfs, logs, os are all on their own spindle groups, in raid 0 arrays.

I want to speed this up, monitoring resource monitor during the process I noticed one major bottleneck:

During the tempdb buildup, its burning at 10 gigs/min, which is great

When burning to the db (streaming thru SSIS doing my tiny little lookup which is harmless then burning) is burning at 1 gig/min

During that time I also notice network IO waits of 900ms/second, from what I gather, its waiting on the front end the whole time (SSIS).

All resources are free during this time, cpu, disk etc, its just burning slow as ____.

I read that if too much flows into SSIS that the buffers fill and it pages to disk?

What could be causing this to grind to such a halt!?!?!

I have tried this with both SQL datasource/destination and ole db, no differences seen

Connection String for MS Access DB resides on the server.

Tue, 24 Nov 2009 20:23:36 Z

I am tying to connect to the ms access db resides on one of our server. I am able to get the data from access db when it's in my local machine, But I am not sure what should be in the connection string, when the access db on the server.

In my local machine, I am using the following connection string, and it's working fine.

Data Source=C:\Temp\Inventory.mdb;Provider=Microsoft.ACE.OLEDB.12.0;

From the server, I have tried the following connection string, but I couldn't make it.

Data Source=\\ServerName\Temp\Inventory.mdb;Provider=Microsoft.ACE.OLEDB.12.0;

Can someone please help me out here ?. what should be in the connection string of access db resides on server.

Thank you very much for your help.

Ketan

Pass Null Date Value to SP in SSIS

Tue, 24 Nov 2009 15:41:23 Z

I have a datetime SSIS Variable. If i remove it's 'value' via properties, it shows as 12/30/1899

Main task
Package has execute sql task with sp as
Execute SQL Task: exec spname ?

This maps to an SSIS Datetime Variable.

If package is called from frontend, and user prefers to enter the date, that date needs to go into proc as parameter.

if user only executes package from frontend without specifying the parameter, it needs to pass Null or some other value to SP.

Inside SP, it has If then condition to see :
create proc proname
@Date SMALLDATETIME = NULL
AS
BEGIN
If @Date is null
select @Date = getdate()
..
..
..
END

Determine Columns to update and updated columns

Sat, 21 Nov 2009 04:31:56 Z

I am using SSIS to update a table from a 3rd party. The table uses 48 fields. I am currently selecting all the data (33,000+ records) and connecting to a ole db command object that updates my table using '?' as a parameter for each of the fields. I created a trigger on the table that attempts to determines which fields updated. I am using the update(column name) in the trigger. It appears that if the field is used as part of the update field this function returns true regardless if the data changed. What I want is:

1.) Only update rows that have changes or insert the row if it does not exist

2.) Determine which fields actually changed and put those field names as a comma delimited string in 3rd table. This table has 4 fields. 1 defaults to current date using GetDate, two fields are from the current row being updated (using the inserted table) and the 4th field the list of fields that changed.

I think I could resolve the first problem by using an inner join and a where clause based on the data. Something like where t1.a <> t2.a OR t1.b <> t2.b, etc should work. What would be the best way to complete the 2nd requirement? Am i corrct that the 1st requirement qill be resolved using a where clause like above?

Thanks for all help...

Foreach Loop - not passing file name into my variable - unsure as to why this is

Mon, 23 Nov 2009 20:30:26 Z

I was using the TechNet tutorial:

How to: Loop through Excel Files and Tables by Using a Foreach Loop Container

It all seems to work great, except that my For each file loop isn't picking up the actual file name of the Excel file and passing it to the variable.

I have the for each file enumerator, it points to a folder: "C:\Current Data Processing" and files: "*.xls" to retrieve the fully qualified name.

In variable mappings I created a variable of type string with scope as the name of my SSIS package and the index in the variable mappings is 0.

(Can someone explain index by the way, I don't understand that. Do some variable mappings provide a range of columns/indexs I could place this stuff in? How do I view that to see what my options are? But that's a less pressing question)

Now the package runs just fine, but only because it points to the hard coded value I entered into the variable. If I remove that the package fails to run.

I don't understand why my Foreach loop container isn't putting the name of the excel file in my C:\Current Data Processing folder into the variable"ExcelFile"?

Thanks!

Trouble automating SSIS package to generate a XLS file using SQL Server Agent

Tue, 24 Nov 2009 17:06:12 Z

The Environment ...
Window Server 2003 R2 - Enterprise x64 Edition sp2
SQL Server 2005

The Scenario:
I have written a stored procedure which collects and returns a result set from our dB.
This Stored procedure is called from an SSIS package which outputs the results to an XLS file.
This SSIS package was created using the EXPORT data 'wizard'
This SSIS package has been imported into our servers Integration Services store.

When I open the store and run the package directly ...
<server_name>/Stored Packages/<package_name>
everything works as expected.

The Problem ...
When I create a Job in SQL Agent on the db server, which is the same server which hosts the Integration Services, and schedule it to run ... it fails.

Any idea why this would work manually but unable to be scheduled via SQL Server Agent?

Thank You,
Gary Stenstrom

making store procsa variable in excute sql tas

Mon, 23 Nov 2009 16:29:50 Z

hey hi,

i have control id and each control is assigned to one specific store proc. so in initial step of the package(execute sql task) we are getting information using a function which is basically a join query from two tables. once that is done, execute sql task is attached to one more execute sql task. which basically has a stroe prco .

so my question is if the input parameter is given example @controid =30 , then the store proc asscoiate to 30 should be run in execute sql task.

and if i say @contorlId 31 then it should run the store proc assocuited to 31.

please let me know in step by step as i am new to ssis.

thanks a lot......

A user request from the session with SPID 61 generated a fatal exception. SQL Server is terminating this session.

Tue, 24 Nov 2009 17:58:55 Z

Hi,

When i call a different package from .net i get the following error in event viewer:
A user request from the session with SPID 61 generated a fatal exception. SQL Server is terminating this session.

What does that mean?

Thanks

XML Source NOAA

Tue, 24 Nov 2009 16:36:15 Z

I am trying to import some XML data from the National Oceanic and Atmospheric Administration (NOAA) and I am running into roadbloks at every turn.
XML file: http://www.weather.gov/xml/current_obs/KMCE.xml
XSD file: http://www.weather.gov/view/current_observation.xsd

I can't get SSIS XML Source to see beyond the imageType. Any thoughts on how I can import the above xml in to a dB, I would really appreciate it.

Pleasae note Newbie to SSIS here.

Sequence Container

Tue, 24 Nov 2009 17:53:25 Z

I have two Sequence Containers. I am trying to design a script task that will call one if a file is found but call the other if it is not found. If this possible how do I call the containers?

  Dim strFile As String = "\\server\directory\file.txt"

        'check for file

        If System.IO.File.Exists(strFile) = True Then



        ElseIf System.IO.File.Exists(strFile) = False Then



        End If

        Dts.TaskResult = Dts.Results.Success

SQL Server 2008 Import/Export Wizard - DTSWizard.exe

Mon, 23 Nov 2009 02:24:31 Z

Located in C:\Program Files\Microsoft SQL Server\100\DTS\Binn .

Just want to confirm it: stand-alone command-line utility, does not need Integration Services running

Thanks.

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Reference SSIS Package Tasks Using Script Task within the same Package

Mon, 23 Nov 2009 17:11:46 Z

Hi all,

We have a third party application which takes user input and passes this through to an SSIS package which it then runs. The user input is used to modify or set task properties within the package, e.g. for an Execute SQL task it may set the query in the SqlStatementSource property before the Execute SQL task is then run. The application does this directly (i.e. it seems to be somehow directly referencing the tasks programmatically at runtime as opposed to using Expressions or package variables).

We need to be able to read these properties at runtime and are trying to use an SSIS script to do this. Using the script and some VB.NET we’ve worked out how to read package variables easy enough, however we do not know how to read the properties of tasks within the package.

We’d have thought that the way to access this would be something like:

Dts.packages(“package_name”).tasks(“task_name”).[property_name].tostring

However the above seems to be no-where near the correct syntax.

One thing we've found via help files/web search is IDTSName, this seems to let you access properties for a package, but again we're no sure how to use this or even if this is the correct thing to use.

Could anyone please point us in the direction of how to reference package tasks using a vb.net script within the same package?

Many thanks,

Iain

For reference we're using 32 SQL Server 2008 Standard on Windows server 2003

how to parse parameter/variable to sqlcommand when extracting data from ODBC data source?

Mon, 23 Nov 2009 07:29:23 Z

Hi there,

I would like to extract data from ODBC data source. There are data for many years (datetime field is like '2009.m11.d12'). But if I use select *, or select datetime, it only gives year and month for datetime value('2009.m11').

But we need datetime value by day, so I have to point out which day I am getting data for. Then I met the question: there is no parameter button in SQL Command panel. I tried to use variable as "select * from Pages where TimePeriod =@[User::testDate]", but didn't work. Only the one below works:
"select * from Pages where TimePeriod ='2009.m11.d12'"

I am connecting to the ODBC data source with ADO NET Source and ADO.NET Connection.

Where did I do wrong and how can I solve this problem?

Thanks,
Di

how to convert empty cell in excel into null in ssis

Tue, 24 Nov 2009 15:30:24 Z

Hi there:
My source excel file contains empty cell. In my ssis tasks, I used excel source data flow task to read the excel file and all empty cells are reserved as
empty cell in ssis. My question is how to convert those empty cells into NULL in SSIS?

thanks

Hui

--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Problem importing excel file ssis 2005

Tue, 24 Nov 2009 17:15:03 Z

Hi,

I am trying to import an excel file (file below) using SSIS 2005.
The target db and the .net application that runs the package is in a server.
When I run the package locally or from the server itself (destination db is the server and not the sql on my pc) by executing only the dtsx and not calling it through the application it runs fine.

However, when i delete the rows before "bill-to party" then the package from the application runs fine. It seems that the problem lies with the rows above that row for some reason.

The excel connection is :

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\files\06102009.xls;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1 ";

I have also increased TypeGuessRows in the server and locally.

What could be wrong?

Thanks

link to file

http://c-haosher.com/book1.xls

SFTP for SSIS

Tue, 24 Nov 2009 17:01:31 Z

Hello,

I have my current SSIS packages configured to make FTP connection to download and process the files. However our client has migrated this to SFTP all of sudden causing the FTP to fail. I have tried the FTP connection manager to point to port 22 instead and it doesn't seem to work. It seems that SSIS doesn't support SFTP.

Is anybody having any solution to this problem?

Thanks,

Hemal Mehta

how to run SSIS package until it succeeds

Tue, 17 Nov 2009 22:07:57 Z

I have a package that ones in a while fails because it connects to a server at a different building that runs late maintenance. I want the package to retry until it succeeds. Is this posible?

Error when using a parameter in OLE DB Source

Tue, 24 Nov 2009 16:22:42 Z

Hello,

I'm quit new at SSIS and have a problem with the usage of a parameter in a SELECT.
First a table 'Table Names', which exist of table names, must be read.
After that I want to loop through the table names and get info from that table.
Finally this info must be proccessed further.

My project is build as follow:
1. Created variable objResult of the type Object
2. Created variable strTableName of the type String
3. Add a Execute SQL Task to load records from the table 'Table Names' into the resultset objResult.
4. Add a ForEach loop which loops through objResult.
    At the option 'Variable Mappings' the variable strTableName is filled with the current Table
5. Add a Data Flow Task within the ForEach loop
6. Add variable strQuery of the type String with the following properties:
    - EvaluateAsExpression
    - Expression = "SELECT * FROM " + @[User::strTableName]
7. The evalueted value will be SELECT * FROM
    My conclusion is the value of strTableName is empty.
8. Add a OLE DB Source in the Data Flow with the following:
   - Data acces mode: SQL command from variable
   - Variable name: User::strQuery
9. When generating a preview or push Ok an error message occures:
   An OLE DB Error has occured. Error code: 0x80040E14
   ...... Description: "Incorrect syntax near '$Customer'."

I hope someone can give me a hint.

Thanks in advance.

Error; - 0x8004D01B Yet DTC Service is running on server

Tue, 24 Nov 2009 13:48:02 Z

Hi,
I wonder if you can help.
I got the following error when running an SSIS script; - 'The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.'

So I logged onto the Server as an Administrator; -
Launched 'Control Panel',
Opened 'Administrative Tools'
Clicked on 'Services'

I then noticed that 'Distributed Transaction Coordinator' has a status of started.

Does anyone have any ideas where I should look next?

Thanks,

Kieran.

SSIS Package Executes From BIDS, Not From SQL Agent

Tue, 24 Nov 2009 10:44:32 Z

Hi Todd,
I did what you have suggested to import the data from excel. The package works fine when executed from BI studio, but when deployed as a job it always says:

"Argument "Excel" for option "connection" is not valid. The command line parameters are invalid. "

Do you have any solutin for this? Please let me know..