Automation Error

Hello,

Last night was the first night we used Sql Server Agent to run our Warehouse ETL packages. We use the built in logging of SSIS as well as our own event logging mechanism as it goes through all of the packages. Unfortunately, when we checked this morning the agent showed failure but in both logs from the packages no errors were reported. I checked the Windows event log and the Sql Server logs but found nothing that would appear to report an error.

Does anyone have any ideas what I might be missing, any recommendations on things to check, we have manually been running these packages for weeks with no problems. Any help would be greatly appreciated! Thank you in advance!

probably user account problem - have you tried to run manually under the same user account as sql agent is running?

|||In reviewing all of the logs it made it through the entire process before reporting failure. The very last process to run is a DBCC CHECKDB task which shows up in the Server logs. I will double check though. Thank you for your input.

Automating File Deployment of SSIS Packages

Hi:

I am trying to utilize DTUtil command to automate deployment of SSIS Package from the Build Server to Dev Server. I am getting an invalid option error, when I try to run the following command:

dtutil /FILE C:\Program Files\ABC\ABC ABC CAD\ETL Packages\Load_Staging_FromWCF.dtsx /MOVE FILE;\\serverA\C$\Program Files\ABC\ABC ABC CAD\ETL Packages\Load_Staging_FromWCF.dtsx /QUIET

Currently I have a .CMD file, in which I have the above command, and I am trying to run the CMD file and it returns an error. Can someone help me the DTUtil switches. My goal is to move a DTSX file from Server A(Build Server) to Server B(Dev Server). Also is there a switch to specify the server name, if UNC path is not supported. Also I am using File System Deployment, and running the file as an Administrator. I have admin privs on both Build and Dev Server. Thanks and I appreciate some help on this.

Why not just copy the package files over there using regular COPY commands?|||

That is true, because I am not actually utilizing SQL Server Deployment switches, although DTUtil does allow moving package files, just not sure what switches to apply. Thanks.

Automating Creation of Reporting Services Reports (Excel format)

Hello,

Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?

Thanks for sharing your thoughts and ideas!

donnie100 wrote:

Hello,

Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?

Thanks for sharing your thoughts and ideas!

You mean you want to use SSIS to build the RDL? I'm not really sure why you would want to do this but heigh-ho.

Is there a .Net API for SSRS? if there is then you could call it from a SSIS script task.

-Jamie

Automatically running the SSIS package once in a day

hi frnds,

i have SSIS package, i would like to schedule the SSIS package inorder to run automatically once in a day.

can anyone suggest me the solun. pls its urgent.

thnkx in adavance..

Use SQL Server Agent|||

hi,

i connected to my DB.but it is not showing SQL Server Agent in that.

reamining all things are there.

can you tell me what will be the problem?

|||You might not have access to SQL Server Agent. Talk to your DBA.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

AutoGenerate Table Schema in SSIS

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?SSIS can't handle changing metadata. Once built, it's set. (Unless you edit it and resave it.) Unless you are going to build the package using .Net, I don't believe this is possible.|||

Leo Mwangi wrote:

I have a database that has few tables whose table Schema changes oftenly based on a flat file specification and I wanted to automate the process where I drop the table and recreate it with the new schema using an SSIS package. Any Ideas on how I can achieve this?

Are you just wanting to drop and recreate the table, or do you want to populate it with data as well? If you want to populate it, Phil's comment applies. If you just want to drop and recreate the table based on info from a flat file, you could use a script task to read the file, create the appropriate SQL statements to DROP and CREATE the table and store them in variables, then call them from an Execute SQL task.

|||True statement, John!|||I want to drop and recreate them then push data. I think i have a working script which I will be pushing using script task. thanks for the suggestions guys.

Auto stats

Hi all,
I have an dtsx (SSIS) for "clone" manually Sql server database to another.
How I copy all stats from one database to another ? I have problem with
"auto stats".
When I try DROP statitics for auto stats I get this error:
No se puede DROP el índice 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'.
No es una colección de estadísticas.
Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not
statitics collection.
What can I do '
-- Get Stats list
SELECT
'[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + ''
+ '.' + st.name + '' AS [Estadistica]
FROM
sys.tables AS tbl
INNER JOIN sys.stats st ON st.object_id=tbl.object_id
ORDER BY
[Table_Name_With_Schema] ASC,[Name] ASC
Thanks in advance, any help will be appreciated, regards, greetings
--
http://www.alhambra-eidos.es/web2005/index.html
www.kiquenet.net
http://www.setbb.com/putainformatica/viewtopic.php?p=843
www.trabajobasura.com/solusoft"Alhambra Eidos Kiquenet" <AlhambraEidosKiquenet@.discussions.microsoft.com>
wrote in message news:3B43B633-9B97-4AAF-9B5E-7B597F7EA970@.microsoft.com...
> Hi all,
> I have an dtsx (SSIS) for "clone" manually Sql server database to another.
> How I copy all stats from one database to another ? I have problem with
> "auto stats".
> When I try DROP statitics for auto stats I get this error:
> No se puede DROP el índice
> 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'.
> No es una colección de estadísticas.
> Cannot DROP index 'dbo.ACTIVIDAD_PROVEEDOR.PK_ACTIVIDAD_PROVEEDOR'. Not
> statitics collection.
>
> What can I do '
>
> -- Get Stats list
> SELECT
> '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
> [Table_Name_With_Schema],
> '[' + st.name + ']' AS [Name],
> '' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + ''
> + '.' + st.name + '' AS [Estadistica]
> FROM
> sys.tables AS tbl
> INNER JOIN sys.stats st ON st.object_id=tbl.object_id
> ORDER BY
> [Table_Name_With_Schema] ASC,[Name] ASC
>
> Thanks in advance, any help will be appreciated, regards, greetings
> --
> http://www.alhambra-eidos.es/web2005/index.html
> www.kiquenet.net
> http://www.setbb.com/putainformatica/viewtopic.php?p=843
> www.trabajobasura.com/solusoft
>
Hi
You are probably trying to drop indexes as statistics, to exclude indexes
try:
SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + ''
AS [Estadistica]
FROM sys.tables AS tbl
JOIN sys.stats st ON st.object_id=tbl.object_id
LEFT JOIN sys.indexes i on i.index_id = st.stats_id and i.object_id =st.object_id
WHERE i.index_id IS NULL
ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
If you only want auto_stats then
SELECT '[' + SCHEMA_NAME(tbl.schema_id) + '].[' + tbl.name + ']' AS
[Table_Name_With_Schema],
'[' + st.name + ']' AS [Name],
'' + SCHEMA_NAME(tbl.schema_id) + '.' + tbl.name + '' + '.' + st.name + ''
AS [Estadistica]
FROM sys.tables AS tbl
JOIN sys.stats st ON st.object_id=tbl.object_id
LEFT JOIN sys.indexes i on i.index_id = st.stats_id and i.object_id =st.object_id
WHERE i.index_id IS NULL
AND st.auto_created = 1
ORDER BY [Table_Name_With_Schema] ASC,[Name] ASC
John