Saving the Full Resultset of Execute Sql Task directly into Sql Server 2005 table

Hi friends,

I couldn't find links for this issue.

1) How to write the contents of a dataset or a full resultset (from execute sql task) directly into a Sql Server 2005 table.

2) Since I have hundreds of Resulting columns, I want to create the Destination table based on the structure of the dataset.

How can we achieve this?

Thanks

Subhash Subramanyam

Seems like it would be easiest to do it within SQL Server instead of SSIS. Probably by using a SELECT INTO statement in your Execute SQL Task. If you needed the resultset in SSIS, you could read it from your newly created table.
|||

Thanks for your reply JayH. I think you did not get my entire scenario.

1) I am using Oracle Source and Sql Server Destination

2) After fetching results of a query from oracle in the form of Full result Set, I use a Script task in which I add few computed columns thus finally storing everything into a DataTable

3) Now this DataTable has to be written into a Sql Server table which has to be created if not present, and it should have the structure of the Datatable.

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Thanks for your reply JayH. I think you did not get my entire scenario.

1) I am using Oracle Source and Sql Server Destination

2) After fetching results of a query from oracle in the form of Full result Set, I use a Script task in which I add few computed columns thus finally storing everything into a DataTable

3) Now this DataTable has to be written into a Sql Server table which has to be created if not present, and it should have the structure of the Datatable.

Thanks

Subhash Subramanyam

In that case you'll have to do it all in a script task. SSIS does not provide any mechanism to do what you want, and I don't think ADO.NET does either, though this is really more of an ADO.NET programming question now than an SSIS question.

I think you'll need to iterate through your columns to create a CREATE TABLE statement to create the table. Then you can iterate through the rows to create INSERT INTO statements. All of which would be executed inside the script.
sql

Saving Query Result to new file

Hi Gurus,

Can u help me out in the following two scenario/problems.

1. I want to save the query results (which is generated within execute sql task) to file (which does not exists & should be created at run time). is it possible?

2. Can I define the file name, in Flat File connection Manager, dynamically (by using some variable)?

Your support and help will be appreciated...

regards,

Anas

Zadoras wrote:

Hi Gurus,

Can u help me out in the following two scenario/problems.

1. I want to save the query results (which is generated within execute sql task) to file (which does not exists & should be created at run time). is it possible?

It is, in theory, possible yes. But you will need more tasks to be able to do it. If I were you I would just use a data-flow (which would contain a Flat File Connection Manager).

Zadoras wrote:

2. Can I define the file name, in Flat File connection Manager, dynamically (by using some variable)?

Yes. Look to use an expression:

Setting expressions
http://blogs.conchango.com/jamiethomson/archive/2006/03/11/3063.aspx)

-Jamie

|||

For your first question it sounds to me like you really need is a DataFlow task...any problem with that?

Rafael Salas

|||

How will i able to create file? I know I can do it using data flow task but my question is that HOW WILL I BE ABLE TO CREATE FILE (not just insert data) using FLAT FILE CONNECTION manager?

is it possible

how can i able to format the data?

|||

Not really sure what the problem is here. The file gets created as soon as you flow data into it using the Flat File Destination Adapter.

-Jamie

|||

If NO file is there I get this error message:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Send Mail Task [Send Mail Task]: Either the file "\\dwdev\d$\DW Load Reports\Comany.xls" does not exist or you do not have permissions to access the file.

Error at Send Mail Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

if I creat file manually... this error goes

Can any also tell me format the data in XLS file (as query result output). It is showing me all data in merely 1 column? though, I have data (from query result) in 7 column

regards,
Anas

|||

I have got the asnwer.....

It is not the problem of Falt File Destination (Data Flow).... I am also using Send Mail Task to send file as an attachment... so i must have attachement file....

got it now.... sorry to bother u. and waisting your times...

thanx for you support

cheers,

sql

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822
|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

sql

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

saving DTS package to sql server

hi,

I have a "Transfer sql server object task" in SSIS that transfers few tables from one database to another.

The task works fine and the project is saved on my c: drive.

I was trying to save the project on the SQL server and don't know if i am doing somehting wrong?

I have the following selections made:

Package Location: SQL server

Server: ServerName

Authentication Type: Windows

Package path: /TransferTables

Protection Level: Encrypt sensitive data with user key

Now when i hit ok i get the "No description found" error with the following details:

No description found (Microsoft Visual Studio)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToSqlServerAs(Package package, IDTSEvents events, String packagePath, String serverName, String serverUserName, String serverPassword)
at Microsoft.DataTransformationServices.Controls.PackageLocationControl.SavePackage(Package package)
at Microsoft.DataTransformationServices.Design.Controls.PackageSaveCopyForm.PackageSaveCopyForm_FormClosing(Object sender, FormClosingEventArgs e)

Does anyone have a solution to this?

Thanks for any help.

I got past the above error and now i am getting this

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)

Any help is appreciated?

|||

As a workaround you can use DTUtil to copy/move a package from the file system to SQL Server.

DTUtil /file fully_qualified_filename /copy sql;pkgname

So: DTUtil /file c:\Packages\MyPkg.dtsx /copy sql;MyPkg

HTH,

Matt

|||

Hi David,

Thanks for the reply.

I finally got the package imported into the sql server.

The problem now is that when i run it remotely from the server machine it works fine but when i run it from my desktop i get the following error:

"The task cannot run on this edition of integration services. it requires a higher level edition"

any ideas?

Thanks

|||

Usually this is because you don't have SSIS installed on the PC in question. You need SSIS installed not just the management tools.

Matt

|||doesn't SSIS get installed as part of the client tools?|||I think you have to install Business Inteligence Management Studio in order to access SSIS packages.|||No, you need to install SSIS from the SSIS option in setup.|||I do have the Business Intelligence Management Studio installed.|||

Hi RookieDBA,

how did you import the files finally, by using Management Studio or with the util? I'm asking because I'm having the same problem and therefore it is very interesting for me to have a solution how to solve this.

Regards,

Jan

|||

I used util. I was not able to import using management studio.

|||I'm having this same problem. Did you ever get a solution?

===================================

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.SaveToDtsServer(Package pPackage, IDTSEvents pEvents, String sPackagePath, String sServerName)
at Microsoft.SqlServer.Dts.ObjectExplorerUI.ImportPackageAsAction.ImportPackage(ImportPackageAsForm dlg)|||Use the dtutil. See reply above from Matt David|||True the dtsutil is a work around. However, the actual probably I found to be corrected by uninstalling the sqltools hotfix(kb918222) and the reinstalling it. Perhaps the hotfix was applied in the wrong order on this machine. http://support.microsoft.com/?id=91822|||

Installing the hotfix had no effect for me. Still got the error, and yes I installed the packages individually to avoid having the install fail.

The problem seems to be with the package itself, as I don't have the same issue with other packages. I tried to recreate the package from scratch, but the second one failed as well. The package is very simple. Two data sources - one ODBC and one SQL, two corresponding connection managers, an execute SQL task and a data flow task with two sub-steps (data reader source and OLE destination).

Actually found a solution to this in another thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406974&SiteID=1

The short answer is run: regsvr32 msxml3.dll and regsvr32 msxml6.dll

SaveCheckpoints=True affects Recordset

I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:

Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable

adp.Fill(dt, Dts.Variables("CountryTable").Value)

It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.

Note: I have SP1 installed.

That's weird for sure. I'll need to investigate.

When we are writing checkpoints, we also write out the value of variables to the checkpoint file - so that we can start the package again in the correct state. I can imagine an error when serializing an object variable could cause some checkpoint funkiness, but as I say, it needs more investigation.

Donald

|||

Donald,

I thought that Object variables don't get written out (unless the behaviour has changed in SP1).

-Jamie

|||

They're not and its documented in BOL now.

I hade the same issue, that my package worked up until I added checkpoints at which point it wouldn't run properly. Thought I had raised a bug but haven't

Save/Copy column mappings in DTS?

Is it possible to save column mapping definitions from a Transform Data Task? The practical use is I have four tables with very similar layouts of which 200 or so columns are identical. I have various front and back office applications that require local copies of this data in various formats. It is EXTREMELY tedious to remap all of the columns for each Transform Data Task required on these applications.

Is there a way to store all of the column mapping def's and import them into a new transform data task?if you are using SSIS, the task itself is an xml file so you could just edit it directly... :)

EDIT: did I say task? I meant the package is an xml file.|||Save the package in Visual basic File then edit it.
And you can re-create the package from the Visual basic File.|||I should have mentioned this is sql 2k I suppose. I haven't toyed with saving packages as vb scripts yet, I'll give that a go and see what sort of damage I can do.

Save The Newbie! SQL help

Hey guys. I have been given the task of converting from oracle to microsoft SQL syntax.

I have this oracle code:
CREATE OR REPLACE PROCEDURE BA_DROP_TABLE (p_TableName IN varchar2) IS
i int;
BEGIN
select count(*) into i from user_tables where table_name = p_TableName;
IF i=1 THEN
EXECUTE IMMEDIATE 'DROP TABLE ' || p_TableName || ' CASCADE CONSTRAINTS';
END IF;
END;
/

I tried to convert it, and this is what I came up with:
if (object_id ('BA_DROP_TABLE') is not null)
drop proc BA_DROP_TABLE
go

CREATE PROCEDURE BA_DROP_TABLE @.p_TableName varchar(30)
AS
DECLARE @.i integer
BEGIN
select @.i = count(*) from user_tables where table_name = p_TableName
IF @.i=1
DROP TABLE p_TableName
END

This doesn't work. I think the problem has something to do with not being able to drop the table because its a parameter. I've heard talk of dynamic sql.

Someone PLEASE help a guy out!

Thanksreplace "DROP TABLE p_TableName" with "exec ('drop table ' + @.p_TableName)

Make sure the user has DROP OBJECTS permission.|||Now I get this error:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'BA_DROP_TABLE'. The stored procedure will still be created.

Save data flow task result into specific table in database

Hello

Kindly i need support in this issue, i create task flow import from flat file and store in database but i need to save all result for task into specific table

Like Record count transferred

Destination table name

Time ..........etc

thanks

Sounds like you are looking for auditing information. Watch the Kimball Webcast on the main page of this forum. Plenty of examples in there.

In summary, you'll populate variables in your data flow, and then after the data flow completes, you'll use an Execute SQL task in the control flow to perform an insert statement into your "specific table" using those variables previously populated.|||where in the main page ?|||

Hosam Abd EL-Wahab wrote:

where in the main page ?

The Ralph Kimball Group Webcast

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534505&SiteID=1